Arrow of time
Arrow of time

How beautiful the PostgreSQL planner can be

Just wanted to share how beautiful the PostgreSQL query execution planner can be. Here is a query where I want ...

Just wanted to share how beautiful the PostgreSQL query execution planner can be. Here is a query where I want to find out both the minimum and the maximum of the id field, which is a sequential primary key:

explorer2=# explain analyze select min(id), max(id) from expl_iocombined;
                                                QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=1.21..1.22 rows=1 width=0) (actual time=0.028..0.028 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.57..0.61 rows=1 width=8) (actual time=0.015..0.015 rows=1 loops=1)
       ->  Index Only Scan using expl_iocombined_pkey on expl_iocombined  (cost=0.57..25512595.31 rows=848505805 width=8) (actual time=0.015..0.015 rows=1 loops=1)
         Index Cond: (id IS NOT NULL)
         Heap Fetches: 0
   InitPlan 2 (returns $1)
     ->  Limit  (cost=0.57..0.61 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=1)
       ->  Index Only Scan Backward using expl_iocombined_pkey on expl_iocombined expl_iocombined_1  (cost=0.57..25512595.31 rows=848505805 width=8) (actual time=0.011..0.011 rows=1 loops=1)
         Index Cond: (id IS NOT NULL)
         Heap Fetches: 1
 Planning time: 0.084 ms
 Execution time: 0.040 ms
(13 rows)

So, it converted the min(x) and max(x) operations into ORDER BY x [DESC] LIMIT 1 and executed it from index-only scans. The index scans are slaved (deeper in execution hierachy) to the LIMIT operation, so the LIMIT operation just asks for a single fetch from the index lookup operation, and there it is. At most, one read operation is required (one for each min and max), and in reality, only one has happened here (the single heap fetch in the second operation).

Other databases have similar operations, just wanted to share this particular, elegant execution plan.


comments powered by Disqus