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

Commitde1d042

Browse files
committed
Support index-only scans in contrib/cube and contrib/seg GiST indexes.
To do this, we only have to remove the compress and decompress supportfunctions, which have never done anything more than detoasting.In the wake of commitd3a4f89, this results in automatically enablingindex-only scans, since the core code will now know that the storedrepresentation is the same as the original data (up to detoasting).The only exciting part of this is that ALTER OPERATOR FAMILY lacksa way to drop a support function that was declared as being part ofan opclass rather than being loose in the family. For the moment,we'll hack our way to a solution with a manual update of the pg_dependentry type, which is what distinguishes the two cases. Perhapssomeday it'll be worth providing a cleaner way to do that, but fornow it seems like a very niche problem.Note that the underlying C functions remain, to support use of the sharedlibraries with older versions of the modules' SQL declarations. Somedaywe may be able to remove them, but not soon.Andrey Borodin, reviewed by meDiscussion:https://postgr.es/m/D0F53A05-4F4A-4DEC-8339-3C069FA0EE11@yandex-team.ru
1 parent84669c9 commitde1d042

File tree

12 files changed

+210
-4
lines changed

12 files changed

+210
-4
lines changed

‎contrib/cube/Makefile

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -4,7 +4,7 @@ MODULE_big = cube
44
OBJS= cube.o cubeparse.o$(WIN32RES)
55

66
EXTENSION = cube
7-
DATA = cube--1.2.sql cube--1.2--1.3.sql\
7+
DATA = cube--1.2.sql cube--1.2--1.3.sqlcube--1.3--1.4.sql\
88
cube--1.1--1.2.sql cube--1.0--1.1.sql\
99
cube--unpackaged--1.0.sql
1010
PGFILEDESC = "cube - multidimensional cube data type"

‎contrib/cube/cube--1.3--1.4.sql

Lines changed: 45 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,45 @@
1+
/* contrib/cube/cube--1.3--1.4.sql*/
2+
3+
-- complain if script is sourced in psql, rather than via ALTER EXTENSION
4+
\echo Use"ALTER EXTENSION cube UPDATE TO '1.4'" to load this file. \quit
5+
6+
--
7+
-- Get rid of unnecessary compress and decompress support functions.
8+
--
9+
-- To be allowed to drop the opclass entry for a support function,
10+
-- we must change the entry's dependency type from 'internal' to 'auto',
11+
-- as though it were a loose member of the opfamily rather than being
12+
-- bound into a particular opclass. There's no SQL command for that,
13+
-- so fake it with a manual update on pg_depend.
14+
--
15+
UPDATEpg_catalog.pg_depend
16+
SET deptype='a'
17+
WHERE classid='pg_catalog.pg_amproc'::pg_catalog.regclass
18+
AND objid=
19+
(SELECT objid
20+
FROMpg_catalog.pg_depend
21+
WHERE classid='pg_catalog.pg_amproc'::pg_catalog.regclass
22+
AND refclassid='pg_catalog.pg_proc'::pg_catalog.regclass
23+
AND (refobjid='g_cube_compress(pg_catalog.internal)'::pg_catalog.regprocedure))
24+
AND refclassid='pg_catalog.pg_opclass'::pg_catalog.regclass
25+
AND deptype='i';
26+
27+
ALTEROPERATOR FAMILY gist_cube_ops USING gist drop function3 (cube);
28+
ALTER EXTENSION cube DROP function g_cube_compress(pg_catalog.internal);
29+
DROPFUNCTION g_cube_compress(pg_catalog.internal);
30+
31+
UPDATEpg_catalog.pg_depend
32+
SET deptype='a'
33+
WHERE classid='pg_catalog.pg_amproc'::pg_catalog.regclass
34+
AND objid=
35+
(SELECT objid
36+
FROMpg_catalog.pg_depend
37+
WHERE classid='pg_catalog.pg_amproc'::pg_catalog.regclass
38+
AND refclassid='pg_catalog.pg_proc'::pg_catalog.regclass
39+
AND (refobjid='g_cube_decompress(pg_catalog.internal)'::pg_catalog.regprocedure))
40+
AND refclassid='pg_catalog.pg_opclass'::pg_catalog.regclass
41+
AND deptype='i';
42+
43+
ALTEROPERATOR FAMILY gist_cube_ops USING gist drop function4 (cube);
44+
ALTER EXTENSION cube DROP function g_cube_decompress(pg_catalog.internal);
45+
DROPFUNCTION g_cube_decompress(pg_catalog.internal);

