Horton

Re: Slow query: bitmap scan troubles

Jeff Janes <jeff.janes@gmail.com>

View thread View on postgr.es
From: Jeff Janes <jeff.janes@gmail.com>
To: Claudio Freire <klaussfreire@gmail.com>
Cc: postgresql@foo.me.uk, postgres performance list <pgsql-performance@postgresql.org>
Date: 2012-12-06T17:27:48Z
Lists: pgsql-hackers, pgsql-performance 
On Wed, Dec 5, 2012 at 9:43 AM, Claudio Freire <klaussfreire@gmail.com> wrote:
> On Wed, Dec 5, 2012 at 2:39 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
>> I'm not sure that this change would fix your problem, because it might
>> also change the costs of the alternative plans in a way that
>> neutralizes things.  But I suspect it would fix it.  Of course, a
>> correct estimate of the join size would also fix it--you have kind of
>> a perfect storm here.
>
> As far as I can see on the explain, the misestimation is 3x~4x not 200x.

It is 3x (14085 vs 4588) for selectivity on one of the tables, "Index
Only Scan using idx_trade_id_book on trade".

But for the join of both tables it is estimate 2120 vs actual 11.

Cheers,

Jeff

Commits

  1. Tweak genericcostestimate's fudge factor for index size.

  2. Tweak index costing for problems with partial indexes.