Autoresearch found a 3-year-old bug in our query engine (posthog.com)

A recent hackathon at PostHog revealed a significant, three-year-old bug in their query engine, discovered through an AI-driven analysis of query performance. By using an autoresearch setup inspired by Andrej Karpathy's framework, the team tasked an AI agent with optimizing slow queries, leading to the identification of faulty timestamp handling that prevented ClickHouse from effectively utilizing its primary key for partition pruning. The fix resulted in a remarkable 37% reduction in query time and a 62% decrease in granules that ClickHouse had to scan, highlighting the potential for AI to detect and resolve long-standing issues in codebases. This breakthrough is crucial for the AI/ML community as it demonstrates the power of AI agents to uncover complex bugs that human developers might overlook due to familiarity with the code. The PostHog team plans to automate this process further by integrating AI into their query logging and performance analysis pipeline. By continuously monitoring for inefficiencies and optimizing queries autonomously, they aim to enhance their software’s performance and reliability, setting a precedent for leveraging AI in code maintenance and performance optimization across various tech stacks.