Hi, Andrei! On Mon, 24 Mar 2025 at 14:10, Andrei Lepikhov <lepi...@gmail.com> wrote: > > Hi, > > Playing with the feature, I found a slightly irritating permutation - > even if this code doesn't group any clauses, it may permute positions of > the quals. See: > > DROP TABLE IF EXISTS main_tbl; > CREATE TABLE main_tbl(id bigint, hundred int, thousand int); > CREATE INDEX mt_hundred_ix ON main_tbl(hundred); > CREATE INDEX mt_thousand_ix ON main_tbl(thousand); > VACUUM (ANALYZE) main_tbl; > > SET enable_seqscan = off; > EXPLAIN (COSTS OFF) > SELECT m.id, m.hundred, m.thousand > FROM main_tbl m WHERE (m.hundred < 2 OR m.thousand < 3); > > Bitmap Heap Scan on public.main_tbl m > Output: id, hundred, thousand > Recheck Cond: ((m.thousand < 3) OR (m.hundred < 2)) > -> BitmapOr > -> Bitmap Index Scan on mt_thousand_ix > Index Cond: (m.thousand < 3) > -> Bitmap Index Scan on mt_hundred_ix > Index Cond: (m.hundred < 2) > > Conditions on the columns "thousand" and "hundred" changed their places > according to the initial positions defined in the user's SQL. > It isn't okay. I see that users often use the trick of "OR order" to > avoid unnecessary calculations - most frequently, Subplan evaluations. > So, it makes sense to fix. > In the attachment, I have included a quick fix for this issue. Although > many tests returned to their initial (pre-18) state, I added some tests > specifically related to this issue to make it clearer.
I looked at your patch and have no objections to it. However it's clearly stated in PostgreSQL manual that nothing about the OR order is warranted [1]. So changing OR order was (and is) ok and any users query tricks about OR order may work and may not work. [1] https://www.postgresql.org/docs/17/sql-expressions.html#SYNTAX-EXPRESS-EVAL Regards, Pavel Borisov Supabase
