Database Reference
In-Depth Information
The planner concludes that using the index is cheaper than a sequential scan and
switches to an index scan. The estimated overall cost dropped from 33.48 to 8.29. The
startup cost is no longer zero, because the planner first scans the index, then pulls the
matching records from data pages (or from memory if in shared buffers already). You'll
also notice that the planner no longer needed to scan 1,477 records. This greatly reduced
the cost.
More complex queries, such as
Example 9-5
, include more child steps. The final step is
always listed first, and its total cost and time equals the sum of all its child steps. The
output indents the child steps.
Example 9-5. EXPLAIN (ANALYZE) with GROUP BY and SUM
EXPLAIN
(
ANALYZE
)
SELECT
left
(
tract_id
,
5
)
AS
county_code
,
SUM
(
white_alone
)
As
w
FROM
census
.
hisp_pop
WHERE
tract_id
BETWEEN
'25025000000'
AND
'25025999999'
GROUP
BY
county_code
;
The accompanying output of
Example 9-5
is shown in
Example 9-6
, showing a grouping
and sum.
Example 9-6. EXPLAIN (ANALYZE) output of HashAggregate strategy plan
HashAggregate
(cost=29.57..32.45 rows=192 width=16) (actual time=0.664..0.664 rows=1 loops=1)
Group Key: "left"((tract_id)::text, 5)
-> Bitmap Heap Scan on hisp_pop
(cost=10.25..28.61 rows=192 width=16) (actual time=0.441..0.550 rows=204
loops=1)
Recheck Cond:
(((tract_id)::text >= '25025000000'::text) AND
((tract_id)::text <= '25025999999'::text))
Heap Blocks: exact=15
-> Bitmap Index Scan on hisp_pop_pkey
(cost=0.00..10.20 rows=192 width=0) (actual time=0.421..0.421 rows=204
loops=1)
Index Cond:
(((tract_id)::text >= '25025000000'::text) AND
((tract_id)::text <= '25025999999'::text))
Planning time: 4.835 ms
Execution time: 0.732 ms
The parent step of
Example 9-6
is the Hash Aggregate. It contains a child step of Bitmap
Heap Scan, which in turn contains a child step of Bitmap Index Scan. In this example,
because this is the first time we're running this query, our planning time greatly over‐
shadows the execution time. However, PostgreSQL caches plans, so if we were to run
this query or a similar one, we should be rewarded with a much reduced planning time.
