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

On 2017/02/23 20:10, zbigniew(dot)szot(at)softiq(dot)pl wrote:
> The following bug has been logged on the website:
>
> Bug reference: 14565
> Logged by: Zbigniew Szot
> Email address: zbigniew(dot)szot(at)softiq(dot)pl
> PostgreSQL version: 9.5.6
> Operating system: linux and AWS cloud (9.6.1)
> Description:
>
> drop schema if exists bug_showcase cascade;
> create schema bug_showcase;
> set search_path to bug_showcase;
>
> -- crate table
> CREATE TABLE test_table (
> chec_key UUID NOT NULL ,
> some_date date,
> some_data_2 varchar,
> some_data_3 varchar,
> some_data_4 varchar
> );
> -- partiton the table + indexes in partitions
> DO $$
> BEGIN
> FOR i IN 0..15 LOOP
> EXECUTE 'CREATE TABLE test_table_'||to_hex(i)||' ( CHECK (chec_key >=
> UUID '''||to_hex(i)||'0000000-0000-0000-0000-000000000000'' AND chec_key <=
> UUID '''||
> to_hex(i)||'fffffff-ffff-ffff-ffff-ffffffffffff'' ) ) INHERITS
> (test_table);';
> EXECUTE 'CREATE INDEX test_table_'||to_hex(i)||'_brin on
> test_table_'||to_hex(i)||' USING brin (chec_key, some_date);';
> END LOOP;
> END$$;
>
> -- this index would be used instead of partial ones but thats not what we
> need
> drop index test_table_4_brin;
>
> -- create partial indexes in one of partitions
> DO $$
> BEGIN
> FOR i IN 0..15 LOOP
> EXECUTE 'create index partial_not_working_'||to_hex(i)||' on
> test_table_4 USING brin ( chec_key, some_date) where (chec_key >= UUID
> ''4'||to_hex(i)||'000000-0000-0000-0000-000000000000'' AND chec_key <= UUID
> ''4'||
> to_hex(i)||'ffffff-ffff-ffff-ffff-ffffffffffff'' );';
> END LOOP;
> END$$;
>
> -- populate table
>
> insert into test_table_1 (chec_key , some_date , some_data_2 ,
> some_data_3,some_data_4 ) select
> uuid('1'||lpad(to_hex(i),5,'0')||'00-0000-4000-a000-000000000000'),date
> '2015-10-1' + random() * interval '2 days' ,md5(random()::text)
> ,md5(random()::text),md5(random()::text) from generate_Series(0,1048575) i
> ;
> insert into test_table_3 (chec_key , some_date , some_data_2 ,
> some_data_3,some_data_4 ) select
> uuid('3'||lpad(to_hex(i),5,'0')||'00-0000-4000-a000-000000000000'),date
> '2015-10-1' + random() * interval '2 days' ,md5(random()::text)
> ,md5(random()::text),md5(random()::text) from generate_Series(0,1048575) i
> ;
> insert into test_table_4 (chec_key , some_date , some_data_2 ,
> some_data_3,some_data_4 ) select
> uuid('4'||lpad(to_hex(i),5,'0')||'00-0000-4000-a000-000000000000'),date
> '2015-10-1' + random() * interval '2 days' ,md5(random()::text)
> ,md5(random()::text),md5(random()::text) from generate_Series(0,1048575) i
> ;
> insert into test_table_7 (chec_key , some_date , some_data_2 ,
> some_data_3,some_data_4 ) select
> uuid('7'||lpad(to_hex(i),5,'0')||'00-0000-4000-a000-000000000000'),date
> '2015-10-1' + random() * interval '2 days' ,md5(random()::text)
> ,md5(random()::text),md5(random()::text) from generate_Series(0,1048575) i
> ;
>
> -- .. ;-)
> analyse test_table;
>
> -- make strong sugestion to use indexes if possible
> set enable_seqscan = off;
> -- this one uses partial_not_working_4
> explain select * from test_table where chec_key in
> ('4400df00-0000-4000-a000-000000000000' )and some_date <'2015-11-02';
> -- this one uses partial_not_working_4
> explain select * from test_table where chec_key in
> ('4400df00-0000-4000-a000-000000000000','4401df00-0000-4000-a000-000000000000'
> )and some_date <'2015-11-02';
>
> -- 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.

The WHERE condition does not imply partial_not_working_4's predicate, so
it cannot possibly be used. The index does not contain entries for some
of the rows being requested by the query (i.e. those for which chec_key =
'1400df00-0000-4000-a000-000000000000').

A simpler example:

create table foo (a) as select generate_series(1, 100000);
create index on foo (a) where a >= 1 and a < 10;

explain (costs off) select * from foo where a in (1, 9);
QUERY PLAN
----------------------------------------------------
Bitmap Heap Scan on foo
Recheck Cond: (a = ANY ('{1,9}'::integer[]))
-> Bitmap Index Scan on foo_a_idx
Index Cond: (a = ANY ('{1,9}'::integer[]))
(4 rows)

-- foo_a_idx does not contain 10
explain (costs off) select * from foo where a in (1, 10);
QUERY PLAN
-------------------------------------------
Seq Scan on foo
Filter: (a = ANY ('{1,10}'::integer[]))
(2 rows)

Thanks,
Amit