Ah okay, thanks. I knew I could set various things but not effective_work_mem (I tried reloading the edited config file but it didn’t seem to pick it up)
From: Vitalii Tymchyshyn [mailto:tivv00@gmail.com] Sent: 04 December 2012 18:51 To: postgresql@foo.me.uk Cc: postgres performance list Subject: Re: [PERFORM] Slow query: bitmap scan troubles
Well, you don't need to put anything down. Most settings that change planner decisions can be tuned on per-quey basis by issuing set commands in given session. This should not affect other queries more than it is needed to run query in the way planner chooses.
>> But the row estimates are not precise at the top of the join/filter. >> It thinks there will 2120 rows, but there are only 11.
>Ah... I didn't spot that one...
Yes, you are right there - this is probably a slightly atypical query of this sort actually, 2012 is a pretty good guess.
On Claudio's suggestion I have found lots more things to read up on and am eagerly awaiting 6pm when I can bring the DB down and start tweaking. The effective_work_mem setting is going from 6Gb->88Gb which I think will make quite a difference.
I still can't quite wrap around my head why accessing an index is expected to use more disk access than doing a bitmap scan of the table itself, but I guess it does make a bit of sense if postgres assumes the table is more likely to be cached.
