Re: Slow query: bitmap scan troubles

From: "Philip Scott" <pscott(at)foo(dot)me(dot)uk>
To: "'postgres performance list'" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Slow query: bitmap scan troubles
Date: 2012-12-04 17:35:32
Message-ID: 096a01cdd245$c4a49830$4dedc890$@foo.me.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-performance


>> 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.

It's all quite, quite fascinating :)

I'll let you know how it goes.

- Phil

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2012-12-04 17:41:31 Re: Patch for checking file parameters to psql before password prompt
Previous Message Robert Haas 2012-12-04 17:34:51 Re: WIP: store additional info in GIN index

Browse pgsql-performance by date

  From Date Subject
Next Message postgresql 2012-12-04 17:47:29 Re: Slow query: bitmap scan troubles
Previous Message Claudio Freire 2012-12-04 17:25:56 Re: Slow query: bitmap scan troubles