Re: Secondary index access optimizations

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;
with
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: http://www.postgrespro.com
The Russian Postgres Company