From: Philip Scott <pscott@foo.me.uk>
To: 'Vitalii Tymchyshyn' <tivv00@gmail.com>
To: postgresql@foo.me.uk
Cc: 'postgres performance list' <pgsql-performance@postgresql.org>
Subject: Re: Slow query: bitmap scan troubles
Date: Tue, 4 Dec 2012 18:55:17 -0000
Message-ID: <098801cdd250$e70232b0$b5069810$@foo.me.uk> (raw)
In-Reply-To: <CABWW-d2gK6=AOBWxJij8GYS6CQC4+p7xWDsr4FSs2-BaXDQMsA@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>
<096f01cdd247$722264a0$56672de0$@foo.me.uk>
<CABWW-d2gK6=AOBWxJij8GYS6CQC4+p7xWDsr4FSs2-BaXDQMsA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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.
Best regards, Vitalii Tymchyshyn
2012/12/4 <postgresql@foo.me.uk>
>> 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
--
Best regards,
Vitalii Tymchyshyn
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, tivv00@gmail.com, postgresql@foo.me.uk
Subject: Re: Slow query: bitmap scan troubles
In-Reply-To: <098801cdd250$e70232b0$b5069810$@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