Index Only Scan support for cube

Started by Andrew Borodinover 8 years ago3 messages

borodin@octonica.com

over 8 years ago

1 attachment(s)

Hi, hackers!

Here's a small patch that implements fetch function necessary for
Index Only Scans that use cube data type.
I reuse function g_cube_decompress() instead of creating new function
g_cube_fetch().
Essentially, they both have to detoast data.

How do you think, is it better to create a shallow copy of
g_cube_decompress instead?
Any other suggestions on the functionality?

This

CREATE TABLE SOMECUBES AS SELECT CUBE(X,X+1) C FROM GENERATE_SERIES(1,100) X;
CREATE INDEX SOMECUBES_IDX ON SOMECUBES USING GIST(C);
SET ENABLE_SEQSCAN = FALSE;
EXPLAIN (COSTS OFF ) SELECT C FROM SOMECUBES WHERE C<@CUBE(30,40);

now produces

Index Only Scan using somecubes_idx on somecubes
Index Cond: (c <@ '(30),(40)'::cube)

instead of

Index Scan using somecubes_idx on somecubes
Index Cond: (c <@ '(30),(40)'::cube)

Best regards, Andrey Borodin, Octonica.

Attachments:

cubefetch.patchapplication/octet-stream; name=cubefetch.patchDownload

From 4f56ac8a8c67a8d497592f6b89e22f959b6aa4e3 Mon Sep 17 00:00:00 2001
From: Andrey Borodin <amborodin@acm.org>
Date: Tue, 23 May 2017 14:38:26 +0500
Subject: [PATCH] Index Only Scan support for cube

---
 contrib/cube/Makefile           |  2 +-
 contrib/cube/cube--1.2--1.3.sql |  8 ++++++++
 contrib/cube/cube.control       |  2 +-
 contrib/cube/expected/cube.out  | 19 +++++++++++++++++++
 contrib/cube/sql/cube.sql       |  6 ++++++
 5 files changed, 35 insertions(+), 2 deletions(-)
 create mode 100644 contrib/cube/cube--1.2--1.3.sql

