[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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



On Thu, Apr 5, 2012 at 17:34, Kevin Grittner
<Kevin.Grittner@xxxxxxxxxxxx> wrote:
> Kim Hansen <kim@xxxxxxxxxxx> 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

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Home]     [Yosemite]

Powered by Linux