Re: POC, WIP: OR-clause support for indexes

From: Alena Rybakina <a(dot)rybakina(at)postgrespro(dot)ru>
To: Andrei Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru>, Robert Haas <robertmhaas(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org, Alexander Korotkov <aekorotkov(at)gmail(dot)com>
Cc: Peter Geoghegan <pg(at)bowt(dot)ie>, "Finnerty, Jim" <jfinnert(at)amazon(dot)com>, Marcos Pegoraro <marcos(at)f10(dot)com(dot)br>, teodor(at)sigaev(dot)ru, Ranier Vilela <ranier(dot)vf(at)gmail(dot)com>, Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com>, Peter Eisentraut <peter(at)eisentraut(dot)org>
Subject: Re: POC, WIP: OR-clause support for indexes
Date: 2023-11-30 10:57:26
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On 30.11.2023 11:00, Alena Rybakina wrote:
> Hi!
>>> Honestly, it seems very hard to avoid the conclusion that this
>>> transformation is being done at too early a stage. Parse analysis is
>>> not the time to try to do query optimization. I can't really believe
>>> that there's a way to produce a committable patch along these lines.
>>> Ideally, a transformation like this should be done after we know what
>>> plan shape we're using (or considering using), so that we can make
>>> cost-based decisions about whether to transform or not. But at the
>>> very least it should happen somewhere in the planner. There's really
>>> no justification for parse analysis rewriting the SQL that the user
>>> entered.
>> Here, we assume that array operation is generally better than many ORs.
>> As a result, it should be more effective to make OR->ANY
>> transformation in the parser (it is a relatively lightweight
>> operation here) and, as a second phase, decompose that in the optimizer.
>> We implemented earlier prototypes in different places of the
>> optimizer, and I'm convinced that only this approach resolves the
>> issues we found.
>> Does this approach look weird? Maybe. We can debate it in this thread.
> I think this is incorrect, and the example of A. Korotkov confirms
> this. If we perform the conversion at the parsing stage, we will skip
> the more important conversion using OR expressions. I'll show you in
> the example below.
> First of all, I will describe my idea to combine two approaches to
> obtaining plans with OR to ANY transformation and ANY to OR
> transformation. I think they are both good, and we can't work with
> just one of them, we should consider both the option of OR
> expressions, and with ANY.

Just in case, I have attached a patch or->any transformation where this
happens at the index creation stage.

I get diff file during make check, but judging by the changes, it shows
that the transformation is going well. I also attached it.

Alena Rybakina
Postgres Professional

Attachment Content-Type Size
v14-0001-Transform-OR-clause-to ANY-expressions.patch text/x-patch 41.6 KB
regression.diffs text/plain 8.0 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Alexander Lakhin 2023-11-30 11:00:01 Re: Is this a problem in GenericXLogFinish()?
Previous Message Alvaro Herrera 2023-11-30 10:57:05 Re: GUC names in messages