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-11-06 13:13:58
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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:
The Russian Postgres Company

Attachment Content-Type Size
optimizer-4.patch text/x-diff 18.2 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2017-11-06 13:19:37 Re: Restricting maximum keep segments by repslots
Previous Message Fabien COELHO 2017-11-06 13:12:46 Re: pow support for pgbench