Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitcef82ed

Browse files
committed
Fix CLUSTER on expression indexes.
Since the introduction of different slot types, in1a0586d, wecreate a virtual slot in tuplesort_begin_cluster(). While that looksright, it unfortunately doesn't actually work, as ExecStoreHeapTuple()is used to store tuples in the slot. Unfortunately no regression testsfor CLUSTER on expression indexes existed so far.Fix the slot type, and add bare bones tests for CLUSTER on expressionindexes.Reported-By: Justin PryzbyAuthor: Andres FreundDiscussion:https://postgr.es/m/20191011210320.GS10470@telsasoft.comBackpatch: 12, like1a0586d
1 parent3a0e857 commitcef82ed

File tree

3 files changed

+145
-1
lines changed

3 files changed

+145
-1
lines changed

‎src/backend/utils/sort/tuplesort.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -933,7 +933,7 @@ tuplesort_begin_cluster(TupleDesc tupDesc,
933933
* scantuple has to point to that slot, too.
934934
*/
935935
state->estate=CreateExecutorState();
936-
slot=MakeSingleTupleTableSlot(tupDesc,&TTSOpsVirtual);
936+
slot=MakeSingleTupleTableSlot(tupDesc,&TTSOpsHeapTuple);
937937
econtext=GetPerTupleExprContext(state->estate);
938938
econtext->ecxt_scantuple=slot;
939939
}

‎src/test/regress/expected/cluster.out

Lines changed: 107 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -466,10 +466,117 @@ where row(hundred, thousand, tenthous) <= row(lhundred, lthousand, ltenthous);
466466

467467
reset enable_indexscan;
468468
reset maintenance_work_mem;
469+
-- test CLUSTER on expression index
470+
CREATE TABLE clstr_expression(id serial primary key, a int, b text COLLATE "C");
471+
INSERT INTO clstr_expression(a, b) SELECT g.i % 42, 'prefix'||g.i FROM generate_series(1, 133) g(i);
472+
CREATE INDEX clstr_expression_minus_a ON clstr_expression ((-a), b);
473+
CREATE INDEX clstr_expression_upper_b ON clstr_expression ((upper(b)));
474+
-- verify indexes work before cluster
475+
BEGIN;
476+
SET LOCAL enable_seqscan = false;
477+
EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
478+
QUERY PLAN
479+
---------------------------------------------------------------
480+
Index Scan using clstr_expression_upper_b on clstr_expression
481+
Index Cond: (upper(b) = 'PREFIX3'::text)
482+
(2 rows)
483+
484+
SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
485+
id | a | b
486+
----+---+---------
487+
3 | 3 | prefix3
488+
(1 row)
489+
490+
EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
491+
QUERY PLAN
492+
---------------------------------------------------------------
493+
Index Scan using clstr_expression_minus_a on clstr_expression
494+
Index Cond: ((- a) = '-3'::integer)
495+
(2 rows)
496+
497+
SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
498+
id | a | b
499+
-----+---+-----------
500+
129 | 3 | prefix129
501+
3 | 3 | prefix3
502+
45 | 3 | prefix45
503+
87 | 3 | prefix87
504+
(4 rows)
505+
506+
COMMIT;
507+
-- and after clustering on clstr_expression_minus_a
508+
CLUSTER clstr_expression USING clstr_expression_minus_a;
509+
BEGIN;
510+
SET LOCAL enable_seqscan = false;
511+
EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
512+
QUERY PLAN
513+
---------------------------------------------------------------
514+
Index Scan using clstr_expression_upper_b on clstr_expression
515+
Index Cond: (upper(b) = 'PREFIX3'::text)
516+
(2 rows)
517+
518+
SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
519+
id | a | b
520+
----+---+---------
521+
3 | 3 | prefix3
522+
(1 row)
523+
524+
EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
525+
QUERY PLAN
526+
---------------------------------------------------------------
527+
Index Scan using clstr_expression_minus_a on clstr_expression
528+
Index Cond: ((- a) = '-3'::integer)
529+
(2 rows)
530+
531+
SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
532+
id | a | b
533+
-----+---+-----------
534+
129 | 3 | prefix129
535+
3 | 3 | prefix3
536+
45 | 3 | prefix45
537+
87 | 3 | prefix87
538+
(4 rows)
539+
540+
COMMIT;
541+
-- and after clustering on clstr_expression_upper_b
542+
CLUSTER clstr_expression USING clstr_expression_upper_b;
543+
BEGIN;
544+
SET LOCAL enable_seqscan = false;
545+
EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
546+
QUERY PLAN
547+
---------------------------------------------------------------
548+
Index Scan using clstr_expression_upper_b on clstr_expression
549+
Index Cond: (upper(b) = 'PREFIX3'::text)
550+
(2 rows)
551+
552+
SELECT * FROM clstr_expression WHERE upper(b) = 'PREFIX3';
553+
id | a | b
554+
----+---+---------
555+
3 | 3 | prefix3
556+
(1 row)
557+
558+
EXPLAIN (COSTS OFF) SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
559+
QUERY PLAN
560+
---------------------------------------------------------------
561+
Index Scan using clstr_expression_minus_a on clstr_expression
562+
Index Cond: ((- a) = '-3'::integer)
563+
(2 rows)
564+
565+
SELECT * FROM clstr_expression WHERE -a = -3 ORDER BY -a, b;
566+
id | a | b
567+
-----+---+-----------
568+
129 | 3 | prefix129
569+
3 | 3 | prefix3
570+
45 | 3 | prefix45
571+
87 | 3 | prefix87
572+
(4 rows)
573+
574+
COMMIT;
469575
-- clean up
470576
DROP TABLE clustertest;
471577
DROP TABLE clstr_1;
472578
DROP TABLE clstr_2;
473579
DROP TABLE clstr_3;
474580
DROP TABLE clstr_4;
581+
DROP TABLE clstr_expression;
475582
DROP USER regress_clstr_user;

