Equality of columns isn't taken in account when performing partition pruning

From: Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Equality of columns isn't taken in account when performing partition pruning
Date: 2020-04-29 08:15:03
Message-ID: effa5924-6b58-b4a8-f282-763f6668e093@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

I noticed some limitation of Postgres optimizer: it doesn't take in
account information about equality of columns of joined tables when
performs partition pruning:

create table i (pk integer primary key) partition by range(pk);
create table i_1 partition of i for values from (0) to (10);
create table i_2 partition of i for values from (10) to (20);
create table o (pk integer primary key);

explain select * from o join i on o.pk = i.pk where i.pk between 0 and 9;
explain select * from o join i on o.pk = i.pk where o.pk between 0 and 9;

Plan for the first query is optimal and access only affected partition i_1:
 Nested Loop  (cost=4.44..53.26 rows=13 width=8)
   ->  Bitmap Heap Scan on i_1 i  (cost=4.29..15.02 rows=13 width=4)
         Recheck Cond: ((pk >= 0) AND (pk <= 9))
         ->  Bitmap Index Scan on i_1_pkey  (cost=0.00..4.29 rows=13
width=0)
               Index Cond: ((pk >= 0) AND (pk <= 9))
   ->  Index Only Scan using o_pkey on o  (cost=0.15..2.94 rows=1 width=4)
         Index Cond: (pk = i.pk)

But plan for the second query (although it is doing actually the same
things) affects both partitions:

 Nested Loop  (cost=4.44..91.89 rows=26 width=8)
   ->  Bitmap Heap Scan on o  (cost=4.29..15.02 rows=13 width=4)
         Recheck Cond: ((pk >= 0) AND (pk <= 9))
         ->  Bitmap Index Scan on o_pkey  (cost=0.00..4.29 rows=13 width=0)
               Index Cond: ((pk >= 0) AND (pk <= 9))
   ->  Append  (cost=0.15..5.89 rows=2 width=4)
         ->  Index Only Scan using i_1_pkey on i_1 (cost=0.15..2.94
rows=1 width=4)
               Index Cond: (pk = o.pk)
         ->  Index Only Scan using i_2_pkey on i_2 (cost=0.15..2.94
rows=1 width=4)
               Index Cond: (pk = o.pk)

I have not investigated yet how difficult it will be to fix it.
May be there is some person more familiar with this part of optimizer
than I.
But if nobody wants to look at it, I can try to investigate it myself.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Peter Eisentraut 2020-04-29 09:29:22 Re: BUG #16395: error when selecting generated column in a foreign table
Previous Message PG Bug reporting form 2020-04-29 06:02:36 BUG #16401: Minor misspelling for hint in Swedish