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

From: Alena Rybakina <lena(dot)ribackina(at)yandex(dot)ru>
To: Ranier Vilela <ranier(dot)vf(at)gmail(dot)com>
Cc: Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>, Marcos Pegoraro <marcos(at)f10(dot)com(dot)br>, Andrey Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru>, teodor(at)sigaev(dot)ru, Peter Geoghegan <pg(at)bowt(dot)ie>
Subject: Re: POC, WIP: OR-clause support for indexes
Date: 2023-06-29 15:15:05
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

> At the moment, I'm not sure that the constant is the right number
> for applying transformations, so I'm in search of it, to be
> honest. I will post my observations on this issue later. If you
> don't mind, I'll leave the constant equal to 15 for now.
> It's hard to predict. Perhaps accounting for time on each benchmark
> could help decide.
I will try to test on JOB [1] (because queries are difficult for the
optimizer due to the significant number of joins and correlations
contained in the dataset) and
tpcds [2] (the benchmark I noticed contains a sufficient number of
queries with "or" expressions).

> Sorry, I don't understand well enough what is meant by points
> "Eliminate unnecessary variables" and "Eliminate unnecessary
> expressions". Can you explain in more detail?
> One example is array_type.
> As you can see in v2 and v3 it no longer exists.
I get it. Honestly, I was guided by the example of converting "IN" to
"ANY" (transformAExprIn), at least the part of the code when we
specifically convert the expression to ScalarArrayOpExpr.

Both there and here, we first look for a common type for the collected
constants, and if there is one, then we try to find the type for the
array structure.

Only I think in my current patch it is also worth returning to the
original version in this place, since if it is not found, the
ScalarArrayOpExpr generation function will be processed incorrectly and
the request may not be executed at all, referring to the error that it
is impossible to determine the type of node (ERROR: unrecognized node
type. )

At the same time we are trying to do this transformation for each group.
The group here implies that these are combined "or" expressions on the
common left side, and at the same time we consider
only expressions that contain a constant and only equality.

What else should be taken into account is that we are trying to do this
processing before forming a BoolExpr expression (if you notice, then
after any outcome we call the makeBoolExpr function,
which just forms the "Or" expression, as in the original version,
regardless of what type of expressions it combines.

> As I said earlier, these are just suggestions.
> But thinking about it now, I think they can be classified as bad early
> optimizations.
Thank you again for your interest in this problem and help. Yes, I think
so too)



Alena Rybakina
Postgres Professional

Attachment Content-Type Size
0001-Replace-OR-clause-to-ANY-expressions.patch text/x-patch 10.0 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Alena Rybakina 2023-06-29 15:16:50 Re: POC, WIP: OR-clause support for indexes
Previous Message Jonathan S. Katz 2023-06-29 14:12:54 plan_create_index_workers doesn't account for TOAST