‎contrib/cube/cube.control

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
# cube extension
22
comment = 'data type for multidimensional cubes'
3-
default_version = '1.3'
3+
default_version = '1.4'
44
module_pathname = '$libdir/cube'
55
relocatable = true

‎contrib/cube/expected/cube.out

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1589,6 +1589,28 @@ SELECT * FROM test_cube WHERE c && '(3000,1000),(0,0)' GROUP BY c ORDER BY c;
15891589
(2424, 160),(2424, 81)
15901590
(5 rows)
15911591

1592+
-- Test index-only scans
1593+
SET enable_bitmapscan = false;
1594+
EXPLAIN (COSTS OFF)
1595+
SELECT c FROM test_cube WHERE c <@ '(3000,1000),(0,0)' ORDER BY c;
1596+
QUERY PLAN
1597+
--------------------------------------------------------
1598+
Sort
1599+
Sort Key: c
1600+
-> Index Only Scan using test_cube_ix on test_cube
1601+
Index Cond: (c <@ '(3000, 1000),(0, 0)'::cube)
1602+
(4 rows)
1603+
1604+
SELECT c FROM test_cube WHERE c <@ '(3000,1000),(0,0)' ORDER BY c;
1605+
c
1606+
-------------------------
1607+
(337, 455),(240, 359)
1608+
(759, 187),(662, 163)
1609+
(1444, 403),(1346, 344)
1610+
(2424, 160),(2424, 81)
1611+
(4 rows)
1612+
1613+
RESET enable_bitmapscan;
15921614
-- kNN with index
15931615
SELECT *, c <-> '(100, 100),(500, 500)'::cube as dist FROM test_cube ORDER BY c <-> '(100, 100),(500, 500)'::cube LIMIT 5;
15941616
c | dist

‎contrib/cube/expected/cube_2.out

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1589,6 +1589,28 @@ SELECT * FROM test_cube WHERE c && '(3000,1000),(0,0)' GROUP BY c ORDER BY c;
15891589
(2424, 160),(2424, 81)
15901590
(5 rows)
15911591

1592+
-- Test index-only scans
1593+
SET enable_bitmapscan = false;
1594+
EXPLAIN (COSTS OFF)
1595+
SELECT c FROM test_cube WHERE c <@ '(3000,1000),(0,0)' ORDER BY c;
1596+
QUERY PLAN
1597+
--------------------------------------------------------
1598+
Sort
1599+
Sort Key: c
1600+
-> Index Only Scan using test_cube_ix on test_cube
1601+
Index Cond: (c <@ '(3000, 1000),(0, 0)'::cube)
1602+
(4 rows)
1603+
1604+
SELECT c FROM test_cube WHERE c <@ '(3000,1000),(0,0)' ORDER BY c;
1605+
c
1606+
-------------------------
1607+
(337, 455),(240, 359)
1608+
(759, 187),(662, 163)
1609+
(1444, 403),(1346, 344)
1610+
(2424, 160),(2424, 81)
1611+
(4 rows)
1612+
1613+
RESET enable_bitmapscan;
15921614
-- kNN with index
15931615
SELECT *, c <-> '(100, 100),(500, 500)'::cube as dist FROM test_cube ORDER BY c <-> '(100, 100),(500, 500)'::cube LIMIT 5;
15941616
c | dist

‎contrib/cube/sql/cube.sql

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -382,6 +382,13 @@ SELECT * FROM test_cube WHERE c && '(3000,1000),(0,0)' ORDER BY c;
382382
-- Test sorting
383383
SELECT*FROM test_cubeWHERE c &&'(3000,1000),(0,0)'GROUP BY cORDER BY c;
384384

385+
-- Test index-only scans
386+
SET enable_bitmapscan= false;
387+
EXPLAIN (COSTS OFF)
388+
SELECT cFROM test_cubeWHERE c<@'(3000,1000),(0,0)'ORDER BY c;
389+
SELECT cFROM test_cubeWHERE c<@'(3000,1000),(0,0)'ORDER BY c;
390+
RESET enable_bitmapscan;
391+
385392
-- kNN with index
386393
SELECT*, c<->'(100, 100),(500, 500)'::cubeas distFROM test_cubeORDER BY c<->'(100, 100),(500, 500)'::cubeLIMIT5;
387394
SELECT*, c<=>'(100, 100),(500, 500)'::cubeas distFROM test_cubeORDER BY c<=>'(100, 100),(500, 500)'::cubeLIMIT5;

‎contrib/seg/Makefile

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -4,7 +4,7 @@ MODULE_big = seg
44
OBJS = seg.o segparse.o$(WIN32RES)
55

66
EXTENSION = seg
7-
DATA = seg--1.1.sql seg--1.1--1.2.sql\
7+
DATA = seg--1.1.sql seg--1.1--1.2.sqlseg--1.2--1.3.sql\
88
seg--1.0--1.1.sql seg--unpackaged--1.0.sql
99
PGFILEDESC = "seg - line segment data type"
1010

‎contrib/seg/expected/seg.out

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -930,12 +930,40 @@ SELECT '1'::seg <@ '-1 .. 1'::seg AS bool;
930930
CREATE TABLE test_seg (s seg);
931931
\copy test_seg from 'data/test_seg.data'
932932
CREATE INDEX test_seg_ix ON test_seg USING gist (s);
933+
EXPLAIN (COSTS OFF)
934+
SELECT count(*) FROM test_seg WHERE s @> '11..11.3';
935+
QUERY PLAN
936+
-------------------------------------------------------
937+
Aggregate
938+
-> Bitmap Heap Scan on test_seg
939+
Recheck Cond: (s @> '1.1e1 .. 11.3'::seg)
940+
-> Bitmap Index Scan on test_seg_ix
941+
Index Cond: (s @> '1.1e1 .. 11.3'::seg)
942+
(5 rows)
943+
944+
SELECT count(*) FROM test_seg WHERE s @> '11..11.3';
945+
count
946+
-------
947+
143
948+
(1 row)
949+
950+
SET enable_bitmapscan = false;
951+
EXPLAIN (COSTS OFF)
952+
SELECT count(*) FROM test_seg WHERE s @> '11..11.3';
953+
QUERY PLAN
954+
-----------------------------------------------------
955+
Aggregate
956+
-> Index Only Scan using test_seg_ix on test_seg
957+
Index Cond: (s @> '1.1e1 .. 11.3'::seg)
958+
(3 rows)
959+
933960
SELECT count(*) FROM test_seg WHERE s @> '11..11.3';
934961
count
935962
-------
936963
143
937964
(1 row)
938965

966+
RESET enable_bitmapscan;
939967
-- Test sorting
940968
SELECT * FROM test_seg WHERE s @> '11..11.3' GROUP BY s;
941969
s

‎contrib/seg/expected/seg_1.out

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -930,12 +930,40 @@ SELECT '1'::seg <@ '-1 .. 1'::seg AS bool;
930930
CREATE TABLE test_seg (s seg);
931931
\copy test_seg from 'data/test_seg.data'
932932
CREATE INDEX test_seg_ix ON test_seg USING gist (s);
933+
EXPLAIN (COSTS OFF)
934+
SELECT count(*) FROM test_seg WHERE s @> '11..11.3';
935+
QUERY PLAN
936+
-------------------------------------------------------
937+
Aggregate
938+
-> Bitmap Heap Scan on test_seg
939+
Recheck Cond: (s @> '1.1e1 .. 11.3'::seg)
940+
-> Bitmap Index Scan on test_seg_ix
941+
Index Cond: (s @> '1.1e1 .. 11.3'::seg)
942+
(5 rows)
943+
944+
SELECT count(*) FROM test_seg WHERE s @> '11..11.3';
945+
count
946+
-------
947+
143
948+
(1 row)
949+
950+
SET enable_bitmapscan = false;
951+
EXPLAIN (COSTS OFF)
952+
SELECT count(*) FROM test_seg WHERE s @> '11..11.3';
953+
QUERY PLAN
954+
-----------------------------------------------------
955+
Aggregate
956+
-> Index Only Scan using test_seg_ix on test_seg
957+
Index Cond: (s @> '1.1e1 .. 11.3'::seg)
958+
(3 rows)
959+
933960
SELECT count(*) FROM test_seg WHERE s @> '11..11.3';
934961
count
935962
-------
936963
143
937964
(1 row)
938965

