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

On 2017/02/23 20:10, zbigniew.szot@softiq.pl wrote:
> 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';

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




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