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

On Fri, Mar 28, 2025 at 1:32 PM Andrei Lepikhov <lepihov(at)gmail(dot)com> wrote:
> On 3/28/25 00:18, Alexander Korotkov wrote:
> > The attached patch changes the reordering algorithm of
> > group_similar_or_args() in the following way. We reorder each group
> > of similar clauses so that the first item of the group stays in place,
> > but all the other items are moved after it. So, if there are no
> > similar clauses, the order of clauses stays the same. When there are
> > some groups, only required reordering happens while the rest of the
> > clauses remain in their places.
> The patch looks good to me from a technical perspective. But it seems
> like an overkill, isn't it?
> You introduce additional CPU-consuming operations in the planning OR
> operations.

I don't think this is going to be CPU-consuming. I don't think this
is going to be measurable. This patch introduces one additional pass
over array of OrArgIndexMatch'es, and qsort of them. I think I've
seen places where we spend quadratic time over the number of
OR-clauses. Even calls of match_index_to_operand() for every clause
and every index look way more expensive.

> My point is: 1) as Pavel has mentioned, Postgres doesn't guarantee the
> evaluation/output order of the clauses at all. 2) we need that to keep
> regression tests stable (don't forget extensions' and forks' developers
> too). But it should be done once if we have no fluidity in OR clauses
> order in general.
> The trade-off with tricky query writers and regression tests may be
> preserving the order until OR->ANY has happened. If it has happened,
> just ensure the order is determined somehow. Except that, any other
> spending on CPU cycles seems too expensive.

I think my patch gives better determinism too. For instance, output
order doesn't depend on order of indexes in rel->indexlist.

------
Regards,
Alexander Korotkov
Supabase