A query plan needs to be computed to execute a query in PostgreSQL. The query only describes how the result should look like. However, to execute a query, PostgreSQL needs to know which operators (e.g., filters, join) and input data (e.g., a table scan or an index scan) have to be used to produce the desired query result. So, the query plan contains the exact information on which steps are needed to execute the query.
When a given function is used on the predicate it can or cannot be pre-evaluated during query planning depending on the function volatility. IMMUTABLE functions return the same result for the same input so the result is pre-evaluated during the query planning and the function invocation will be replaced by a constant value in the query plan. But for functions using other volatility classification Postgres does not apply this optimization.
In this talk, we will present some techniques we implemented in TimescaleDB, where we hacked the planner to add and/or replace the constant value of a function call result to give the planner a chance to produce a better execution plan. This is useful because it can potentially improve query performance on partitioned tables by allowing partitions to be pruned at planning time, significantly reducing execution time.
References:
https://www.timescale.com/blog/how-we-fixed-long-running-postgresql-now-queries/
https://github.com/timescale/timescaledb/pull/4340
https://www.timescale.com/blog/how-we-made-real-time-data-aggregation-in-postgres-faster-by-50-000/
https://github.com/timescale/timescaledb/pull/6325
Improve query plans by "constifying" expressions
Fabrízio de Royes Mello
Timescale
2024-11-07 10:30:00 - AUDITORIO 1
A query plan needs to be computed to execute a query in PostgreSQL. The query only describes how the result should look like. However, to execute a query, PostgreSQL needs to know which operators (e.g., filters, join) and input data (e.g., a table scan or an index scan) have to be used to produce the desired query result. So, the query plan contains the exact information on which steps are needed to execute the query. When a given function is used on the predicate it can or cannot be pre-evaluated during query planning depending on the function volatility. IMMUTABLE functions return the same result for the same input so the result is pre-evaluated during the query planning and the function invocation will be replaced by a constant value in the query plan. But for functions using other volatility classification Postgres does not apply this optimization. In this talk, we will present some techniques we implemented in TimescaleDB, where we hacked the planner to add and/or replace the constant value of a function call result to give the planner a chance to produce a better execution plan. This is useful because it can potentially improve query performance on partitioned tables by allowing partitions to be pruned at planning time, significantly reducing execution time. References: https://www.timescale.com/blog/how-we-fixed-long-running-postgresql-now-queries/ https://github.com/timescale/timescaledb/pull/4340 https://www.timescale.com/blog/how-we-made-real-time-data-aggregation-in-postgres-faster-by-50-000/ https://github.com/timescale/timescaledb/pull/6325