Re: BUG #14565: query planner does not use partial index in partiton if query is performed on multiple partitions

Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> writes:
> On 2017/02/23 20:10, zbigniew(dot)szot(at)softiq(dot)pl wrote:
>> -- this one DOES NOT use partial_not_working_4 .. bug or feature ? ;-)
>> explain select * from test_table where chec_key in
>> ('4400df00-0000-4000-a000-000000000000'
>> ,'1400df00-0000-4000-a000-000000000000')and some_date <'2015-11-02';

> Not a bug, I'd think.

After looking at this more closely, I think the OP is probably wishing
that the planner would consider a BitmapOr plan on two different partial
indexes. You can get it to consider that if the query is phrased as an
OR, but not when it's written like this with IN (which will get converted
to an "= ANY(ARRAY[])" condition).

Trivial example:

regression=# create table foo (f1 int, f2 text);
CREATE TABLE
regression=# create index on foo(f1) where f1 >= 0 and f1 < 10;
CREATE INDEX
regression=# create index on foo(f1) where f1 >= 10 and f1 < 20;
CREATE INDEX
regression=# explain select * from foo where f1 in (7, 11);
QUERY PLAN
------------------------------------------------------
Seq Scan on foo (cost=0.00..25.88 rows=13 width=36)
Filter: (f1 = ANY ('{7,11}'::integer[]))
(2 rows)

regression=# explain select * from foo where f1 = 7 or f1 = 11;
QUERY PLAN
--------------------------------------------------------------------------------
Bitmap Heap Scan on foo (cost=8.27..19.00 rows=13 width=36)
Recheck Cond: ((f1 = 7) OR (f1 = 11))
-> BitmapOr (cost=8.27..8.27 rows=13 width=0)
-> Bitmap Index Scan on foo_f1_idx (cost=0.00..4.13 rows=6 width=0)
Index Cond: (f1 = 7)
-> Bitmap Index Scan on foo_f1_idx1 (cost=0.00..4.13 rows=6 width=0)
Index Cond: (f1 = 11)
(7 rows)

You could certainly claim it's a bug that these two phrasings of the query
aren't treated 100% identically, but I'd tell you to get lost. The IN
planning code is designed to handle fairly large numbers of IN items
without planner performance going into the toilet; it's not practical
for it to consider a different index for each item.

The underlying reason why I'm not very excited about this issue is that
I think the above-depicted index design is fundamentally stupid anyway.
It's much simpler, both for you and for the planner, just to make one
non-partial index on the whole range of f1. And I know of no reason to
believe that multiple partial indexes would outperform that design for
any ordinary workload.

regards, tom lane