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

Peter Geoghegan <pg@bowt.ie>

From: Peter Geoghegan <pg@bowt.ie>

To: Tom Lane <tgl@sss.pgh.pa.us>

Cc: Robert Haas <robertmhaas@gmail.com>, Alexander Korotkov <aekorotkov@gmail.com>, Andrei Lepikhov <lepihov@gmail.com>, Alena Rybakina <a.rybakina@postgrespro.ru>, jian he <jian.universality@gmail.com>, Nikolay Shaplov <dhyan@nataraj.su>, pgsql-hackers@lists.postgresql.org, pgsql-hackers@postgresql.org, Marcos Pegoraro <marcos@f10.com.br>, teodor@sigaev.ru, Peter Eisentraut <peter@eisentraut.org>, Ranier Vilela <ranier.vf@gmail.com>

Date: 2024-10-07T16:41:12Z

Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Make group_similar_or_args() reorder clause list as little as possible
- 775a06d44c04 18.0 landed
Allow usage of match_orclause_to_indexcol() for joins
- 627d63419e22 18.0 landed
Skip not SOAP-supported indexes while transforming an OR clause into SAOP
- 5bba0546eecb 18.0 landed
Remove the wrong assertion from match_orclause_to_indexcol()
- d4d11940df94 18.0 landed
Teach bitmap path generation about transforming OR-clauses to SAOP's
- ae4569161a27 18.0 landed
Transform OR-clauses to SAOP's during index matching
- d4378c0005e6 18.0 landed
Fix the value of or_to_any_transform_limit in postgresql.conf.sample
- 2af75e117478 17.0 landed
Transform OR clauses to ANY expression
- 72bd38cc99a1 17.0 landed
MergeAttributes code deduplication
- 64444ce071f6 17.0 cited
SEARCH and CYCLE clauses
- 3696a600e229 14.0 cited
Improve estimation of OR clauses using extended statistics.
- 25a9e54d2db3 14.0 cited
Teach btree to handle ScalarArrayOpExpr quals natively.
- 9e8da0f75731 9.2.0 cited
Revise collation derivation method and expression-tree representation.
- b310b6e31ce5 9.1.0 cited
Instead of trying to force WHERE clauses into CNF or DNF normal form,
- 9888192fb773 8.0.0 cited

On Mon, Oct 7, 2024 at 12:02 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Peter Geoghegan <pg@bowt.ie> writes:
> > To be clear, I don't think that it's essential that we have equivalent
> > behavior in those cases where the patch applies its transformations. I
> > have no objections to committing the patch without any handling for
> > that.
>
> Oy.  I don't agree with that *at all*.  An "optimization" that changes
> query semantics is going to be widely seen as a bug.

I think that you must have misinterpreted what I meant by "equivalent
behavior". The context was important. I really meant: "Ideally, the
patch's transformations would produce an equivalent execution strategy
to what we already get in when IN() is used directly, *even in the
presence of constants of mixed though related types*. Ideally, the
final patch would somehow be able to generate a SAOP with one array of
the same common type in cases where an analogous IN() query can do the
same. But I'm not going to insist on adding something for that now."

Importantly, I meant equivalent outcome in terms of execution
strategy, across similar queries where the patch sometimes succeeds in
generating a SAOP, and sometimes fails -- I wasn't trying to say
anything about query semantics. This wasn't intended to be a rigorous
argument (if it was then I'd have explained why my detailed and
rigorous proposal didn't break query semantics).

-- 
Peter Geoghegan