Horton

Re: Slow query: bitmap scan troubles

Philip Scott <pscott@foo.me.uk>

View thread View on postgr.es
From: "Philip Scott" <pscott@foo.me.uk>
To: "'Claudio Freire'" <klaussfreire@gmail.com>, <postgresql@foo.me.uk>
Cc: "'postgres performance list'" <pgsql-performance@postgresql.org>
Date: 2012-12-04T18:31:05Z
Lists: pgsql-hackers, pgsql-performance 
> The difference between cost estimation and actual cost of your queries,
under relatively precise row estimates, seems to suggest your e_c_s or r_p_c
aren't a reflection of your hardware's performance.

Wow, so tweaking these has fixed it and then some. It now picks a slightly
different plan than the 'fast' one previously:

New super fast version with e_c_s 6GB->88Gb and r_p_c 2-> 1 (s_p_c 1->0.5):
http://explain.depesz.com/s/ECk

For reference:
> Slow version with bitmapscan enabled: http://explain.depesz.com/s/6I7 
> Fast version with bitmapscan disabled: http://explain.depesz.com/s/4MWG

Commits

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

  2. Tweak index costing for problems with partial indexes.