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