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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
