Horton

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

Peter Geoghegan <pg@bowt.ie>

View thread
From: Peter Geoghegan <pg@bowt.ie>
To: "a.rybakina" <a.rybakina@postgrespro.ru>
Cc: "Finnerty, Jim" <jfinnert@amazon.com>, Marcos Pegoraro <marcos@f10.com.br>, Andrey Lepikhov <a.lepikhov@postgrespro.ru>, pgsql-hackers@postgresql.org, teodor@sigaev.ru, Ranier Vilela <ranier.vf@gmail.com>, Tomas Vondra <tomas.vondra@enterprisedb.com>
Date: 2023-08-20T22:11:24Z
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 Thu, Aug 17, 2023 at 3:08 AM a.rybakina <a.rybakina@postgrespro.ru> wrote:
> This is an interesting feature. I didn't notice this function before, I studied many times consider_new_or_cause, which were called there. As far as I know, there is a selectivity calculation going on there, but as far as I remember, I called it earlier after my conversion, and unfortunately it didn't solve my problem with calculating selectivity. I'll reconsider it again, maybe I can find something I missed.

Back in 2003, commit 9888192f removed (or at least simplified) what
were then called "CNF/DNF CONVERSION ROUTINES". Prior to that point
the optimizer README had something about leaving clause lists
un-normalized leading to selectivity estimation problems. Bear in mind
that this is a couple of years before ScalarArrayOpExpr was first
invented. Apparently even back then "The OR-of-ANDs format is useful
for indexscan implementation". It's possible that that old work will
offer some hints on what to do now.

In a way it's not surprising that work in this area would have some
impact on selectivies. The surprising part is the extent of the
problem, I suppose.

I see that a lot of the things in this area are just used by BitmapOr
clauses, such as build_paths_for_OR() -- but you're not necessarily
able to use any of that stuff. Also, choose_bitmap_and() has some
stuff about how it compensates to avoid "too-small selectivity that
makes a redundant AND step look like it reduces the total cost". It
also mentions some problems with match_join_clauses_to_index() +
extract_restriction_or_clauses(). Again, this might be a good place to
look for more clues.

-- 
Peter Geoghegan