Message-ID: <5280E207.2040901@commontongue.com>
Date: Mon, 11 Nov 2013 14:56:23 +0100
From: Jan Walter <john@commontongue.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:24.0) Gecko/20100101 Thunderbird/24.1.0
MIME-Version: 1.0
To: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Size of IN list affects query plan
References: <527CEF87.6080008@commontongue.com>
 <27470.1383921102@sss.pgh.pa.us>
In-Reply-To: <27470.1383921102@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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