‎src/test/regress/sql/cluster.sql

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -222,10 +222,47 @@ where row(hundred, thousand, tenthous) <= row(lhundred, lthousand, ltenthous);
222222
reset enable_indexscan;
223223
reset maintenance_work_mem;
224224

225+
-- test CLUSTER on expression index
226+
CREATETABLEclstr_expression(idserialprimary key, aint, btext COLLATE"C");
227+
INSERT INTO clstr_expression(a, b)SELECTg.i %42,'prefix'||g.iFROM generate_series(1,133) g(i);
228+
CREATEINDEXclstr_expression_minus_aON clstr_expression ((-a), b);
229+
CREATEINDEXclstr_expression_upper_bON clstr_expression ((upper(b)));
230+
231+
-- verify indexes work before cluster
232+
BEGIN;
233+
SET LOCAL enable_seqscan= false;
234+
EXPLAIN (COSTS OFF)SELECT*FROM clstr_expressionWHEREupper(b)='PREFIX3';
235+
SELECT*FROM clstr_expressionWHEREupper(b)='PREFIX3';
236+
EXPLAIN (COSTS OFF)SELECT*FROM clstr_expressionWHERE-a=-3ORDER BY-a, b;
237+
SELECT*FROM clstr_expressionWHERE-a=-3ORDER BY-a, b;
238+
COMMIT;
239+
240+
-- and after clustering on clstr_expression_minus_a
241+
CLUSTER clstr_expression USING clstr_expression_minus_a;
242+
BEGIN;
243+
SET LOCAL enable_seqscan= false;
244+
EXPLAIN (COSTS OFF)SELECT*FROM clstr_expressionWHEREupper(b)='PREFIX3';
245+
SELECT*FROM clstr_expressionWHEREupper(b)='PREFIX3';
246+
EXPLAIN (COSTS OFF)SELECT*FROM clstr_expressionWHERE-a=-3ORDER BY-a, b;
247+
SELECT*FROM clstr_expressionWHERE-a=-3ORDER BY-a, b;
248+
COMMIT;
249+
250+
-- and after clustering on clstr_expression_upper_b
251+
CLUSTER clstr_expression USING clstr_expression_upper_b;
252+
BEGIN;
253+
SET LOCAL enable_seqscan= false;
254+
EXPLAIN (COSTS OFF)SELECT*FROM clstr_expressionWHEREupper(b)='PREFIX3';
255+
SELECT*FROM clstr_expressionWHEREupper(b)='PREFIX3';
256+
EXPLAIN (COSTS OFF)SELECT*FROM clstr_expressionWHERE-a=-3ORDER BY-a, b;
257+
SELECT*FROM clstr_expressionWHERE-a=-3ORDER BY-a, b;
258+
COMMIT;
259+
225260
-- clean up
226261
DROPTABLE clustertest;
227262
DROPTABLE clstr_1;
228263
DROPTABLE clstr_2;
229264
DROPTABLE clstr_3;
230265
DROPTABLE clstr_4;
266+
DROPTABLE clstr_expression;
267+
231268
DROPUSER regress_clstr_user;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp