Horton

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

Peter Geoghegan <pg@bowt.ie>

View thread
From: Peter Geoghegan <pg@bowt.ie>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Alena Rybakina <a.rybakina@postgrespro.ru>, Alexander Korotkov <aekorotkov@gmail.com>, pgsql-hackers@postgresql.org, "Finnerty, Jim" <jfinnert@amazon.com>, Marcos Pegoraro <marcos@f10.com.br>, Andrey Lepikhov <a.lepikhov@postgrespro.ru>, teodor@sigaev.ru, Ranier Vilela <ranier.vf@gmail.com>, Tomas Vondra <tomas.vondra@enterprisedb.com>, Peter Eisentraut <peter@eisentraut.org>
Date: 2023-10-30T16:01:29Z
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 →

  1. Make group_similar_or_args() reorder clause list as little as possible

  2. Allow usage of match_orclause_to_indexcol() for joins

  3. Skip not SOAP-supported indexes while transforming an OR clause into SAOP

  4. Remove the wrong assertion from match_orclause_to_indexcol()

  5. Teach bitmap path generation about transforming OR-clauses to SAOP's

  6. Transform OR-clauses to SAOP's during index matching

  7. Fix the value of or_to_any_transform_limit in postgresql.conf.sample

  8. Transform OR clauses to ANY expression

  9. MergeAttributes code deduplication

  10. SEARCH and CYCLE clauses

  11. Improve estimation of OR clauses using extended statistics.

  12. Teach btree to handle ScalarArrayOpExpr quals natively.

  13. Revise collation derivation method and expression-tree representation.

  14. Instead of trying to force WHERE clauses into CNF or DNF normal form,

On Mon, Oct 30, 2023 at 6:40 AM Robert Haas <robertmhaas@gmail.com> wrote:
> I agree with that, with some caveats, mainly that the reverse is to
> some extent also true. Maybe not completely, because arguably the
> ANY() formulation should just be straight-up easier to deal with, but
> in principle, the two are equivalent and it shouldn't matter which
> representation we pick.

I recently looked into MySQL's handling of these issues, which is more
mature and better documented than what we can do. EXPLAIN ANALYZE will
show an IN() list as if the query had been written as a list of ORs,
even though it can efficiently execute an index scan that uses
IN()/"OR var = constant" lists. So I agree with what you said here. It
is perhaps just as accident of history that we're talking about
converting to a ScalarArrayOpExpr, rather than talking about
converting to some other clause type that we associate with OR lists.

The essential point is that there ought to be one clause type that is
easier to deal with.

> But practically, it may, and we need to be sure that we don't put in
> place a translation that is theoretically a win but in practice leads
> to large regressions. Avoiding regressions here is more important than
> capturing all the possible gains. A patch that wins in some scenarios
> and does nothing in others can be committed; a patch that wins in even
> more scenarios but causes serious regressions in some cases probably
> can't.

I agree. Most of the really big wins here will come from simple
transformations. I see no reason why we can't take an incremental
approach. In fact I think we almost have to do so, since as I
understand it the transformations are just infeasible in certain
extreme cases.

-- 
Peter Geoghegan