966+
RESET enable_bitmapscan;
939967
-- Test sorting
940968
SELECT * FROM test_seg WHERE s @> '11..11.3' GROUP BY s;
941969
s

‎contrib/seg/seg--1.2--1.3.sql

Lines changed: 45 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,45 @@
1+
/* contrib/seg/seg--1.2--1.3.sql*/
2+
3+
-- complain if script is sourced in psql, rather than via ALTER EXTENSION
4+
\echo Use"ALTER EXTENSION seg UPDATE TO '1.3'" to load this file. \quit
5+
6+
--
7+
-- Get rid of unnecessary compress and decompress support functions.
8+
--
9+
-- To be allowed to drop the opclass entry for a support function,
10+
-- we must change the entry's dependency type from 'internal' to 'auto',
11+
-- as though it were a loose member of the opfamily rather than being
12+
-- bound into a particular opclass. There's no SQL command for that,
13+
-- so fake it with a manual update on pg_depend.
14+
--
15+
UPDATEpg_catalog.pg_depend
16+
SET deptype='a'
17+
WHERE classid='pg_catalog.pg_amproc'::pg_catalog.regclass
18+
AND objid=
19+
(SELECT objid
20+
FROMpg_catalog.pg_depend
21+
WHERE classid='pg_catalog.pg_amproc'::pg_catalog.regclass
22+
AND refclassid='pg_catalog.pg_proc'::pg_catalog.regclass
23+
AND (refobjid='gseg_compress(pg_catalog.internal)'::pg_catalog.regprocedure))
24+
AND refclassid='pg_catalog.pg_opclass'::pg_catalog.regclass
25+
AND deptype='i';
26+
27+
ALTEROPERATOR FAMILY gist_seg_ops USING gist drop function3 (seg);
28+
ALTER EXTENSION seg DROP function gseg_compress(pg_catalog.internal);
29+
DROPfunction gseg_compress(pg_catalog.internal);
30+
31+
UPDATEpg_catalog.pg_depend
32+
SET deptype='a'
33+
WHERE classid='pg_catalog.pg_amproc'::pg_catalog.regclass
34+
AND objid=
35+
(SELECT objid
36+
FROMpg_catalog.pg_depend
37+
WHERE classid='pg_catalog.pg_amproc'::pg_catalog.regclass
38+
AND refclassid='pg_catalog.pg_proc'::pg_catalog.regclass
39+
AND (refobjid='gseg_decompress(pg_catalog.internal)'::pg_catalog.regprocedure))
40+
AND refclassid='pg_catalog.pg_opclass'::pg_catalog.regclass
41+
AND deptype='i';
42+
43+
ALTEROPERATOR FAMILY gist_seg_ops USING gist drop function4 (seg);
44+
ALTER EXTENSION seg DROP function gseg_decompress(pg_catalog.internal);
45+
DROPfunction gseg_decompress(pg_catalog.internal);

‎contrib/seg/seg.control

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
# seg extension
22
comment = 'data type for representing line segments or floating-point intervals'
3-
default_version = '1.2'
3+
default_version = '1.3'
44
module_pathname = '$libdir/seg'
55
relocatable = true

‎contrib/seg/sql/seg.sql

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -216,7 +216,16 @@ CREATE TABLE test_seg (s seg);
216216
\copy test_segfrom'data/test_seg.data'
217217

218218
CREATEINDEXtest_seg_ixON test_seg USING gist (s);
219+
220+
EXPLAIN (COSTS OFF)
221+
SELECTcount(*)FROM test_segWHERE s @>'11..11.3';
222+
SELECTcount(*)FROM test_segWHERE s @>'11..11.3';
223+
224+
SET enable_bitmapscan= false;
225+
EXPLAIN (COSTS OFF)
226+
SELECTcount(*)FROM test_segWHERE s @>'11..11.3';
219227
SELECTcount(*)FROM test_segWHERE s @>'11..11.3';
228+
RESET enable_bitmapscan;
220229

221230
-- Test sorting
222231
SELECT*FROM test_segWHERE s @>'11..11.3'GROUP BY s;

0 commit comments

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