diff --git a/contrib/cube/Makefile b/contrib/cube/Makefile
index be7a1bc1a00..175da7791fb 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.1--1.2.sql cube--1.0--1.1.sql \
+DATA =  cube--1.2--1.3.sql cube--1.2.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.2--1.3.sql b/contrib/cube/cube--1.2--1.3.sql
new file mode 100644
index 00000000000..a122d5039fd
--- /dev/null
+++ b/contrib/cube/cube--1.2--1.3.sql
@@ -0,0 +1,8 @@
+/* contrib/cube/cube--1.2--1.3.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "ALTER EXTENSION btree_gist UPDATE TO '1.5'" to load this file. \quit
+
+
+ALTER OPERATOR FAMILY gist_cube_ops USING gist ADD
+	FUNCTION	9 (cube, cube) g_cube_decompress (internal) ;
diff --git a/contrib/cube/cube.control b/contrib/cube/cube.control
index b03cfa0a580..af062d48436 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.2'
+default_version = '1.3'
 module_pathname = '$libdir/cube'
 relocatable = true
diff --git a/contrib/cube/expected/cube.out b/contrib/cube/expected/cube.out
index 328b3b5f5de..52d65c0339a 100644
--- a/contrib/cube/expected/cube.out
+++ b/contrib/cube/expected/cube.out
@@ -1701,6 +1701,25 @@ SELECT * FROM test_cube ORDER BY c~>4 DESC LIMIT 15; -- descending by 2nd coordi
  (46151, 49848),(46058, 49830)
 (15 rows)
 
+-- Test Index Only Scans
+SET ENABLE_BITMAPSCAN = FALSE;
+EXPLAIN (COSTS OFF) SELECT c FROM test_cube WHERE c <@ '(3000,1000),(0,0)';
+                    QUERY PLAN                    
+--------------------------------------------------
+ Index Only Scan using test_cube_ix on test_cube
+   Index Cond: (c <@ '(3000, 1000),(0, 0)'::cube)
+(2 rows)
+
+SELECT c FROM test_cube WHERE c <@ '(3000,1000),(0,0)';
+            c            
+-------------------------
+ (2424, 160),(2424, 81)
+ (759, 187),(662, 163)
+ (1444, 403),(1346, 344)
+ (337, 455),(240, 359)
+(4 rows)
+
+SET ENABLE_BITMAPSCAN = TRUE;
 -- same thing for index with points
 CREATE TABLE test_point(c cube);
 INSERT INTO test_point(SELECT cube(array[c->1,c->2,c->3,c->4]) FROM test_cube);
diff --git a/contrib/cube/sql/cube.sql b/contrib/cube/sql/cube.sql
index 58ea3ad8113..277a81a03e7 100644
--- a/contrib/cube/sql/cube.sql
+++ b/contrib/cube/sql/cube.sql
@@ -393,6 +393,12 @@ SELECT * FROM test_cube ORDER BY c~>4 LIMIT 15; -- ascending by 2nd coordinate o
 SELECT * FROM test_cube ORDER BY c~>1 DESC LIMIT 15; -- descending by 1st coordinate of lower left corner
 SELECT * FROM test_cube ORDER BY c~>4 DESC LIMIT 15; -- descending by 2nd coordinate or upper right corner
 
+-- Test Index Only Scans
+SET ENABLE_BITMAPSCAN = FALSE;
+EXPLAIN (COSTS OFF) SELECT c FROM test_cube WHERE c <@ '(3000,1000),(0,0)';
+SELECT c FROM test_cube WHERE c <@ '(3000,1000),(0,0)';
+SET ENABLE_BITMAPSCAN = TRUE;
+
 -- same thing for index with points
 CREATE TABLE test_point(c cube);
 INSERT INTO test_point(SELECT cube(array[c->1,c->2,c->3,c->4]) FROM test_cube);

Andrey Borodin

x4mmm@yandex-team.ru

over 8 years ago

In reply to: Andrew Borodin (#1)

Re: Index Only Scan support for cube

Hi hackers!

23 мая 2017 г., в 14:53, Andrew Borodin <borodin@octonica.com> написал(а):

Here's a small patch that implements fetch function necessary for
Index Only Scans that use cube data type.

Tom Lane have just commited d3a4f89 (Allow no-op GiST support functions to be omitted) Thanks, Tom! : )
"Index Only Scan support for cube" patch now is obsolete. I'm working on another similar patch for contribs to support GiST IOS and remove no-op support functions.

Best regards, Andrey Borodin.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Alexander Korotkov

a.korotkov@postgrespro.ru

over 8 years ago

In reply to: Andrey Borodin (#2)

Re: Index Only Scan support for cube

On Wed, Sep 20, 2017 at 8:26 AM, Andrey Borodin <x4mmm@yandex-team.ru>
wrote:

Hi hackers!

23 мая 2017 г., в 14:53, Andrew Borodin <borodin@octonica.com>

написал(а):

Here's a small patch that implements fetch function necessary for
Index Only Scans that use cube data type.

Tom Lane have just commited d3a4f89 (Allow no-op GiST support functions to
be omitted) Thanks, Tom! : )
"Index Only Scan support for cube" patch now is obsolete. I'm working on
another similar patch for contribs to support GiST IOS and remove no-op
support functions.

Good.
BTW, some strangeness of g_cube_decompress() catch my eye. It compares
results of two evaluations of same expression DatumGetNDBOXP(entry->key).

NDBOX *key = DatumGetNDBOXP(entry->key);

if (key != DatumGetNDBOXP(entry->key))

In fact it's correct, because it compares results of two detoasting. If
datum isn't toasted then results would be the same. And if data is toasted
then results would be two different allocation of detoasted datum.
However, we do extra detoasting here.

For example, see gbt_var_decompress(). There is no extra detoasting here.

GBT_VARKEY *key = (GBT_VARKEY *) PG_DETOAST_DATUM(entry->key);

if (key != (GBT_VARKEY *) DatumGetPointer(entry->key))

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company