From: Philip Scott <pscott@foo.me.uk>
To: 'postgres performance list' <pgsql-performance@postgresql.org>
Subject: Re: Slow query: bitmap scan troubles
Date: Tue, 4 Dec 2012 17:35:32 -0000
Message-ID: <096a01cdd245$c4a49830$4dedc890$@foo.me.uk> (raw)
In-Reply-To: <CAGTBQpbqzBf5545-qxdLQHbLyBBOvxn3hEtjQmqEp+UJPKsSrA@mail.gmail.com>
References: <092a01cdd230$ff6143c0$fe23cb40$@foo.me.uk>
<CAGTBQpaKOfZs+qy+S7CCPcTg7PYOrwgcQAXgWV88cQTn-BFstg@mail.gmail.com>
<CAMkU=1wEc1cCeUdR1oZ3Q2fe2pGj6tvrwRcY2obbfEk8LcNypQ@mail.gmail.com>
<CAGTBQpbqzBf5545-qxdLQHbLyBBOvxn3hEtjQmqEp+UJPKsSrA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pscott@foo.me.uk
Subject: Re: Slow query: bitmap scan troubles
In-Reply-To: <096a01cdd245$c4a49830$4dedc890$@foo.me.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox