Re: Secondary index access optimizations

From: Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru>
To: Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com>
Cc: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
Subject: Re: Secondary index access optimizations
Date: 2017-09-07 15:58:31
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On 07.09.2017 13:00, Thomas Munro wrote:
> On Sun, Sep 3, 2017 at 4:34 AM, Konstantin Knizhnik
> <k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
>> Thank you for review.
>> I attached new version of the patch with
>> remove_restrictions_implied_by_constraints() function.
>> Concerning failed tests - this is actually result of this optimization:
>> extra filter conditions are removed from query plans.
>> Sorry, I have not included updated version of expected test output files to
>> the patch.
>> Now I did it.
> A regression test under contrib/postgres_fdw now fails:
> - Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T
> 1" WHERE (("C 1" IS NOT NULL))
> + Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
> (("C 1" IS NOT NULL)) is indeed redundant in that case, because column
> "C 1" was declared to be NOT NULL. But:
> 1. Do we want to go this far? Note that this is not involving
> inheritance and constraint exclusion. I don't immediately see any
> reason why not, but I'm not sure.
> 2. If yes, then this postgres_fdw test should be changed, because I
> think it was trying to demonstrate that IS NOT NULL expressions are
> sent to remote databases -- it would need to be changed so that it
> tries that with a column that is actually nullable.
I do not see any reasons why we should disable this optimization in case
of FDW.
And disabling it requires some extra efforts...

So I have updated test for postgres_fdw, replacing query
SELECT * FROM ft1 t1 WHERE c1 IS NOT NULL and c3 is not null;

Now it checks two things:
1. That not null check is passed to foreign server for nullable column (c3)
2. That not null check is excluded from query execution plan when it can
be omitted because column is not nullable.

Updated version of the patch is attached to this mail.
Also I added support of date type to operator_predicate_proof to be able
to imply (logdate <= '2017-03-31') from (logdate < '2017-04-01') .

Konstantin Knizhnik
Postgres Professional:
The Russian Postgres Company

Attachment Content-Type Size
optimizer-3.patch text/x-patch 16.2 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Ildus Kurbangaliev 2017-09-07 16:42:36 Custom compression methods
Previous Message Nikhil Sontakke 2017-09-07 15:58:05 Re: logical decoding of two-phase transactions