Support index-only scans in contrib/cube and contrib/seg GiST indexes.

To do this, we only have to remove the compress and decompress support
functions, which have never done anything more than detoasting.
In the wake of commit d3a4f89d8, this results in automatically enabling
index-only scans, since the core code will now know that the stored
representation is the same as the original data (up to detoasting).

The only exciting part of this is that ALTER OPERATOR FAMILY lacks
a way to drop a support function that was declared as being part of
an 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_depend
entry type, which is what distinguishes the two cases.  Perhaps
someday it'll be worth providing a cleaner way to do that, but for
now it seems like a very niche problem.

Note that the underlying C functions remain, to support use of the shared
libraries with older versions of the modules' SQL declarations.  Someday
we may be able to remove them, but not soon.

Andrey Borodin, reviewed by me

Discussion: https://postgr.es/m/D0F53A05-4F4A-4DEC-8339-3C069FA0EE11@yandex-team.ru

6 files changed, 98 insertions, 2 deletions

diff --git a/contrib/cube/Makefile b/contrib/cube/Makefile
index 244c1d9bbf3..accb7d28a39 100644
--- a/contrib/cube/Makefile
+++ b/contrib/cube/Makefile
@@ -4,7 +4,7 @@ MODULE_big = cube
 OBJS= cube.o cubeparse.o $(WIN32RES)
 
 EXTENSION = cube
-DATA = cube--1.2.sql cube--1.2--1.3.sql \
+DATA = cube--1.2.sql cube--1.2--1.3.sql cube--1.3--1.4.sql \
 	cube--1.1--1.2.sql cube--1.0--1.1.sql \
 	cube--unpackaged--1.0.sql
 PGFILEDESC = "cube - multidimensional cube data type"
