diff options
Diffstat (limited to 'contrib/btree_gin/sql')
30 files changed, 562 insertions, 0 deletions
diff --git a/contrib/btree_gin/sql/bit.sql b/contrib/btree_gin/sql/bit.sql new file mode 100644 index 0000000..6762be0 --- /dev/null +++ b/contrib/btree_gin/sql/bit.sql @@ -0,0 +1,15 @@ +set enable_seqscan=off; + +CREATE TABLE test_bit ( + i bit(3) +); + +INSERT INTO test_bit VALUES ('001'),('010'),('011'),('100'),('101'),('110'); + +CREATE INDEX idx_bit ON test_bit USING gin (i); + +SELECT * FROM test_bit WHERE i<'100'::bit(3) ORDER BY i; +SELECT * FROM test_bit WHERE i<='100'::bit(3) ORDER BY i; +SELECT * FROM test_bit WHERE i='100'::bit(3) ORDER BY i; +SELECT * FROM test_bit WHERE i>='100'::bit(3) ORDER BY i; +SELECT * FROM test_bit WHERE i>'100'::bit(3) ORDER BY i; diff --git a/contrib/btree_gin/sql/bool.sql b/contrib/btree_gin/sql/bool.sql new file mode 100644 index 0000000..dad2ff3 --- /dev/null +++ b/contrib/btree_gin/sql/bool.sql @@ -0,0 +1,27 @@ +set enable_seqscan=off; + +CREATE TABLE test_bool ( + i boolean +); + +INSERT INTO test_bool VALUES (false),(true),(null); + +CREATE INDEX idx_bool ON test_bool USING gin (i); + +SELECT * FROM test_bool WHERE i<true ORDER BY i; +SELECT * FROM test_bool WHERE i<=true ORDER BY i; +SELECT * FROM test_bool WHERE i=true ORDER BY i; +SELECT * FROM test_bool WHERE i>=true ORDER BY i; +SELECT * FROM test_bool WHERE i>true ORDER BY i; + +SELECT * FROM test_bool WHERE i<false ORDER BY i; +SELECT * FROM test_bool WHERE i<=false ORDER BY i; +SELECT * FROM test_bool WHERE i=false ORDER BY i; +SELECT * FROM test_bool WHERE i>=false ORDER BY i; +SELECT * FROM test_bool WHERE i>false ORDER BY i; + +EXPLAIN (COSTS OFF) SELECT * FROM test_bool WHERE i<true ORDER BY i; +EXPLAIN (COSTS OFF) SELECT * FROM test_bool WHERE i<=true ORDER BY i; +EXPLAIN (COSTS OFF) SELECT * FROM test_bool WHERE i=true ORDER BY i; +EXPLAIN (COSTS OFF) SELECT * FROM test_bool WHERE i>=true ORDER BY i; +EXPLAIN (COSTS OFF) SELECT * FROM test_bool WHERE i>true ORDER BY i; diff --git a/contrib/btree_gin/sql/bpchar.sql b/contrib/btree_gin/sql/bpchar.sql new file mode 100644 index 0000000..4c951e3 --- /dev/null +++ b/contrib/btree_gin/sql/bpchar.sql @@ -0,0 +1,22 @@ +set enable_seqscan=off; + +CREATE TABLE test_bpchar ( + i char(10) +); + +INSERT INTO test_bpchar VALUES ('a'),('ab'),('abc'),('abc '),('abb'),('axy'),('xyz'),('xyz '); + +CREATE INDEX idx_bpchar ON test_bpchar USING gin (i); + +SELECT * FROM test_bpchar WHERE i<'abc' ORDER BY i; +SELECT * FROM test_bpchar WHERE i<='abc' ORDER BY i; +SELECT * FROM test_bpchar WHERE i='abc' ORDER BY i; +SELECT * FROM test_bpchar WHERE i='abc ' ORDER BY i; +SELECT * FROM test_bpchar WHERE i>='abc' ORDER BY i; +SELECT * FROM test_bpchar WHERE i>'abc' ORDER BY i; + +EXPLAIN (COSTS OFF) SELECT * FROM test_bpchar WHERE i<'abc' ORDER BY i; +EXPLAIN (COSTS OFF) SELECT * FROM test_bpchar WHERE i<='abc' ORDER BY i; +EXPLAIN (COSTS OFF) SELECT * FROM test_bpchar WHERE i='abc' ORDER BY i; +EXPLAIN (COSTS OFF) SELECT * FROM test_bpchar WHERE i>='abc' ORDER BY i; +EXPLAIN (COSTS OFF) SELECT * FROM test_bpchar WHERE i>'abc' ORDER BY i; diff --git a/contrib/btree_gin/sql/bytea.sql b/contrib/btree_gin/sql/bytea.sql new file mode 100644 index 0000000..5f3eb11 --- /dev/null +++ b/contrib/btree_gin/sql/bytea.sql @@ -0,0 +1,17 @@ +set enable_seqscan=off; +-- ensure consistent test output regardless of the default bytea format +SET bytea_output TO escape; + +CREATE TABLE test_bytea ( + i bytea +); + +INSERT INTO test_bytea VALUES ('a'),('ab'),('abc'),('abb'),('axy'),('xyz'); + +CREATE INDEX idx_bytea ON test_bytea USING gin (i); + +SELECT * FROM test_bytea WHERE i<'abc'::bytea ORDER BY i; +SELECT * FROM test_bytea WHERE i<='abc'::bytea ORDER BY i; +SELECT * FROM test_bytea WHERE i='abc'::bytea ORDER BY i; +SELECT * FROM test_bytea WHERE i>='abc'::bytea ORDER BY i; +SELECT * FROM test_bytea WHERE i>'abc'::bytea ORDER BY i; diff --git a/contrib/btree_gin/sql/char.sql b/contrib/btree_gin/sql/char.sql new file mode 100644 index 0000000..1f114a5 --- /dev/null +++ b/contrib/btree_gin/sql/char.sql @@ -0,0 +1,15 @@ +set enable_seqscan=off; + +CREATE TABLE test_char ( + i "char" +); + +INSERT INTO test_char VALUES ('a'),('b'),('c'),('d'),('e'),('f'); + +CREATE INDEX idx_char ON test_char USING gin (i); + +SELECT * FROM test_char WHERE i<'d'::"char" ORDER BY i; +SELECT * FROM test_char WHERE i<='d'::"char" ORDER BY i; +SELECT * FROM test_char WHERE i='d'::"char" ORDER BY i; +SELECT * FROM test_char WHERE i>='d'::"char" ORDER BY i; +SELECT * FROM test_char WHERE i>'d'::"char" ORDER BY i; diff --git a/contrib/btree_gin/sql/cidr.sql b/contrib/btree_gin/sql/cidr.sql new file mode 100644 index 0000000..4a76e5f --- /dev/null +++ b/contrib/btree_gin/sql/cidr.sql @@ -0,0 +1,22 @@ +set enable_seqscan=off; + +CREATE TABLE test_cidr ( + i cidr +); + +INSERT INTO test_cidr VALUES + ( '1.2.3.4' ), + ( '1.2.4.4' ), + ( '1.2.5.4' ), + ( '1.2.6.4' ), + ( '1.2.7.4' ), + ( '1.2.8.4' ) +; + +CREATE INDEX idx_cidr ON test_cidr USING gin (i); + +SELECT * FROM test_cidr WHERE i<'1.2.6.4'::cidr ORDER BY i; +SELECT * FROM test_cidr WHERE i<='1.2.6.4'::cidr ORDER BY i; +SELECT * FROM test_cidr WHERE i='1.2.6.4'::cidr ORDER BY i; +SELECT * FROM test_cidr WHERE i>='1.2.6.4'::cidr ORDER BY i; +SELECT * FROM test_cidr WHERE i>'1.2.6.4'::cidr ORDER BY i; diff --git a/contrib/btree_gin/sql/date.sql b/contrib/btree_gin/sql/date.sql new file mode 100644 index 0000000..35086f6 --- /dev/null +++ b/contrib/btree_gin/sql/date.sql @@ -0,0 +1,22 @@ +set enable_seqscan=off; + +CREATE TABLE test_date ( + i date +); + +INSERT INTO test_date VALUES + ( '2004-10-23' ), + ( '2004-10-24' ), + ( '2004-10-25' ), + ( '2004-10-26' ), + ( '2004-10-27' ), + ( '2004-10-28' ) +; + +CREATE INDEX idx_date ON test_date USING gin (i); + +SELECT * FROM test_date WHERE i<'2004-10-26'::date ORDER BY i; +SELECT * FROM test_date WHERE i<='2004-10-26'::date ORDER BY i; +SELECT * FROM test_date WHERE i='2004-10-26'::date ORDER BY i; +SELECT * FROM test_date WHERE i>='2004-10-26'::date ORDER BY i; +SELECT * FROM test_date WHERE i>'2004-10-26'::date ORDER BY i; diff --git a/contrib/btree_gin/sql/enum.sql b/contrib/btree_gin/sql/enum.sql new file mode 100644 index 0000000..ec2a622 --- /dev/null +++ b/contrib/btree_gin/sql/enum.sql @@ -0,0 +1,26 @@ +set enable_seqscan=off; + +CREATE TYPE rainbow AS ENUM ('r','o','y','g','b','i','v'); + +CREATE TABLE test_enum ( + i rainbow +); + +INSERT INTO test_enum VALUES ('v'),('y'),('r'),('g'),('o'),('i'),('b'); + +CREATE INDEX idx_enum ON test_enum USING gin (i); + +SELECT * FROM test_enum WHERE i<'g'::rainbow ORDER BY i; +SELECT * FROM test_enum WHERE i<='g'::rainbow ORDER BY i; +SELECT * FROM test_enum WHERE i='g'::rainbow ORDER BY i; +SELECT * FROM test_enum WHERE i>='g'::rainbow ORDER BY i; +SELECT * FROM test_enum WHERE i>'g'::rainbow ORDER BY i; + +explain (costs off) SELECT * FROM test_enum WHERE i>='g'::rainbow ORDER BY i; + + +-- make sure we handle the non-evenly-numbered oid case for enums +create type e as enum ('0', '2', '3'); +alter type e add value '1' after '0'; +create table t as select (i % 4)::text::e from generate_series(0, 100000) as i; +create index on t using gin (e); diff --git a/contrib/btree_gin/sql/float4.sql b/contrib/btree_gin/sql/float4.sql new file mode 100644 index 0000000..759778a --- /dev/null +++ b/contrib/btree_gin/sql/float4.sql @@ -0,0 +1,15 @@ +set enable_seqscan=off; + +CREATE TABLE test_float4 ( + i float4 +); + +INSERT INTO test_float4 VALUES (-2),(-1),(0),(1),(2),(3); + +CREATE INDEX idx_float4 ON test_float4 USING gin (i); + +SELECT * FROM test_float4 WHERE i<1::float4 ORDER BY i; +SELECT * FROM test_float4 WHERE i<=1::float4 ORDER BY i; +SELECT * FROM test_float4 WHERE i=1::float4 ORDER BY i; +SELECT * FROM test_float4 WHERE i>=1::float4 ORDER BY i; +SELECT * FROM test_float4 WHERE i>1::float4 ORDER BY i; diff --git a/contrib/btree_gin/sql/float8.sql b/contrib/btree_gin/sql/float8.sql new file mode 100644 index 0000000..b046ac4 --- /dev/null +++ b/contrib/btree_gin/sql/float8.sql @@ -0,0 +1,15 @@ +set enable_seqscan=off; + +CREATE TABLE test_float8 ( + i float8 +); + +INSERT INTO test_float8 VALUES (-2),(-1),(0),(1),(2),(3); + +CREATE INDEX idx_float8 ON test_float8 USING gin (i); + +SELECT * FROM test_float8 WHERE i<1::float8 ORDER BY i; +SELECT * FROM test_float8 WHERE i<=1::float8 ORDER BY i; +SELECT * FROM test_float8 WHERE i=1::float8 ORDER BY i; +SELECT * FROM test_float8 WHERE i>=1::float8 ORDER BY i; +SELECT * FROM test_float8 WHERE i>1::float8 ORDER BY i; diff --git a/contrib/btree_gin/sql/inet.sql b/contrib/btree_gin/sql/inet.sql new file mode 100644 index 0000000..e5ec087 --- /dev/null +++ b/contrib/btree_gin/sql/inet.sql @@ -0,0 +1,22 @@ +set enable_seqscan=off; + +CREATE TABLE test_inet ( + i inet +); + +INSERT INTO test_inet VALUES + ( '1.2.3.4/16' ), + ( '1.2.4.4/16' ), + ( '1.2.5.4/16' ), + ( '1.2.6.4/16' ), + ( '1.2.7.4/16' ), + ( '1.2.8.4/16' ) +; + +CREATE INDEX idx_inet ON test_inet USING gin (i); + +SELECT * FROM test_inet WHERE i<'1.2.6.4/16'::inet ORDER BY i; +SELECT * FROM test_inet WHERE i<='1.2.6.4/16'::inet ORDER BY i; +SELECT * FROM test_inet WHERE i='1.2.6.4/16'::inet ORDER BY i; +SELECT * FROM test_inet WHERE i>='1.2.6.4/16'::inet ORDER BY i; +SELECT * FROM test_inet WHERE i>'1.2.6.4/16'::inet ORDER BY i; diff --git a/contrib/btree_gin/sql/install_btree_gin.sql b/contrib/btree_gin/sql/install_btree_gin.sql new file mode 100644 index 0000000..746e776 --- /dev/null +++ b/contrib/btree_gin/sql/install_btree_gin.sql @@ -0,0 +1,6 @@ +CREATE EXTENSION btree_gin; + +-- Check whether any of our opclasses fail amvalidate +SELECT amname, opcname +FROM pg_opclass opc LEFT JOIN pg_am am ON am.oid = opcmethod +WHERE opc.oid >= 16384 AND NOT amvalidate(opc.oid); diff --git a/contrib/btree_gin/sql/int2.sql b/contrib/btree_gin/sql/int2.sql new file mode 100644 index 0000000..f06f117 --- /dev/null +++ b/contrib/btree_gin/sql/int2.sql @@ -0,0 +1,15 @@ +set enable_seqscan=off; + +CREATE TABLE test_int2 ( + i int2 +); + +INSERT INTO test_int2 VALUES (-2),(-1),(0),(1),(2),(3); + +CREATE INDEX idx_int2 ON test_int2 USING gin (i); + +SELECT * FROM test_int2 WHERE i<1::int2 ORDER BY i; +SELECT * FROM test_int2 WHERE i<=1::int2 ORDER BY i; +SELECT * FROM test_int2 WHERE i=1::int2 ORDER BY i; +SELECT * FROM test_int2 WHERE i>=1::int2 ORDER BY i; +SELECT * FROM test_int2 WHERE i>1::int2 ORDER BY i; diff --git a/contrib/btree_gin/sql/int4.sql b/contrib/btree_gin/sql/int4.sql new file mode 100644 index 0000000..6499c29 --- /dev/null +++ b/contrib/btree_gin/sql/int4.sql @@ -0,0 +1,15 @@ +set enable_seqscan=off; + +CREATE TABLE test_int4 ( + i int4 +); + +INSERT INTO test_int4 VALUES (-2),(-1),(0),(1),(2),(3); + +CREATE INDEX idx_int4 ON test_int4 USING gin (i); + +SELECT * FROM test_int4 WHERE i<1::int4 ORDER BY i; +SELECT * FROM test_int4 WHERE i<=1::int4 ORDER BY i; +SELECT * FROM test_int4 WHERE i=1::int4 ORDER BY i; +SELECT * FROM test_int4 WHERE i>=1::int4 ORDER BY i; +SELECT * FROM test_int4 WHERE i>1::int4 ORDER BY i; diff --git a/contrib/btree_gin/sql/int8.sql b/contrib/btree_gin/sql/int8.sql new file mode 100644 index 0000000..4d9c287 --- /dev/null +++ b/contrib/btree_gin/sql/int8.sql @@ -0,0 +1,15 @@ +set enable_seqscan=off; + +CREATE TABLE test_int8 ( + i int8 +); + +INSERT INTO test_int8 VALUES (-2),(-1),(0),(1),(2),(3); + +CREATE INDEX idx_int8 ON test_int8 USING gin (i); + +SELECT * FROM test_int8 WHERE i<1::int8 ORDER BY i; +SELECT * FROM test_int8 WHERE i<=1::int8 ORDER BY i; +SELECT * FROM test_int8 WHERE i=1::int8 ORDER BY i; +SELECT * FROM test_int8 WHERE i>=1::int8 ORDER BY i; +SELECT * FROM test_int8 WHERE i>1::int8 ORDER BY i; diff --git a/contrib/btree_gin/sql/interval.sql b/contrib/btree_gin/sql/interval.sql new file mode 100644 index 0000000..e385158 --- /dev/null +++ b/contrib/btree_gin/sql/interval.sql @@ -0,0 +1,22 @@ +set enable_seqscan=off; + +CREATE TABLE test_interval ( + i interval +); + +INSERT INTO test_interval VALUES + ( '03:55:08' ), + ( '04:55:08' ), + ( '05:55:08' ), + ( '08:55:08' ), + ( '09:55:08' ), + ( '10:55:08' ) +; + +CREATE INDEX idx_interval ON test_interval USING gin (i); + +SELECT * FROM test_interval WHERE i<'08:55:08'::interval ORDER BY i; +SELECT * FROM test_interval WHERE i<='08:55:08'::interval ORDER BY i; +SELECT * FROM test_interval WHERE i='08:55:08'::interval ORDER BY i; +SELECT * FROM test_interval WHERE i>='08:55:08'::interval ORDER BY i; +SELECT * FROM test_interval WHERE i>'08:55:08'::interval ORDER BY i; diff --git a/contrib/btree_gin/sql/macaddr.sql b/contrib/btree_gin/sql/macaddr.sql new file mode 100644 index 0000000..66566aa --- /dev/null +++ b/contrib/btree_gin/sql/macaddr.sql @@ -0,0 +1,22 @@ +set enable_seqscan=off; + +CREATE TABLE test_macaddr ( + i macaddr +); + +INSERT INTO test_macaddr VALUES + ( '22:00:5c:03:55:08' ), + ( '22:00:5c:04:55:08' ), + ( '22:00:5c:05:55:08' ), + ( '22:00:5c:08:55:08' ), + ( '22:00:5c:09:55:08' ), + ( '22:00:5c:10:55:08' ) +; + +CREATE INDEX idx_macaddr ON test_macaddr USING gin (i); + +SELECT * FROM test_macaddr WHERE i<'22:00:5c:08:55:08'::macaddr ORDER BY i; +SELECT * FROM test_macaddr WHERE i<='22:00:5c:08:55:08'::macaddr ORDER BY i; +SELECT * FROM test_macaddr WHERE i='22:00:5c:08:55:08'::macaddr ORDER BY i; +SELECT * FROM test_macaddr WHERE i>='22:00:5c:08:55:08'::macaddr ORDER BY i; +SELECT * FROM test_macaddr WHERE i>'22:00:5c:08:55:08'::macaddr ORDER BY i; diff --git a/contrib/btree_gin/sql/macaddr8.sql b/contrib/btree_gin/sql/macaddr8.sql new file mode 100644 index 0000000..86785c3 --- /dev/null +++ b/contrib/btree_gin/sql/macaddr8.sql @@ -0,0 +1,22 @@ +set enable_seqscan=off; + +CREATE TABLE test_macaddr8 ( + i macaddr8 +); + +INSERT INTO test_macaddr8 VALUES + ( '22:00:5c:03:55:08:01:02' ), + ( '22:00:5c:04:55:08:01:02' ), + ( '22:00:5c:05:55:08:01:02' ), + ( '22:00:5c:08:55:08:01:02' ), + ( '22:00:5c:09:55:08:01:02' ), + ( '22:00:5c:10:55:08:01:02' ) +; + +CREATE INDEX idx_macaddr8 ON test_macaddr8 USING gin (i); + +SELECT * FROM test_macaddr8 WHERE i<'22:00:5c:08:55:08:01:02'::macaddr8 ORDER BY i; +SELECT * FROM test_macaddr8 WHERE i<='22:00:5c:08:55:08:01:02'::macaddr8 ORDER BY i; +SELECT * FROM test_macaddr8 WHERE i='22:00:5c:08:55:08:01:02'::macaddr8 ORDER BY i; +SELECT * FROM test_macaddr8 WHERE i>='22:00:5c:08:55:08:01:02'::macaddr8 ORDER BY i; +SELECT * FROM test_macaddr8 WHERE i>'22:00:5c:08:55:08:01:02'::macaddr8 ORDER BY i; diff --git a/contrib/btree_gin/sql/money.sql b/contrib/btree_gin/sql/money.sql new file mode 100644 index 0000000..4a9c8c5 --- /dev/null +++ b/contrib/btree_gin/sql/money.sql @@ -0,0 +1,15 @@ +set enable_seqscan=off; + +CREATE TABLE test_money ( + i money +); + +INSERT INTO test_money VALUES ('-2'),('-1'),('0'),('1'),('2'),('3'); + +CREATE INDEX idx_money ON test_money USING gin (i); + +SELECT * FROM test_money WHERE i<'1'::money ORDER BY i; +SELECT * FROM test_money WHERE i<='1'::money ORDER BY i; +SELECT * FROM test_money WHERE i='1'::money ORDER BY i; +SELECT * FROM test_money WHERE i>='1'::money ORDER BY i; +SELECT * FROM test_money WHERE i>'1'::money ORDER BY i; diff --git a/contrib/btree_gin/sql/name.sql b/contrib/btree_gin/sql/name.sql new file mode 100644 index 0000000..c11580c --- /dev/null +++ b/contrib/btree_gin/sql/name.sql @@ -0,0 +1,21 @@ +set enable_seqscan=off; + +CREATE TABLE test_name ( + i name +); + +INSERT INTO test_name VALUES ('a'),('ab'),('abc'),('abb'),('axy'),('xyz'); + +CREATE INDEX idx_name ON test_name USING gin (i); + +SELECT * FROM test_name WHERE i<'abc' ORDER BY i; +SELECT * FROM test_name WHERE i<='abc' ORDER BY i; +SELECT * FROM test_name WHERE i='abc' ORDER BY i; +SELECT * FROM test_name WHERE i>='abc' ORDER BY i; +SELECT * FROM test_name WHERE i>'abc' ORDER BY i; + +EXPLAIN (COSTS OFF) SELECT * FROM test_name WHERE i<'abc' ORDER BY i; +EXPLAIN (COSTS OFF) SELECT * FROM test_name WHERE i<='abc' ORDER BY i; +EXPLAIN (COSTS OFF) SELECT * FROM test_name WHERE i='abc' ORDER BY i; +EXPLAIN (COSTS OFF) SELECT * FROM test_name WHERE i>='abc' ORDER BY i; +EXPLAIN (COSTS OFF) SELECT * FROM test_name WHERE i>'abc' ORDER BY i; diff --git a/contrib/btree_gin/sql/numeric.sql b/contrib/btree_gin/sql/numeric.sql new file mode 100644 index 0000000..dbaaa2c --- /dev/null +++ b/contrib/btree_gin/sql/numeric.sql @@ -0,0 +1,15 @@ +set enable_seqscan=off; + +CREATE TABLE test_numeric ( + i numeric +); + +INSERT INTO test_numeric VALUES (-2),(-1),(0),(1),(2),(3); + +CREATE INDEX idx_numeric ON test_numeric USING gin (i); + +SELECT * FROM test_numeric WHERE i<'1'::numeric ORDER BY i; +SELECT * FROM test_numeric WHERE i<='1'::numeric ORDER BY i; +SELECT * FROM test_numeric WHERE i='1'::numeric ORDER BY i; +SELECT * FROM test_numeric WHERE i>='1'::numeric ORDER BY i; +SELECT * FROM test_numeric WHERE i>'1'::numeric ORDER BY i; diff --git a/contrib/btree_gin/sql/oid.sql b/contrib/btree_gin/sql/oid.sql new file mode 100644 index 0000000..1739f80 --- /dev/null +++ b/contrib/btree_gin/sql/oid.sql @@ -0,0 +1,15 @@ +set enable_seqscan=off; + +CREATE TABLE test_oid ( + i oid +); + +INSERT INTO test_oid VALUES (0),(1),(2),(3),(4),(5); + +CREATE INDEX idx_oid ON test_oid USING gin (i); + +SELECT * FROM test_oid WHERE i<3::oid ORDER BY i; +SELECT * FROM test_oid WHERE i<=3::oid ORDER BY i; +SELECT * FROM test_oid WHERE i=3::oid ORDER BY i; +SELECT * FROM test_oid WHERE i>=3::oid ORDER BY i; +SELECT * FROM test_oid WHERE i>3::oid ORDER BY i; diff --git a/contrib/btree_gin/sql/text.sql b/contrib/btree_gin/sql/text.sql new file mode 100644 index 0000000..d5b3b39 --- /dev/null +++ b/contrib/btree_gin/sql/text.sql @@ -0,0 +1,15 @@ +set enable_seqscan=off; + +CREATE TABLE test_text ( + i text +); + +INSERT INTO test_text VALUES ('a'),('ab'),('abc'),('abb'),('axy'),('xyz'); + +CREATE INDEX idx_text ON test_text USING gin (i); + +SELECT * FROM test_text WHERE i<'abc' ORDER BY i; +SELECT * FROM test_text WHERE i<='abc' ORDER BY i; +SELECT * FROM test_text WHERE i='abc' ORDER BY i; +SELECT * FROM test_text WHERE i>='abc' ORDER BY i; +SELECT * FROM test_text WHERE i>'abc' ORDER BY i; diff --git a/contrib/btree_gin/sql/time.sql b/contrib/btree_gin/sql/time.sql new file mode 100644 index 0000000..62d709a --- /dev/null +++ b/contrib/btree_gin/sql/time.sql @@ -0,0 +1,22 @@ +set enable_seqscan=off; + +CREATE TABLE test_time ( + i time +); + +INSERT INTO test_time VALUES + ( '03:55:08' ), + ( '04:55:08' ), + ( '05:55:08' ), + ( '08:55:08' ), + ( '09:55:08' ), + ( '10:55:08' ) +; + +CREATE INDEX idx_time ON test_time USING gin (i); + +SELECT * FROM test_time WHERE i<'08:55:08'::time ORDER BY i; +SELECT * FROM test_time WHERE i<='08:55:08'::time ORDER BY i; +SELECT * FROM test_time WHERE i='08:55:08'::time ORDER BY i; +SELECT * FROM test_time WHERE i>='08:55:08'::time ORDER BY i; +SELECT * FROM test_time WHERE i>'08:55:08'::time ORDER BY i; diff --git a/contrib/btree_gin/sql/timestamp.sql b/contrib/btree_gin/sql/timestamp.sql new file mode 100644 index 0000000..56727e8 --- /dev/null +++ b/contrib/btree_gin/sql/timestamp.sql @@ -0,0 +1,22 @@ +set enable_seqscan=off; + +CREATE TABLE test_timestamp ( + i timestamp +); + +INSERT INTO test_timestamp VALUES + ( '2004-10-26 03:55:08' ), + ( '2004-10-26 04:55:08' ), + ( '2004-10-26 05:55:08' ), + ( '2004-10-26 08:55:08' ), + ( '2004-10-26 09:55:08' ), + ( '2004-10-26 10:55:08' ) +; + +CREATE INDEX idx_timestamp ON test_timestamp USING gin (i); + +SELECT * FROM test_timestamp WHERE i<'2004-10-26 08:55:08'::timestamp ORDER BY i; +SELECT * FROM test_timestamp WHERE i<='2004-10-26 08:55:08'::timestamp ORDER BY i; +SELECT * FROM test_timestamp WHERE i='2004-10-26 08:55:08'::timestamp ORDER BY i; +SELECT * FROM test_timestamp WHERE i>='2004-10-26 08:55:08'::timestamp ORDER BY i; +SELECT * FROM test_timestamp WHERE i>'2004-10-26 08:55:08'::timestamp ORDER BY i; diff --git a/contrib/btree_gin/sql/timestamptz.sql b/contrib/btree_gin/sql/timestamptz.sql new file mode 100644 index 0000000..e6cfdb1 --- /dev/null +++ b/contrib/btree_gin/sql/timestamptz.sql @@ -0,0 +1,22 @@ +set enable_seqscan=off; + +CREATE TABLE test_timestamptz ( + i timestamptz +); + +INSERT INTO test_timestamptz VALUES + ( '2004-10-26 03:55:08' ), + ( '2004-10-26 04:55:08' ), + ( '2004-10-26 05:55:08' ), + ( '2004-10-26 08:55:08' ), + ( '2004-10-26 09:55:08' ), + ( '2004-10-26 10:55:08' ) +; + +CREATE INDEX idx_timestamptz ON test_timestamptz USING gin (i); + +SELECT * FROM test_timestamptz WHERE i<'2004-10-26 08:55:08'::timestamptz ORDER BY i; +SELECT * FROM test_timestamptz WHERE i<='2004-10-26 08:55:08'::timestamptz ORDER BY i; +SELECT * FROM test_timestamptz WHERE i='2004-10-26 08:55:08'::timestamptz ORDER BY i; +SELECT * FROM test_timestamptz WHERE i>='2004-10-26 08:55:08'::timestamptz ORDER BY i; +SELECT * FROM test_timestamptz WHERE i>'2004-10-26 08:55:08'::timestamptz ORDER BY i; diff --git a/contrib/btree_gin/sql/timetz.sql b/contrib/btree_gin/sql/timetz.sql new file mode 100644 index 0000000..ca947b7 --- /dev/null +++ b/contrib/btree_gin/sql/timetz.sql @@ -0,0 +1,22 @@ +set enable_seqscan=off; + +CREATE TABLE test_timetz ( + i timetz +); + +INSERT INTO test_timetz VALUES + ( '03:55:08 GMT+2' ), + ( '04:55:08 GMT+2' ), + ( '05:55:08 GMT+2' ), + ( '08:55:08 GMT+2' ), + ( '09:55:08 GMT+2' ), + ( '10:55:08 GMT+2' ) +; + +CREATE INDEX idx_timetz ON test_timetz USING gin (i); + +SELECT * FROM test_timetz WHERE i<'08:55:08 GMT+2'::timetz ORDER BY i; +SELECT * FROM test_timetz WHERE i<='08:55:08 GMT+2'::timetz ORDER BY i; +SELECT * FROM test_timetz WHERE i='08:55:08 GMT+2'::timetz ORDER BY i; +SELECT * FROM test_timetz WHERE i>='08:55:08 GMT+2'::timetz ORDER BY i; +SELECT * FROM test_timetz WHERE i>'08:55:08 GMT+2'::timetz ORDER BY i; diff --git a/contrib/btree_gin/sql/uuid.sql b/contrib/btree_gin/sql/uuid.sql new file mode 100644 index 0000000..3c141bd --- /dev/null +++ b/contrib/btree_gin/sql/uuid.sql @@ -0,0 +1,28 @@ +set enable_seqscan=off; + +CREATE TABLE test_uuid ( + i uuid +); + +INSERT INTO test_uuid VALUES + ( '00000000-0000-0000-0000-000000000000' ), + ( '299bc99f-2f79-4e3e-bfea-2cbfd62a7c27' ), + ( '6264af33-0d43-4337-bf4e-43509b8a4be8' ), + ( 'ce41c936-6acb-4feb-8c91-852a673e5a5c' ), + ( 'd2ce731f-f2a8-4a2b-be37-8f0ba637427f' ), + ( 'ffffffff-ffff-ffff-ffff-ffffffffffff' ) +; + +CREATE INDEX idx_uuid ON test_uuid USING gin (i); + +SELECT * FROM test_uuid WHERE i<'ce41c936-6acb-4feb-8c91-852a673e5a5c'::uuid ORDER BY i; +SELECT * FROM test_uuid WHERE i<='ce41c936-6acb-4feb-8c91-852a673e5a5c'::uuid ORDER BY i; +SELECT * FROM test_uuid WHERE i='ce41c936-6acb-4feb-8c91-852a673e5a5c'::uuid ORDER BY i; +SELECT * FROM test_uuid WHERE i>='ce41c936-6acb-4feb-8c91-852a673e5a5c'::uuid ORDER BY i; +SELECT * FROM test_uuid WHERE i>'ce41c936-6acb-4feb-8c91-852a673e5a5c'::uuid ORDER BY i; + +EXPLAIN (COSTS OFF) SELECT * FROM test_uuid WHERE i<'ce41c936-6acb-4feb-8c91-852a673e5a5c'::uuid ORDER BY i; +EXPLAIN (COSTS OFF) SELECT * FROM test_uuid WHERE i<='ce41c936-6acb-4feb-8c91-852a673e5a5c'::uuid ORDER BY i; +EXPLAIN (COSTS OFF) SELECT * FROM test_uuid WHERE i='ce41c936-6acb-4feb-8c91-852a673e5a5c'::uuid ORDER BY i; +EXPLAIN (COSTS OFF) SELECT * FROM test_uuid WHERE i>='ce41c936-6acb-4feb-8c91-852a673e5a5c'::uuid ORDER BY i; +EXPLAIN (COSTS OFF) SELECT * FROM test_uuid WHERE i>'ce41c936-6acb-4feb-8c91-852a673e5a5c'::uuid ORDER BY i; diff --git a/contrib/btree_gin/sql/varbit.sql b/contrib/btree_gin/sql/varbit.sql new file mode 100644 index 0000000..4ad2d0c --- /dev/null +++ b/contrib/btree_gin/sql/varbit.sql @@ -0,0 +1,15 @@ +set enable_seqscan=off; + +CREATE TABLE test_varbit ( + i varbit +); + +INSERT INTO test_varbit VALUES ('001'),('010'),('011'),('100'),('101'),('110'); + +CREATE INDEX idx_varbit ON test_varbit USING gin (i); + +SELECT * FROM test_varbit WHERE i<'100'::varbit ORDER BY i; +SELECT * FROM test_varbit WHERE i<='100'::varbit ORDER BY i; +SELECT * FROM test_varbit WHERE i='100'::varbit ORDER BY i; +SELECT * FROM test_varbit WHERE i>='100'::varbit ORDER BY i; +SELECT * FROM test_varbit WHERE i>'100'::varbit ORDER BY i; diff --git a/contrib/btree_gin/sql/varchar.sql b/contrib/btree_gin/sql/varchar.sql new file mode 100644 index 0000000..dbdacab --- /dev/null +++ b/contrib/btree_gin/sql/varchar.sql @@ -0,0 +1,15 @@ +set enable_seqscan=off; + +CREATE TABLE test_varchar ( + i varchar +); + +INSERT INTO test_varchar VALUES ('a'),('ab'),('abc'),('abb'),('axy'),('xyz'); + +CREATE INDEX idx_varchar ON test_varchar USING gin (i); + +SELECT * FROM test_varchar WHERE i<'abc'::varchar ORDER BY i; +SELECT * FROM test_varchar WHERE i<='abc'::varchar ORDER BY i; +SELECT * FROM test_varchar WHERE i='abc'::varchar ORDER BY i; +SELECT * FROM test_varchar WHERE i>='abc'::varchar ORDER BY i; +SELECT * FROM test_varchar WHERE i>'abc'::varchar ORDER BY i; |