CLUSTER sort on abbreviated expressions is broken
Thomas Munro <thomas.munro@gmail.com>
From: Thomas Munro <thomas.munro@gmail.com>
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2022-04-03T04:05:00Z
Lists: pgsql-hackers
Attachments
- 0001-Add-simple-test-for-CLUSTER-on-expression-indexes.patch (text/x-patch) patch 0001
Hi, Independently of a problem with a recent commit, it seems that $SUBJECT in all releases (well, I only tested as far back as 11). I attach an addition to the tests to show this, but here's a stand-alone repro: DROP TABLE IF EXISTS clstr_expression; CREATE TABLE clstr_expression(id serial primary key, a int, b text COLLATE "C"); INSERT INTO clstr_expression(a, b) SELECT g.i % 42, 'prefix'||g.i FROM generate_series(1, 133) g(i); CREATE INDEX clstr_expression_minus_a ON clstr_expression ((-a), b); CREATE INDEX clstr_expression_upper_b ON clstr_expression ((upper(b))); CLUSTER clstr_expression USING clstr_expression_minus_a; WITH rows AS (SELECT ctid, lag(a) OVER (ORDER BY ctid) AS la, a FROM clstr_expression) SELECT * FROM rows WHERE la < a; All good, and now for the part that I think is misbehaving: CLUSTER clstr_expression USING clstr_expression_upper_b; WITH rows AS (SELECT ctid, lag(b) OVER (ORDER BY ctid) AS lb, b FROM clstr_expression) SELECT * FROM rows WHERE upper(lb) > upper(b); That should produce no rows. It works as expected if you SET enable_seqscan = off and re-run CLUSTER, revealing that it's the seq-scan-and-sort strategy that is broken. It also works as expected for non-yet-abbreviatable collations.
Commits
-
Fix CLUSTER tuplesorts on abbreviated expressions.
- 8ab0ebb9a842 15.0 landed
- e4521841a1ee 14.3 landed
- 1272630a2497 13.7 landed
- 5487585e376d 12.11 landed
- adb2d84fc2d6 11.16 landed
- a903895b3fe4 10.21 landed
-
Fix tuplesort optimization for CLUSTER-on-expression.
- cc58eecc5d75 15.0 cited