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

On Wed, 29 Apr 2020 at 20:15, Konstantin Knizhnik
<k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
>
> 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:

It's not a bug. It's just an optimisation that we don't do. Also, it's
not limited to partition pruning, as if the "i" table had been a
normal table with an index then you may also want to complain that
when your hash join hashes on "i" (perhaps the planner thinks it still
has fewer rows than "o") that it does not perform the "i.pk BETWEEN 0
AND 9" during the scan with the 2nd query.

I did talk about a possible way to fix this back in [1], but I think
we'll need to build some infrastructure to optimise finding matching
expressions in a List beforehand. The code I had put together was a
bit inefficient due to our lack of an efficient way to find an
expression in a List.

David

[1] https://www.postgresql.org/message-id/flat/30810.1449335261%40sss.pgh.pa.us#906319f5e212fc3a6a682f16da079f04