From: Jan Walter <john@commontongue.com>
To: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Size of IN list affects query plan
Date: Mon, 11 Nov 2013 14:56:23 +0100
Message-ID: <5280E207.2040901@commontongue.com> (raw)
In-Reply-To: <27470.1383921102@sss.pgh.pa.us>
References: <527CEF87.6080008@commontongue.com>
<27470.1383921102@sss.pgh.pa.us>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Thanks for your comments.
On 8.11.2013 15:31, Tom Lane wrote:
> AFAICT, the reason the second plan is slow is the large number of
> checks of the IN list. The planner does account for the cost of that,
> but it's drastically underestimating that cost relative to the cost of
> I/O for the heap and index accesses. I suppose that your test case is
> fully cached in memory, which helps make the CPU costs more important
> than I/O costs. If you think this is representative of your real
> workload, then you need to decrease random_page_cost (and maybe
> seq_page_cost too) to make the cost estimates correspond better to
> that reality.
I am not sure I understand it well - in the first case (fast query),
cache is utilized in a better way? Going down with random_page_cost
gives me fast query plans with big lists as you expected.
I tested the slow query on different machines with (default) settings of
seq_page_cost, and I am getting those fast query plans, too, so I am
curious what else could affect that (same db vacuum analyzed).
Anyway it opens a question if big (tens to hundreds) IN lists is a bad
practice, or just something that has to be used carefully. I have to
admit I am surprised that this rather standard technique leads to so
wide range of performance.
On 8.11.2013 15:31, bricklen wrote:
> Looking at your EXPLAIN ANALYZE plan I was immediately reminded of
> this article
> http://www.datadoghq.com/2013/08/100x-faster-postgres-performance-by-changing-1-line/,
> where changing the array to a VALUES() clause was a huge win for them.
Yeah, I saw it before. Unfortunately that does not help significantly in
my case.
Jan
--
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: john@commontongue.com
Subject: Re: Size of IN list affects query plan
In-Reply-To: <5280E207.2040901@commontongue.com>
* 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