Re: Planner selects slow "Bitmap Heap Scan" when "Index Scan" is faster

On Thu, Apr 5, 2012 at 17:34, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Kim Hansen <kim(at)rthansen(dot)dk> wrote:
>
>> I have a query where the planner makes a wrong cost estimate, it
>> looks like it underestimates the cost of a "Bitmap Heap Scan"
>> compared to an "Index Scan".
>
>> What can I do to fix the cost estimate?
>
> Could you try running the query with cpu_tuple_cost = 0.05 and let
> us know how that goes?
>

It looks like it just increased the estimated cost of both queries by
about 1000.

Regards,
Kim

===============

yield=> explain analyze select "filtered_demands"."pol" as "c0" from
"demands"."filtered_demands" as "filtered_demands" where
("filtered_demands"."pod" = 'VELAG') group by "filtered_demands"."pol"
order by "filtered_demands"."pol" ASC NULLS LAST;

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=39540.92..39540.92 rows=2 width=6) (actual
time=186.833..186.858 rows=221 loops=1)
Sort Key: pol
Sort Method: quicksort Memory: 35kB
-> HashAggregate (cost=39540.81..39540.91 rows=2 width=6) (actual
time=186.643..186.678 rows=221 loops=1)
-> Bitmap Heap Scan on filtered_demands
(cost=566.23..39479.81 rows=24401 width=6) (actual time=6.154..180.654
rows=18588 loops=1)
Recheck Cond: (pod = 'VELAG'::text)
-> Bitmap Index Scan on filtered_demands_pod_pol_idx
(cost=0.00..560.12 rows=24401 width=0) (actual time=4.699..4.699
rows=18588 loops=1)
Index Cond: (pod = 'VELAG'::text)
Total runtime: 186.912 ms
(9 rows)

yield=> set enable_bitmapscan = false;
SET
yield=> explain analyze select "filtered_demands"."pol" as "c0" from
"demands"."filtered_demands" as "filtered_demands" where
("filtered_demands"."pod" = 'VELAG') group by "filtered_demands"."pol"
order by "filtered_demands"."pol" ASC NULLS LAST;

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Group (cost=0.00..77510.37 rows=2 width=6) (actual
time=0.029..20.361 rows=221 loops=1)
-> Index Scan using filtered_demands_pod_pol_idx on
filtered_demands (cost=0.00..77449.37 rows=24401 width=6) (actual
time=0.027..16.859 rows=18588 loops=1)
Index Cond: (pod = 'VELAG'::text)
Total runtime: 20.410 ms
(4 rows)

yield=>

--
Kim Rydhof Thor Hansen
Vadgårdsvej 3, 2. tv.
2860 Søborg
Phone: +45 3091 2437