[BUGS] BUG #14565: query planner does not use partial index in partiton ifquery is performed on multiple partitions - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      14565
Logged by:          Zbigniew Szot
Email address:      zbigniew.szot@softiq.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';


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs