Skip site navigation (1) Skip section navigation (2)

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

From: Kim Hansen <kim(at)rthansen(dot)dk>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Planner selects slow "Bitmap Heap Scan" when "Index Scan" is faster
Date: 2012-04-05 16:01:16
Message-ID: CAEGYRW6nBdRez2yU3bH7puL7GrghL7qBxGyi9dUNEY54zJKKDQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: Merlin MoncureDate: 2012-04-05 18:43:55
Subject: Re: H800 + md1200 Performance problem
Previous:From: Tomas VondraDate: 2012-04-05 15:49:56
Subject: Re: H800 + md1200 Performance problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group