Introduction
The EXPLAIN statement in PostgreSQL returns the execution plan generated by the planner for a given SQL statement. It details how the database will execute the query, indicating whether it will utilize index scans, sequential scans, or different join algorithms if multiple tables are involved. A crucial aspect of this display is the estimated execution cost of the statement, which is particularly emphasized when the ANALYZE option is used. This option allows for a deeper understanding of how the SQL query performs during execution.
How ANALYZE Works
Statistics Collection: ANALYZE gathers statistics on table columns, including data distribution and null values.
Usage by the Planner: The PostgreSQL planner uses this statistical data to determine the best execution plan for SQL queries, optimizing performance.
How to Use ANALYZE
Bulk Inserts/Updates/Deletes:
Whenever you perform large-scale data modifications (e.g., adding, modifying, or removing a significant number of rows), it’s essential to run ANALYZE to update the statistics reflecting the new data distribution.
Scheduled Maintenance: Regularly scheduled ANALYZE commands can be part of your maintenance routine to keep statistics fresh.
Step 1: Query Analysis
mydb=# EXPLAIN ANALYZE SELECT * FROM product ;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Seq Scan on product (cost=0.00..4.00 rows=200 width=24) (actual time=0.074..7.039 rows=200 loops=1)
Planning Time: 4.072 ms
Execution Time: 22.602 ms
(3 rows)Explanation:
Seq Scan on product: A sequential scan was also performed on the product table.
Cost: The estimated cost ranges from 0.00 to 4.00.
Rows: The planner estimated 200 rows, which matches the actual returned rows.
Timing: The planning time was significantly higher at 4.072 ms, and the total execution time was 22.602 ms, indicating more complex processing during execution.
Step 2:
mydb=# EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELCET * FROM public.product;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Seq Scan on public.product (cost=0.00..4.00 rows=200 width=24) (actual time=0.087..6.783 rows=200 loops=1)
Output: productid, name
Buffers: shared hit=2
Planning Time: 0.175 ms
Execution Time: 14.170 ms
(5 rows)Explanation:
Seq Scan on product: A sequential scan was performed on the product table.
Cost: The estimated cost of the operation ranges from 0.00 (startup cost) to 4.00 (total cost).
Rows: The planner estimated 200 rows, but only 200 rows were actually returned.
Buffers: Indicates that 2 shared buffer hit occurred.
Timing: The planning time was 0.175 ms, while the total execution time was 14.170 ms.
There’s more in store!
The execution plans generated by PostgreSQL provide valuable insights into how SQL queries are executed. The first query using ANALYZE shows a sequential scan on the product table, with a total execution time of 22.602 ms and a planning time of 4.072 ms, indicating more complex processing. The second query, which also performed a sequential scan, utilized ANALYZE, VERBOSE, and BUFFERS, and achieved a faster execution time of 14.170 ms with a planning time of only 0.175 ms. Both queries returned the same result set of 200 rows, but the differences in timing and execution metrics highlight the variability in query performance and the benefits of using detailed execution options.
Understanding these aspects is essential for optimizing database performance and refining SQL queries for efficiency. We’ll be blogging about these exact Performance tuning tips, tricks, tweaks and intriguing aspects in the upcoming blog series. Stay tuned!
Keep Calm and Tune PostgreSQL…
Leave a Reply