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

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

From: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
To: Kim Hansen <kim(at)rthansen(dot)dk>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Planner selects slow "Bitmap Heap Scan" when "Index Scan" is faster
Date: 2012-04-06 17:11:37
Message-ID: CAMkU=1zVGenuzg6UpPW_SHPAJ8CvQ2zS_JRQXYTfQHhxDwE2xQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Wed, Apr 4, 2012 at 6:47 AM, Kim Hansen <kim(at)rthansen(dot)dk> wrote:
> Hi All
>
> 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".
>
> This it the two plans, I have also pasted them below:
>  Slow (189ms): http://explain.depesz.com/s/2Wq
>  Fast (21ms): http://explain.depesz.com/s/ThQ

Could you do explain (analyze, buffers)?

Did you run these queries multiple times in both orders?  If you just
ran them once each, in the order indicated, then the bitmap scan may
have done the hard work of reading all the needed buffers into cache,
and the index scan then got to enjoy that cache.

Cheers,

Jeff

In response to

Responses

pgsql-performance by date

Next:From: Kim HansenDate: 2012-04-06 22:09:36
Subject: Re: Planner selects slow "Bitmap Heap Scan" when "Index Scan" is faster
Previous:From: Julien CigarDate: 2012-04-06 11:16:39
Subject: Re: bad plan

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