How Statsig uses query-level experiments to speed up Metrics Explorer

Statsig has traditionally focused on large-scale experiments to understand the impact of significant product changes, but has shifted its approach to optimize query performance in Metrics Explorer by experimenting at the query level. The company aims to improve query speed rather than user experience stability, allowing them to quickly gather extensive data samples and empirically test methodologies. This shift in focus enables non-experts to explore performance improvements and helps prevent unintended regressions. The core experiment involved comparing the use of Common Table Expressions (CTEs) versus temporary tables in query execution, revealing that CTEs generally reduced latency except in cases involving very large datasets where temporary tables were more effective. Factors such as expensive joins, the number of funnel steps, and grouping by fields were analyzed to determine their influence on the decision to use CTEs or temporary tables, leading to a more data-driven approach in optimizing Metrics Explorer queries.