Re: Secondary index access optimizations

On 11/06/2017 04:27 AM, Thomas Munro wrote:
> On Fri, Sep 8, 2017 at 3:58 AM, Konstantin Knizhnik
> <k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
>> 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') .
> Hi Konstantin,
>
> Is there any reason why you don't want to split this into two separate
> proposals? One for remove_restrictions_implied_by_constraints() and
> one for the operator_predicate_proof() changes.
>
> Your v3 patch breaks the new partition_join test (the recently
> committed partition-wise join stuff), as far as I can tell in a good
> way. Can you please double check those changes and post an updated
> patch?
>
Hi Thomas.

The primary idea of this patch was to provide more efficient plans for queries on partitioned tables.
So remove_restrictions_implied_by_constraints() removes redundant predicate checks.
But it doesn't work for standard Postgres 10 partitioning, because here constraints are set using intervals with open high boundary and original version of
operator_predicate_proof() is not able to handle this case.

I have explained this problem in my previous e-mails in this thread.
This is why I have changed operator_predicate_proof() to correctly handle this case.

If you think this patch should be splitted into two, ok: I can do it.
I just want to notice that without patching operator_predicate_proof() it may give not positive effect for standard partitioning,
which I expect to be the most popular use case where this optimization may have an effect.

Concerning broken partition_join test: it is "expected" failure: my patch removes from the plans redundant checks.
So the only required action is to update expected file with results.
Attached please find updated patch.

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company