Re: Scanning all partition when more than 100 items in "where id in ()" clause

On 2018/07/27 11:23, Stephen Frost wrote:
> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
>> /*
>> * Proof attempts involving large arrays in ScalarArrayOpExpr nodes are
>> * likely to require O(N^2) time, and more often than not fail anyway.
>> * So we set an arbitrary limit on the number of array elements that
>> * we will allow to be treated as an AND or OR clause.
>> * XXX is it worth exposing this as a GUC knob?
>> */
>> #define MAX_SAOP_ARRAY_SIZE 100
>
> Which certainly makes me think that comment in there might be worth
> something- perhaps we should make this a GUC and let users see just what
> would end up happening with a different choice. There could certainly
> be cases where it'd be better to work it out.
>
>> Not a bug, but a tradeoff. You'd be unhappy if the planner spent longer
>> devising the plan than it saved to eliminate the extra partitions.
>
> While I agree in concept, I'm awful curious how the "simpler" approach
> used when we hit the limit resulted in a 5x increase in planning time.
> Looks a bit like the extra time required to perform that elimination for
> at least a few more items would be saving us cycles somewhere else that
> are apparently pretty expensive.

The "simpler" approach in this case is predtest.c not pruning partitions
at all, which results in the planner creating a scan plan for every
partition, instead of just one in the case where the IN(..) list was
within the limit for the pruning to occur.

Fwiw, on the tiny machine I work on, attempting pruning with 100-item list
takes way longer than it takes the planner to just forget about pruning
and create a plan for all partitions. But that's just about the planning
time.

\d+ lt
Partition key: LIST (b)
Partitions: lt_1 FOR VALUES IN (1),
lt_10 FOR VALUES IN (10),
lt_2 FOR VALUES IN (2),
lt_3 FOR VALUES IN (3),
lt_4 FOR VALUES IN (4),
lt_5 FOR VALUES IN (5),
lt_6 FOR VALUES IN (6),
lt_7 FOR VALUES IN (7),
lt_8 FOR VALUES IN (8),
lt_9 FOR VALUES IN (9)

-- 100-item list allowing pruning to occur
explain analyze select * from lt where b in
(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1);

QUERY PLAN

─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
───────────────────────────────────────────────────────────────────────────────────
Append (cost=0.29..437.60 rows=5085 width=8) (actual time=0.096..138.388
rows=10000 loops=1)
-> Index Scan using lt_1_b_idx on lt_1 (cost=0.29..437.60 rows=5085
width=8) (actual time=0.087..56.177 rows=10000 loops=1)
Index Cond: (b = ANY
('{1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}'::integer[]))
Planning time: 24.349 ms
Execution time: 179.944 ms
(5 rows)

-- 101-item list disabling pruning
explain analyze select * from lt where b in
(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1);

QUERY PLAN

─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
─────────────────────────────────────────────────────────────────────────────────────
Append (cost=0.29..4463.78 rows=51360 width=8) (actual
time=0.172..141.214 rows=10000 loops=1)
-> Index Scan using lt_1_b_idx on lt_1 (cost=0.29..438.78 rows=5136
width=8) (actual time=0.153..55.516 rows=10000 loops=1)
Index Cond: (b = ANY
('{1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}'::integer[]))
-> Index Scan using lt_2_b_idx on lt_2 (cost=0.29..442.78 rows=5136
width=8) (actual time=0.091..0.091 rows=0 loops=1)
Index Cond: (b = ANY
('{1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}'::integer[]))
-> Index Scan using lt_3_b_idx on lt_3 (cost=0.29..446.78 rows=5136
width=8) (actual time=0.090..0.090 rows=0 loops=1)
Index Cond: (b = ANY
('{1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}'::integer[]))

[ Index Scan nodes of remaining patitions ]

Planning time: 7.146 ms
Execution time: 184.115 ms
(23 rows)

Oddly, that seems exactly the opposite of what OP is seeing.

Thanks,
Amit