diff --git a/contrib/cube/cube--1.3--1.4.sql b/contrib/cube/cube--1.3--1.4.sql
new file mode 100644
index 00000000000..869820c0c83
--- /dev/null
+++ b/contrib/cube/cube--1.3--1.4.sql
@@ -0,0 +1,45 @@
+/* contrib/cube/cube--1.3--1.4.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION cube UPDATE TO '1.4'" to load this file. \quit
+
+--
+-- Get rid of unnecessary compress and decompress support functions.
+--
+-- To be allowed to drop the opclass entry for a support function,
+-- we must change the entry's dependency type from 'internal' to 'auto',
+-- as though it were a loose member of the opfamily rather than being
+-- bound into a particular opclass.  There's no SQL command for that,
+-- so fake it with a manual update on pg_depend.
+--
+UPDATE pg_catalog.pg_depend
+SET deptype = 'a'
+WHERE classid = 'pg_catalog.pg_amproc'::pg_catalog.regclass
+  AND objid =
+    (SELECT objid
+     FROM pg_catalog.pg_depend
+     WHERE classid = 'pg_catalog.pg_amproc'::pg_catalog.regclass
+       AND refclassid = 'pg_catalog.pg_proc'::pg_catalog.regclass
+       AND (refobjid = 'g_cube_compress(pg_catalog.internal)'::pg_catalog.regprocedure))
+  AND refclassid = 'pg_catalog.pg_opclass'::pg_catalog.regclass
+  AND deptype = 'i';
+
+ALTER OPERATOR FAMILY gist_cube_ops USING gist drop function 3 (cube);
+ALTER EXTENSION cube DROP function g_cube_compress(pg_catalog.internal);
+DROP FUNCTION g_cube_compress(pg_catalog.internal);
+
+UPDATE pg_catalog.pg_depend
+SET deptype = 'a'
+WHERE classid = 'pg_catalog.pg_amproc'::pg_catalog.regclass
+  AND objid =
+    (SELECT objid
+     FROM pg_catalog.pg_depend
+     WHERE classid = 'pg_catalog.pg_amproc'::pg_catalog.regclass
+       AND refclassid = 'pg_catalog.pg_proc'::pg_catalog.regclass
+       AND (refobjid = 'g_cube_decompress(pg_catalog.internal)'::pg_catalog.regprocedure))
+  AND refclassid = 'pg_catalog.pg_opclass'::pg_catalog.regclass
+  AND deptype = 'i';
+
+ALTER OPERATOR FAMILY gist_cube_ops USING gist drop function 4 (cube);
+ALTER EXTENSION cube DROP function g_cube_decompress(pg_catalog.internal);
+DROP FUNCTION g_cube_decompress(pg_catalog.internal);
diff --git a/contrib/cube/cube.control b/contrib/cube/cube.control
index af062d48436..f39a838e3f1 100644
--- a/contrib/cube/cube.control
+++ b/contrib/cube/cube.control
@@ -1,5 +1,5 @@
 # cube extension
 comment = 'data type for multidimensional cubes'
-default_version = '1.3'
+default_version = '1.4'
 module_pathname = '$libdir/cube'
 relocatable = true
diff --git a/contrib/cube/expected/cube.out b/contrib/cube/expected/cube.out
index 328b3b5f5de..c430b4e1f0c 100644
--- a/contrib/cube/expected/cube.out
+++ b/contrib/cube/expected/cube.out
@@ -1589,6 +1589,28 @@ SELECT * FROM test_cube WHERE c && '(3000,1000),(0,0)' GROUP BY c ORDER BY c;
  (2424, 160),(2424, 81)
 (5 rows)
 
+-- Test index-only scans
+SET enable_bitmapscan = false;
+EXPLAIN (COSTS OFF)
+SELECT c FROM test_cube WHERE c <@ '(3000,1000),(0,0)' ORDER BY c;
+                       QUERY PLAN                       
+--------------------------------------------------------
+ Sort
+   Sort Key: c
+   ->  Index Only Scan using test_cube_ix on test_cube
+         Index Cond: (c <@ '(3000, 1000),(0, 0)'::cube)
+(4 rows)
+
+SELECT c FROM test_cube WHERE c <@ '(3000,1000),(0,0)' ORDER BY c;
+            c            
+-------------------------
+ (337, 455),(240, 359)
+ (759, 187),(662, 163)
+ (1444, 403),(1346, 344)
+ (2424, 160),(2424, 81)
+(4 rows)
+
+RESET enable_bitmapscan;
 -- kNN with index
 SELECT *, c <-> '(100, 100),(500, 500)'::cube as dist FROM test_cube ORDER BY c <-> '(100, 100),(500, 500)'::cube LIMIT 5;
             c            |       dist       
diff --git a/contrib/cube/expected/cube_2.out b/contrib/cube/expected/cube_2.out
index 1aa5cf2f983..b979c4d6c88 100644
--- a/contrib/cube/expected/cube_2.out
+++ b/contrib/cube/expected/cube_2.out
@@ -1589,6 +1589,28 @@ SELECT * FROM test_cube WHERE c && '(3000,1000),(0,0)' GROUP BY c ORDER BY c;
  (2424, 160),(2424, 81)
 (5 rows)
 
+-- Test index-only scans
+SET enable_bitmapscan = false;
+EXPLAIN (COSTS OFF)
+SELECT c FROM test_cube WHERE c <@ '(3000,1000),(0,0)' ORDER BY c;
+                       QUERY PLAN                       
+--------------------------------------------------------
+ Sort
+   Sort Key: c
+   ->  Index Only Scan using test_cube_ix on test_cube
+         Index Cond: (c <@ '(3000, 1000),(0, 0)'::cube)
+(4 rows)
+
+SELECT c FROM test_cube WHERE c <@ '(3000,1000),(0,0)' ORDER BY c;
+            c            
+-------------------------
+ (337, 455),(240, 359)
+ (759, 187),(662, 163)
+ (1444, 403),(1346, 344)
+ (2424, 160),(2424, 81)
+(4 rows)
+
+RESET enable_bitmapscan;
 -- kNN with index
 SELECT *, c <-> '(100, 100),(500, 500)'::cube as dist FROM test_cube ORDER BY c <-> '(100, 100),(500, 500)'::cube LIMIT 5;
             c            |       dist       
diff --git a/contrib/cube/sql/cube.sql b/contrib/cube/sql/cube.sql
index 58ea3ad8113..eb245768959 100644
--- a/contrib/cube/sql/cube.sql
+++ b/contrib/cube/sql/cube.sql
@@ -382,6 +382,13 @@ SELECT * FROM test_cube WHERE c && '(3000,1000),(0,0)' ORDER BY c;
 -- Test sorting
 SELECT * FROM test_cube WHERE c && '(3000,1000),(0,0)' GROUP BY c ORDER BY c;
 
+-- Test index-only scans
+SET enable_bitmapscan = false;
+EXPLAIN (COSTS OFF)
+SELECT c FROM test_cube WHERE c <@ '(3000,1000),(0,0)' ORDER BY c;
+SELECT c FROM test_cube WHERE c <@ '(3000,1000),(0,0)' ORDER BY c;
+RESET enable_bitmapscan;
+
 -- kNN with index
 SELECT *, c <-> '(100, 100),(500, 500)'::cube as dist FROM test_cube ORDER BY c <-> '(100, 100),(500, 500)'::cube LIMIT 5;
 SELECT *, c <=> '(100, 100),(500, 500)'::cube as dist FROM test_cube ORDER BY c <=> '(100, 100),(500, 500)'::cube LIMIT 5;