diff options
Diffstat (limited to 'contrib/btree_gin')
68 files changed, 3648 insertions, 0 deletions
diff --git a/contrib/btree_gin/.gitignore b/contrib/btree_gin/.gitignore new file mode 100644 index 0000000..5dcb3ff --- /dev/null +++ b/contrib/btree_gin/.gitignore @@ -0,0 +1,4 @@ +# Generated subdirectories +/log/ +/results/ +/tmp_check/ diff --git a/contrib/btree_gin/Makefile b/contrib/btree_gin/Makefile new file mode 100644 index 0000000..0a15811 --- /dev/null +++ b/contrib/btree_gin/Makefile @@ -0,0 +1,27 @@ +# contrib/btree_gin/Makefile + +MODULE_big = btree_gin +OBJS = \ + $(WIN32RES) \ + btree_gin.o + +EXTENSION = btree_gin +DATA = btree_gin--1.0.sql btree_gin--1.0--1.1.sql btree_gin--1.1--1.2.sql \ + btree_gin--1.2--1.3.sql +PGFILEDESC = "btree_gin - B-tree equivalent GIN operator classes" + +REGRESS = install_btree_gin int2 int4 int8 float4 float8 money oid \ + timestamp timestamptz time timetz date interval \ + macaddr macaddr8 inet cidr text varchar char bytea bit varbit \ + numeric enum uuid name bool bpchar + +ifdef USE_PGXS +PG_CONFIG = pg_config +PGXS := $(shell $(PG_CONFIG) --pgxs) +include $(PGXS) +else +subdir = contrib/btree_gin +top_builddir = ../.. +include $(top_builddir)/src/Makefile.global +include $(top_srcdir)/contrib/contrib-global.mk +endif diff --git a/contrib/btree_gin/btree_gin--1.0--1.1.sql b/contrib/btree_gin/btree_gin--1.0--1.1.sql new file mode 100644 index 0000000..dd81d27 --- /dev/null +++ b/contrib/btree_gin/btree_gin--1.0--1.1.sql @@ -0,0 +1,35 @@ +/* contrib/btree_gin/btree_gin--1.0--1.1.sql */ + +-- complain if script is sourced in psql, rather than via CREATE EXTENSION +\echo Use "ALTER EXTENSION btree_gin UPDATE TO '1.1'" to load this file. \quit + +-- macaddr8 datatype support new in 10.0. +CREATE FUNCTION gin_extract_value_macaddr8(macaddr8, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_macaddr8(macaddr8, macaddr8, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_macaddr8(macaddr8, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS macaddr8_ops +DEFAULT FOR TYPE macaddr8 USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 macaddr8_cmp(macaddr8, macaddr8), + FUNCTION 2 gin_extract_value_macaddr8(macaddr8, internal), + FUNCTION 3 gin_extract_query_macaddr8(macaddr8, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_macaddr8(macaddr8, macaddr8, int2, internal), +STORAGE macaddr8; diff --git a/contrib/btree_gin/btree_gin--1.0.sql b/contrib/btree_gin/btree_gin--1.0.sql new file mode 100644 index 0000000..cf867ef --- /dev/null +++ b/contrib/btree_gin/btree_gin--1.0.sql @@ -0,0 +1,689 @@ +/* contrib/btree_gin/btree_gin--1.0.sql */ + +-- complain if script is sourced in psql, rather than via CREATE EXTENSION +\echo Use "CREATE EXTENSION btree_gin" to load this file. \quit + +CREATE FUNCTION gin_btree_consistent(internal, int2, anyelement, int4, internal, internal) +RETURNS bool +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_value_int2(int2, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_int2(int2, int2, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_int2(int2, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS int2_ops +DEFAULT FOR TYPE int2 USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 btint2cmp(int2,int2), + FUNCTION 2 gin_extract_value_int2(int2, internal), + FUNCTION 3 gin_extract_query_int2(int2, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_int2(int2,int2,int2, internal), +STORAGE int2; + +CREATE FUNCTION gin_extract_value_int4(int4, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_int4(int4, int4, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_int4(int4, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS int4_ops +DEFAULT FOR TYPE int4 USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 btint4cmp(int4,int4), + FUNCTION 2 gin_extract_value_int4(int4, internal), + FUNCTION 3 gin_extract_query_int4(int4, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_int4(int4,int4,int2, internal), +STORAGE int4; + +CREATE FUNCTION gin_extract_value_int8(int8, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_int8(int8, int8, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_int8(int8, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS int8_ops +DEFAULT FOR TYPE int8 USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 btint8cmp(int8,int8), + FUNCTION 2 gin_extract_value_int8(int8, internal), + FUNCTION 3 gin_extract_query_int8(int8, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_int8(int8,int8,int2, internal), +STORAGE int8; + +CREATE FUNCTION gin_extract_value_float4(float4, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_float4(float4, float4, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_float4(float4, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS float4_ops +DEFAULT FOR TYPE float4 USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 btfloat4cmp(float4,float4), + FUNCTION 2 gin_extract_value_float4(float4, internal), + FUNCTION 3 gin_extract_query_float4(float4, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_float4(float4,float4,int2, internal), +STORAGE float4; + +CREATE FUNCTION gin_extract_value_float8(float8, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_float8(float8, float8, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_float8(float8, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS float8_ops +DEFAULT FOR TYPE float8 USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 btfloat8cmp(float8,float8), + FUNCTION 2 gin_extract_value_float8(float8, internal), + FUNCTION 3 gin_extract_query_float8(float8, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_float8(float8,float8,int2, internal), +STORAGE float8; + +CREATE FUNCTION gin_extract_value_money(money, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_money(money, money, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_money(money, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS money_ops +DEFAULT FOR TYPE money USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 cash_cmp(money,money), + FUNCTION 2 gin_extract_value_money(money, internal), + FUNCTION 3 gin_extract_query_money(money, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_money(money,money,int2, internal), +STORAGE money; + +CREATE FUNCTION gin_extract_value_oid(oid, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_oid(oid, oid, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_oid(oid, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS oid_ops +DEFAULT FOR TYPE oid USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 btoidcmp(oid,oid), + FUNCTION 2 gin_extract_value_oid(oid, internal), + FUNCTION 3 gin_extract_query_oid(oid, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_oid(oid,oid,int2, internal), +STORAGE oid; + +CREATE FUNCTION gin_extract_value_timestamp(timestamp, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_timestamp(timestamp, timestamp, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_timestamp(timestamp, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS timestamp_ops +DEFAULT FOR TYPE timestamp USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 timestamp_cmp(timestamp,timestamp), + FUNCTION 2 gin_extract_value_timestamp(timestamp, internal), + FUNCTION 3 gin_extract_query_timestamp(timestamp, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_timestamp(timestamp,timestamp,int2, internal), +STORAGE timestamp; + +CREATE FUNCTION gin_extract_value_timestamptz(timestamptz, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_timestamptz(timestamptz, timestamptz, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_timestamptz(timestamptz, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS timestamptz_ops +DEFAULT FOR TYPE timestamptz USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 timestamptz_cmp(timestamptz,timestamptz), + FUNCTION 2 gin_extract_value_timestamptz(timestamptz, internal), + FUNCTION 3 gin_extract_query_timestamptz(timestamptz, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_timestamptz(timestamptz,timestamptz,int2, internal), +STORAGE timestamptz; + +CREATE FUNCTION gin_extract_value_time(time, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_time(time, time, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_time(time, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS time_ops +DEFAULT FOR TYPE time USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 time_cmp(time,time), + FUNCTION 2 gin_extract_value_time(time, internal), + FUNCTION 3 gin_extract_query_time(time, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_time(time,time,int2, internal), +STORAGE time; + +CREATE FUNCTION gin_extract_value_timetz(timetz, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_timetz(timetz, timetz, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_timetz(timetz, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS timetz_ops +DEFAULT FOR TYPE timetz USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 timetz_cmp(timetz,timetz), + FUNCTION 2 gin_extract_value_timetz(timetz, internal), + FUNCTION 3 gin_extract_query_timetz(timetz, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_timetz(timetz,timetz,int2, internal), +STORAGE timetz; + +CREATE FUNCTION gin_extract_value_date(date, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_date(date, date, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_date(date, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS date_ops +DEFAULT FOR TYPE date USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 date_cmp(date,date), + FUNCTION 2 gin_extract_value_date(date, internal), + FUNCTION 3 gin_extract_query_date(date, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_date(date,date,int2, internal), +STORAGE date; + +CREATE FUNCTION gin_extract_value_interval(interval, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_interval(interval, interval, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_interval(interval, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS interval_ops +DEFAULT FOR TYPE interval USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 interval_cmp(interval,interval), + FUNCTION 2 gin_extract_value_interval(interval, internal), + FUNCTION 3 gin_extract_query_interval(interval, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_interval(interval,interval,int2, internal), +STORAGE interval; + +CREATE FUNCTION gin_extract_value_macaddr(macaddr, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_macaddr(macaddr, macaddr, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_macaddr(macaddr, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS macaddr_ops +DEFAULT FOR TYPE macaddr USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 macaddr_cmp(macaddr,macaddr), + FUNCTION 2 gin_extract_value_macaddr(macaddr, internal), + FUNCTION 3 gin_extract_query_macaddr(macaddr, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_macaddr(macaddr,macaddr,int2, internal), +STORAGE macaddr; + +CREATE FUNCTION gin_extract_value_inet(inet, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_inet(inet, inet, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_inet(inet, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS inet_ops +DEFAULT FOR TYPE inet USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 network_cmp(inet,inet), + FUNCTION 2 gin_extract_value_inet(inet, internal), + FUNCTION 3 gin_extract_query_inet(inet, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_inet(inet,inet,int2, internal), +STORAGE inet; + +CREATE FUNCTION gin_extract_value_cidr(cidr, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_cidr(cidr, cidr, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_cidr(cidr, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS cidr_ops +DEFAULT FOR TYPE cidr USING gin +AS + OPERATOR 1 <(inet,inet), + OPERATOR 2 <=(inet,inet), + OPERATOR 3 =(inet,inet), + OPERATOR 4 >=(inet,inet), + OPERATOR 5 >(inet,inet), + FUNCTION 1 network_cmp(inet,inet), + FUNCTION 2 gin_extract_value_cidr(cidr, internal), + FUNCTION 3 gin_extract_query_cidr(cidr, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_cidr(cidr,cidr,int2, internal), +STORAGE cidr; + +CREATE FUNCTION gin_extract_value_text(text, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_text(text, text, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_text(text, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS text_ops +DEFAULT FOR TYPE text USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 bttextcmp(text,text), + FUNCTION 2 gin_extract_value_text(text, internal), + FUNCTION 3 gin_extract_query_text(text, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_text(text,text,int2, internal), +STORAGE text; + +CREATE OPERATOR CLASS varchar_ops +DEFAULT FOR TYPE varchar USING gin +AS + OPERATOR 1 <(text,text), + OPERATOR 2 <=(text,text), + OPERATOR 3 =(text,text), + OPERATOR 4 >=(text,text), + OPERATOR 5 >(text,text), + FUNCTION 1 bttextcmp(text,text), + FUNCTION 2 gin_extract_value_text(text, internal), + FUNCTION 3 gin_extract_query_text(text, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_text(text,text,int2, internal), +STORAGE varchar; + +CREATE FUNCTION gin_extract_value_char("char", internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_char("char", "char", int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_char("char", internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS char_ops +DEFAULT FOR TYPE "char" USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 btcharcmp("char","char"), + FUNCTION 2 gin_extract_value_char("char", internal), + FUNCTION 3 gin_extract_query_char("char", internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_char("char","char",int2, internal), +STORAGE "char"; + +CREATE FUNCTION gin_extract_value_bytea(bytea, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_bytea(bytea, bytea, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_bytea(bytea, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS bytea_ops +DEFAULT FOR TYPE bytea USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 byteacmp(bytea,bytea), + FUNCTION 2 gin_extract_value_bytea(bytea, internal), + FUNCTION 3 gin_extract_query_bytea(bytea, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_bytea(bytea,bytea,int2, internal), +STORAGE bytea; + +CREATE FUNCTION gin_extract_value_bit(bit, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_bit(bit, bit, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_bit(bit, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS bit_ops +DEFAULT FOR TYPE bit USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 bitcmp(bit,bit), + FUNCTION 2 gin_extract_value_bit(bit, internal), + FUNCTION 3 gin_extract_query_bit(bit, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_bit(bit,bit,int2, internal), +STORAGE bit; + +CREATE FUNCTION gin_extract_value_varbit(varbit, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_varbit(varbit, varbit, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_varbit(varbit, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS varbit_ops +DEFAULT FOR TYPE varbit USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 varbitcmp(varbit,varbit), + FUNCTION 2 gin_extract_value_varbit(varbit, internal), + FUNCTION 3 gin_extract_query_varbit(varbit, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_varbit(varbit,varbit,int2, internal), +STORAGE varbit; + +CREATE FUNCTION gin_extract_value_numeric(numeric, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_numeric(numeric, numeric, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_numeric(numeric, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_numeric_cmp(numeric, numeric) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS numeric_ops +DEFAULT FOR TYPE numeric USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 gin_numeric_cmp(numeric,numeric), + FUNCTION 2 gin_extract_value_numeric(numeric, internal), + FUNCTION 3 gin_extract_query_numeric(numeric, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_numeric(numeric,numeric,int2, internal), +STORAGE numeric; diff --git a/contrib/btree_gin/btree_gin--1.1--1.2.sql b/contrib/btree_gin/btree_gin--1.1--1.2.sql new file mode 100644 index 0000000..2a16837 --- /dev/null +++ b/contrib/btree_gin/btree_gin--1.1--1.2.sql @@ -0,0 +1,47 @@ +/* contrib/btree_gin/btree_gin--1.1--1.2.sql */ + +-- complain if script is sourced in psql, rather than via CREATE EXTENSION +\echo Use "ALTER EXTENSION btree_gin UPDATE TO '1.1'" to load this file. \quit + +-- +-- +-- +-- enum ops +-- +-- + + +CREATE FUNCTION gin_extract_value_anyenum(anyenum, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_anyenum(anyenum, anyenum, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_anyenum(anyenum, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_enum_cmp(anyenum, anyenum) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS enum_ops +DEFAULT FOR TYPE anyenum USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 gin_enum_cmp(anyenum,anyenum), + FUNCTION 2 gin_extract_value_anyenum(anyenum, internal), + FUNCTION 3 gin_extract_query_anyenum(anyenum, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_anyenum(anyenum,anyenum,int2, internal), +STORAGE anyenum; diff --git a/contrib/btree_gin/btree_gin--1.2--1.3.sql b/contrib/btree_gin/btree_gin--1.2--1.3.sql new file mode 100644 index 0000000..db675b7 --- /dev/null +++ b/contrib/btree_gin/btree_gin--1.2--1.3.sql @@ -0,0 +1,128 @@ +/* contrib/btree_gin/btree_gin--1.2--1.3.sql */ + +-- complain if script is sourced in psql, rather than via CREATE EXTENSION +\echo Use "ALTER EXTENSION btree_gin UPDATE TO '1.3'" to load this file. \quit + +-- uuid datatype support new in 1.3. +CREATE FUNCTION gin_extract_value_uuid(uuid, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_uuid(uuid, uuid, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_uuid(uuid, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS uuid_ops +DEFAULT FOR TYPE uuid USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 uuid_cmp(uuid,uuid), + FUNCTION 2 gin_extract_value_uuid(uuid, internal), + FUNCTION 3 gin_extract_query_uuid(uuid, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_uuid(uuid,uuid,int2, internal), +STORAGE uuid; + +-- name datatype support new in 1.3. +CREATE FUNCTION gin_extract_value_name(name, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_name(name, name, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_name(name, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS name_ops +DEFAULT FOR TYPE name USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 btnamecmp(name,name), + FUNCTION 2 gin_extract_value_name(name, internal), + FUNCTION 3 gin_extract_query_name(name, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_name(name,name,int2, internal), +STORAGE name; + +-- bool datatype support new in 1.3. +CREATE FUNCTION gin_extract_value_bool(bool, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_bool(bool, bool, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_bool(bool, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS bool_ops +DEFAULT FOR TYPE bool USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 btboolcmp(bool,bool), + FUNCTION 2 gin_extract_value_bool(bool, internal), + FUNCTION 3 gin_extract_query_bool(bool, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_bool(bool,bool,int2, internal), +STORAGE bool; + +-- bpchar datatype support new in 1.3. +CREATE FUNCTION gin_extract_value_bpchar(bpchar, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_compare_prefix_bpchar(bpchar, bpchar, int2, internal) +RETURNS int4 +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE FUNCTION gin_extract_query_bpchar(bpchar, internal, int2, internal, internal) +RETURNS internal +AS 'MODULE_PATHNAME' +LANGUAGE C STRICT IMMUTABLE; + +CREATE OPERATOR CLASS bpchar_ops +DEFAULT FOR TYPE bpchar USING gin +AS + OPERATOR 1 <, + OPERATOR 2 <=, + OPERATOR 3 =, + OPERATOR 4 >=, + OPERATOR 5 >, + FUNCTION 1 bpcharcmp(bpchar, bpchar), + FUNCTION 2 gin_extract_value_bpchar(bpchar, internal), + FUNCTION 3 gin_extract_query_bpchar(bpchar, internal, int2, internal, internal), + FUNCTION 4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), + FUNCTION 5 gin_compare_prefix_bpchar(bpchar,bpchar,int2, internal), +STORAGE bpchar; diff --git a/contrib/btree_gin/btree_gin.c b/contrib/btree_gin/btree_gin.c new file mode 100644 index 0000000..b09bb8d --- /dev/null +++ b/contrib/btree_gin/btree_gin.c @@ -0,0 +1,513 @@ +/* + * contrib/btree_gin/btree_gin.c + */ +#include "postgres.h" + +#include <limits.h> + +#include "access/stratnum.h" +#include "utils/builtins.h" +#include "utils/bytea.h" +#include "utils/cash.h" +#include "utils/date.h" +#include "utils/float.h" +#include "utils/inet.h" +#include "utils/numeric.h" +#include "utils/timestamp.h" +#include "utils/uuid.h" +#include "utils/varbit.h" + +PG_MODULE_MAGIC; + +typedef struct QueryInfo +{ + StrategyNumber strategy; + Datum datum; + bool is_varlena; + Datum (*typecmp) (FunctionCallInfo); +} QueryInfo; + +/*** GIN support functions shared by all datatypes ***/ + +static Datum +gin_btree_extract_value(FunctionCallInfo fcinfo, bool is_varlena) +{ + Datum datum = PG_GETARG_DATUM(0); + int32 *nentries = (int32 *) PG_GETARG_POINTER(1); + Datum *entries = (Datum *) palloc(sizeof(Datum)); + + if (is_varlena) + datum = PointerGetDatum(PG_DETOAST_DATUM(datum)); + entries[0] = datum; + *nentries = 1; + + PG_RETURN_POINTER(entries); +} + +/* + * For BTGreaterEqualStrategyNumber, BTGreaterStrategyNumber, and + * BTEqualStrategyNumber we want to start the index scan at the + * supplied query datum, and work forward. For BTLessStrategyNumber + * and BTLessEqualStrategyNumber, we need to start at the leftmost + * key, and work forward until the supplied query datum (which must be + * sent along inside the QueryInfo structure). + */ +static Datum +gin_btree_extract_query(FunctionCallInfo fcinfo, + bool is_varlena, + Datum (*leftmostvalue) (void), + Datum (*typecmp) (FunctionCallInfo)) +{ + Datum datum = PG_GETARG_DATUM(0); + int32 *nentries = (int32 *) PG_GETARG_POINTER(1); + StrategyNumber strategy = PG_GETARG_UINT16(2); + bool **partialmatch = (bool **) PG_GETARG_POINTER(3); + Pointer **extra_data = (Pointer **) PG_GETARG_POINTER(4); + Datum *entries = (Datum *) palloc(sizeof(Datum)); + QueryInfo *data = (QueryInfo *) palloc(sizeof(QueryInfo)); + bool *ptr_partialmatch; + + *nentries = 1; + ptr_partialmatch = *partialmatch = (bool *) palloc(sizeof(bool)); + *ptr_partialmatch = false; + if (is_varlena) + datum = PointerGetDatum(PG_DETOAST_DATUM(datum)); + data->strategy = strategy; + data->datum = datum; + data->is_varlena = is_varlena; + data->typecmp = typecmp; + *extra_data = (Pointer *) palloc(sizeof(Pointer)); + **extra_data = (Pointer) data; + + switch (strategy) + { + case BTLessStrategyNumber: + case BTLessEqualStrategyNumber: + entries[0] = leftmostvalue(); + *ptr_partialmatch = true; + break; + case BTGreaterEqualStrategyNumber: + case BTGreaterStrategyNumber: + *ptr_partialmatch = true; + /* FALLTHROUGH */ + case BTEqualStrategyNumber: + entries[0] = datum; + break; + default: + elog(ERROR, "unrecognized strategy number: %d", strategy); + } + + PG_RETURN_POINTER(entries); +} + +/* + * Datum a is a value from extract_query method and for BTLess* + * strategy it is a left-most value. So, use original datum from QueryInfo + * to decide to stop scanning or not. Datum b is always from index. + */ +static Datum +gin_btree_compare_prefix(FunctionCallInfo fcinfo) +{ + Datum a = PG_GETARG_DATUM(0); + Datum b = PG_GETARG_DATUM(1); + QueryInfo *data = (QueryInfo *) PG_GETARG_POINTER(3); + int32 res, + cmp; + + cmp = DatumGetInt32(CallerFInfoFunctionCall2(data->typecmp, + fcinfo->flinfo, + PG_GET_COLLATION(), + (data->strategy == BTLessStrategyNumber || + data->strategy == BTLessEqualStrategyNumber) + ? data->datum : a, + b)); + + switch (data->strategy) + { + case BTLessStrategyNumber: + /* If original datum > indexed one then return match */ + if (cmp > 0) + res = 0; + else + res = 1; + break; + case BTLessEqualStrategyNumber: + /* The same except equality */ + if (cmp >= 0) + res = 0; + else + res = 1; + break; + case BTEqualStrategyNumber: + if (cmp != 0) + res = 1; + else + res = 0; + break; + case BTGreaterEqualStrategyNumber: + /* If original datum <= indexed one then return match */ + if (cmp <= 0) + res = 0; + else + res = 1; + break; + case BTGreaterStrategyNumber: + /* If original datum <= indexed one then return match */ + /* If original datum == indexed one then continue scan */ + if (cmp < 0) + res = 0; + else if (cmp == 0) + res = -1; + else + res = 1; + break; + default: + elog(ERROR, "unrecognized strategy number: %d", + data->strategy); + res = 0; + } + + PG_RETURN_INT32(res); +} + +PG_FUNCTION_INFO_V1(gin_btree_consistent); +Datum +gin_btree_consistent(PG_FUNCTION_ARGS) +{ + bool *recheck = (bool *) PG_GETARG_POINTER(5); + + *recheck = false; + PG_RETURN_BOOL(true); +} + +/*** GIN_SUPPORT macro defines the datatype specific functions ***/ + +#define GIN_SUPPORT(type, is_varlena, leftmostvalue, typecmp) \ +PG_FUNCTION_INFO_V1(gin_extract_value_##type); \ +Datum \ +gin_extract_value_##type(PG_FUNCTION_ARGS) \ +{ \ + return gin_btree_extract_value(fcinfo, is_varlena); \ +} \ +PG_FUNCTION_INFO_V1(gin_extract_query_##type); \ +Datum \ +gin_extract_query_##type(PG_FUNCTION_ARGS) \ +{ \ + return gin_btree_extract_query(fcinfo, \ + is_varlena, leftmostvalue, typecmp); \ +} \ +PG_FUNCTION_INFO_V1(gin_compare_prefix_##type); \ +Datum \ +gin_compare_prefix_##type(PG_FUNCTION_ARGS) \ +{ \ + return gin_btree_compare_prefix(fcinfo); \ +} + + +/*** Datatype specifications ***/ + +static Datum +leftmostvalue_int2(void) +{ + return Int16GetDatum(SHRT_MIN); +} + +GIN_SUPPORT(int2, false, leftmostvalue_int2, btint2cmp) + +static Datum +leftmostvalue_int4(void) +{ + return Int32GetDatum(INT_MIN); +} + +GIN_SUPPORT(int4, false, leftmostvalue_int4, btint4cmp) + +static Datum +leftmostvalue_int8(void) +{ + return Int64GetDatum(PG_INT64_MIN); +} + +GIN_SUPPORT(int8, false, leftmostvalue_int8, btint8cmp) + +static Datum +leftmostvalue_float4(void) +{ + return Float4GetDatum(-get_float4_infinity()); +} + +GIN_SUPPORT(float4, false, leftmostvalue_float4, btfloat4cmp) + +static Datum +leftmostvalue_float8(void) +{ + return Float8GetDatum(-get_float8_infinity()); +} + +GIN_SUPPORT(float8, false, leftmostvalue_float8, btfloat8cmp) + +static Datum +leftmostvalue_money(void) +{ + return Int64GetDatum(PG_INT64_MIN); +} + +GIN_SUPPORT(money, false, leftmostvalue_money, cash_cmp) + +static Datum +leftmostvalue_oid(void) +{ + return ObjectIdGetDatum(0); +} + +GIN_SUPPORT(oid, false, leftmostvalue_oid, btoidcmp) + +static Datum +leftmostvalue_timestamp(void) +{ + return TimestampGetDatum(DT_NOBEGIN); +} + +GIN_SUPPORT(timestamp, false, leftmostvalue_timestamp, timestamp_cmp) + +GIN_SUPPORT(timestamptz, false, leftmostvalue_timestamp, timestamp_cmp) + +static Datum +leftmostvalue_time(void) +{ + return TimeADTGetDatum(0); +} + +GIN_SUPPORT(time, false, leftmostvalue_time, time_cmp) + +static Datum +leftmostvalue_timetz(void) +{ + TimeTzADT *v = palloc(sizeof(TimeTzADT)); + + v->time = 0; + v->zone = -24 * 3600; /* XXX is that true? */ + + return TimeTzADTPGetDatum(v); +} + +GIN_SUPPORT(timetz, false, leftmostvalue_timetz, timetz_cmp) + +static Datum +leftmostvalue_date(void) +{ + return DateADTGetDatum(DATEVAL_NOBEGIN); +} + +GIN_SUPPORT(date, false, leftmostvalue_date, date_cmp) + +static Datum +leftmostvalue_interval(void) +{ + Interval *v = palloc(sizeof(Interval)); + + v->time = PG_INT64_MIN; + v->day = PG_INT32_MIN; + v->month = PG_INT32_MIN; + return IntervalPGetDatum(v); +} + +GIN_SUPPORT(interval, false, leftmostvalue_interval, interval_cmp) + +static Datum +leftmostvalue_macaddr(void) +{ + macaddr *v = palloc0(sizeof(macaddr)); + + return MacaddrPGetDatum(v); +} + +GIN_SUPPORT(macaddr, false, leftmostvalue_macaddr, macaddr_cmp) + +static Datum +leftmostvalue_macaddr8(void) +{ + macaddr8 *v = palloc0(sizeof(macaddr8)); + + return Macaddr8PGetDatum(v); +} + +GIN_SUPPORT(macaddr8, false, leftmostvalue_macaddr8, macaddr8_cmp) + +static Datum +leftmostvalue_inet(void) +{ + return DirectFunctionCall1(inet_in, CStringGetDatum("0.0.0.0/0")); +} + +GIN_SUPPORT(inet, true, leftmostvalue_inet, network_cmp) + +GIN_SUPPORT(cidr, true, leftmostvalue_inet, network_cmp) + +static Datum +leftmostvalue_text(void) +{ + return PointerGetDatum(cstring_to_text_with_len("", 0)); +} + +GIN_SUPPORT(text, true, leftmostvalue_text, bttextcmp) + +GIN_SUPPORT(bpchar, true, leftmostvalue_text, bpcharcmp) + +static Datum +leftmostvalue_char(void) +{ + return CharGetDatum(0); +} + +GIN_SUPPORT(char, false, leftmostvalue_char, btcharcmp) + +GIN_SUPPORT(bytea, true, leftmostvalue_text, byteacmp) + +static Datum +leftmostvalue_bit(void) +{ + return DirectFunctionCall3(bit_in, + CStringGetDatum(""), + ObjectIdGetDatum(0), + Int32GetDatum(-1)); +} + +GIN_SUPPORT(bit, true, leftmostvalue_bit, bitcmp) + +static Datum +leftmostvalue_varbit(void) +{ + return DirectFunctionCall3(varbit_in, + CStringGetDatum(""), + ObjectIdGetDatum(0), + Int32GetDatum(-1)); +} + +GIN_SUPPORT(varbit, true, leftmostvalue_varbit, bitcmp) + +/* + * Numeric type hasn't a real left-most value, so we use PointerGetDatum(NULL) + * (*not* a SQL NULL) to represent that. We can get away with that because + * the value returned by our leftmostvalue function will never be stored in + * the index nor passed to anything except our compare and prefix-comparison + * functions. The same trick could be used for other pass-by-reference types. + */ + +#define NUMERIC_IS_LEFTMOST(x) ((x) == NULL) + +PG_FUNCTION_INFO_V1(gin_numeric_cmp); + +Datum +gin_numeric_cmp(PG_FUNCTION_ARGS) +{ + Numeric a = (Numeric) PG_GETARG_POINTER(0); + Numeric b = (Numeric) PG_GETARG_POINTER(1); + int res = 0; + + if (NUMERIC_IS_LEFTMOST(a)) + { + res = (NUMERIC_IS_LEFTMOST(b)) ? 0 : -1; + } + else if (NUMERIC_IS_LEFTMOST(b)) + { + res = 1; + } + else + { + res = DatumGetInt32(DirectFunctionCall2(numeric_cmp, + NumericGetDatum(a), + NumericGetDatum(b))); + } + + PG_RETURN_INT32(res); +} + +static Datum +leftmostvalue_numeric(void) +{ + return PointerGetDatum(NULL); +} + +GIN_SUPPORT(numeric, true, leftmostvalue_numeric, gin_numeric_cmp) + +/* + * Use a similar trick to that used for numeric for enums, since we don't + * actually know the leftmost value of any enum without knowing the concrete + * type, so we use a dummy leftmost value of InvalidOid. + * + * Note that we use CallerFInfoFunctionCall2 here so that enum_cmp + * gets a valid fn_extra to work with. Unlike most other type comparison + * routines it needs it, so we can't use DirectFunctionCall2. + */ + +#define ENUM_IS_LEFTMOST(x) ((x) == InvalidOid) + +PG_FUNCTION_INFO_V1(gin_enum_cmp); + +Datum +gin_enum_cmp(PG_FUNCTION_ARGS) +{ + Oid a = PG_GETARG_OID(0); + Oid b = PG_GETARG_OID(1); + int res = 0; + + if (ENUM_IS_LEFTMOST(a)) + { + res = (ENUM_IS_LEFTMOST(b)) ? 0 : -1; + } + else if (ENUM_IS_LEFTMOST(b)) + { + res = 1; + } + else + { + res = DatumGetInt32(CallerFInfoFunctionCall2(enum_cmp, + fcinfo->flinfo, + PG_GET_COLLATION(), + ObjectIdGetDatum(a), + ObjectIdGetDatum(b))); + } + + PG_RETURN_INT32(res); +} + +static Datum +leftmostvalue_enum(void) +{ + return ObjectIdGetDatum(InvalidOid); +} + +GIN_SUPPORT(anyenum, false, leftmostvalue_enum, gin_enum_cmp) + +static Datum +leftmostvalue_uuid(void) +{ + /* + * palloc0 will create the UUID with all zeroes: + * "00000000-0000-0000-0000-000000000000" + */ + pg_uuid_t *retval = (pg_uuid_t *) palloc0(sizeof(pg_uuid_t)); + + return UUIDPGetDatum(retval); +} + +GIN_SUPPORT(uuid, false, leftmostvalue_uuid, uuid_cmp) + +static Datum +leftmostvalue_name(void) +{ + NameData *result = (NameData *) palloc0(NAMEDATALEN); + + return NameGetDatum(result); +} + +GIN_SUPPORT(name, false, leftmostvalue_name, btnamecmp) + +static Datum +leftmostvalue_bool(void) +{ + return BoolGetDatum(false); +} + +GIN_SUPPORT(bool, false, leftmostvalue_bool, btboolcmp) diff --git a/contrib/btree_gin/btree_gin.control b/contrib/btree_gin/btree_gin.control new file mode 100644 index 0000000..67d0c99 --- /dev/null +++ b/contrib/btree_gin/btree_gin.control @@ -0,0 +1,6 @@ +# btree_gin extension +comment = 'support for indexing common datatypes in GIN' +default_version = '1.3' +module_pathname = '$libdir/btree_gin' +relocatable = true +trusted = true diff --git a/contrib/btree_gin/expected/bit.out b/contrib/btree_gin/expected/bit.out new file mode 100644 index 0000000..3c00a20 --- /dev/null +++ b/contrib/btree_gin/expected/bit.out @@ -0,0 +1,44 @@ +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; + i +----- + 001 + 010 + 011 +(3 rows) + +SELECT * FROM test_bit WHERE i<='100'::bit(3) ORDER BY i; + i +----- + 001 + 010 + 011 + 100 +(4 rows) + +SELECT * FROM test_bit WHERE i='100'::bit(3) ORDER BY i; + i +----- + 100 +(1 row) + +SELECT * FROM test_bit WHERE i>='100'::bit(3) ORDER BY i; + i +----- + 100 + 101 + 110 +(3 rows) + +SELECT * FROM test_bit WHERE i>'100'::bit(3) ORDER BY i; + i +----- + 101 + 110 +(2 rows) + diff --git a/contrib/btree_gin/expected/bool.out b/contrib/btree_gin/expected/bool.out new file mode 100644 index 0000000..efb3e1e --- /dev/null +++ b/contrib/btree_gin/expected/bool.out @@ -0,0 +1,119 @@ +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; + i +--- + f +(1 row) + +SELECT * FROM test_bool WHERE i<=true ORDER BY i; + i +--- + f + t +(2 rows) + +SELECT * FROM test_bool WHERE i=true ORDER BY i; + i +--- + t +(1 row) + +SELECT * FROM test_bool WHERE i>=true ORDER BY i; + i +--- + t +(1 row) + +SELECT * FROM test_bool WHERE i>true ORDER BY i; + i +--- +(0 rows) + +SELECT * FROM test_bool WHERE i<false ORDER BY i; + i +--- +(0 rows) + +SELECT * FROM test_bool WHERE i<=false ORDER BY i; + i +--- + f +(1 row) + +SELECT * FROM test_bool WHERE i=false ORDER BY i; + i +--- + f +(1 row) + +SELECT * FROM test_bool WHERE i>=false ORDER BY i; + i +--- + f + t +(2 rows) + +SELECT * FROM test_bool WHERE i>false ORDER BY i; + i +--- + t +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM test_bool WHERE i<true ORDER BY i; + QUERY PLAN +------------------------------------------- + Sort + Sort Key: i + -> Bitmap Heap Scan on test_bool + Recheck Cond: (i < true) + -> Bitmap Index Scan on idx_bool + Index Cond: (i < true) +(6 rows) + +EXPLAIN (COSTS OFF) SELECT * FROM test_bool WHERE i<=true ORDER BY i; + QUERY PLAN +------------------------------------------- + Sort + Sort Key: i + -> Bitmap Heap Scan on test_bool + Recheck Cond: (i <= true) + -> Bitmap Index Scan on idx_bool + Index Cond: (i <= true) +(6 rows) + +EXPLAIN (COSTS OFF) SELECT * FROM test_bool WHERE i=true ORDER BY i; + QUERY PLAN +----------------------------- + Sort + Sort Key: i + -> Seq Scan on test_bool + Filter: i +(4 rows) + +EXPLAIN (COSTS OFF) SELECT * FROM test_bool WHERE i>=true ORDER BY i; + QUERY PLAN +------------------------------------------- + Sort + Sort Key: i + -> Bitmap Heap Scan on test_bool + Recheck Cond: (i >= true) + -> Bitmap Index Scan on idx_bool + Index Cond: (i >= true) +(6 rows) + +EXPLAIN (COSTS OFF) SELECT * FROM test_bool WHERE i>true ORDER BY i; + QUERY PLAN +------------------------------------------- + Sort + Sort Key: i + -> Bitmap Heap Scan on test_bool + Recheck Cond: (i > true) + -> Bitmap Index Scan on idx_bool + Index Cond: (i > true) +(6 rows) + diff --git a/contrib/btree_gin/expected/bpchar.out b/contrib/btree_gin/expected/bpchar.out new file mode 100644 index 0000000..2eb8855 --- /dev/null +++ b/contrib/btree_gin/expected/bpchar.out @@ -0,0 +1,109 @@ +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; + i +------------ + a + ab + abb +(3 rows) + +SELECT * FROM test_bpchar WHERE i<='abc' ORDER BY i; + i +------------ + a + ab + abb + abc + abc +(5 rows) + +SELECT * FROM test_bpchar WHERE i='abc' ORDER BY i; + i +------------ + abc + abc +(2 rows) + +SELECT * FROM test_bpchar WHERE i='abc ' ORDER BY i; + i +------------ + abc + abc +(2 rows) + +SELECT * FROM test_bpchar WHERE i>='abc' ORDER BY i; + i +------------ + abc + abc + axy + xyz + xyz +(5 rows) + +SELECT * FROM test_bpchar WHERE i>'abc' ORDER BY i; + i +------------ + axy + xyz + xyz +(3 rows) + +EXPLAIN (COSTS OFF) SELECT * FROM test_bpchar WHERE i<'abc' ORDER BY i; + QUERY PLAN +----------------------------------------------- + Sort + Sort Key: i + -> Bitmap Heap Scan on test_bpchar + Recheck Cond: (i < 'abc'::bpchar) + -> Bitmap Index Scan on idx_bpchar + Index Cond: (i < 'abc'::bpchar) +(6 rows) + +EXPLAIN (COSTS OFF) SELECT * FROM test_bpchar WHERE i<='abc' ORDER BY i; + QUERY PLAN +------------------------------------------------ + Sort + Sort Key: i + -> Bitmap Heap Scan on test_bpchar + Recheck Cond: (i <= 'abc'::bpchar) + -> Bitmap Index Scan on idx_bpchar + Index Cond: (i <= 'abc'::bpchar) +(6 rows) + +EXPLAIN (COSTS OFF) SELECT * FROM test_bpchar WHERE i='abc' ORDER BY i; + QUERY PLAN +----------------------------------------- + Bitmap Heap Scan on test_bpchar + Recheck Cond: (i = 'abc'::bpchar) + -> Bitmap Index Scan on idx_bpchar + Index Cond: (i = 'abc'::bpchar) +(4 rows) + +EXPLAIN (COSTS OFF) SELECT * FROM test_bpchar WHERE i>='abc' ORDER BY i; + QUERY PLAN +------------------------------------------------ + Sort + Sort Key: i + -> Bitmap Heap Scan on test_bpchar + Recheck Cond: (i >= 'abc'::bpchar) + -> Bitmap Index Scan on idx_bpchar + Index Cond: (i >= 'abc'::bpchar) +(6 rows) + +EXPLAIN (COSTS OFF) SELECT * FROM test_bpchar WHERE i>'abc' ORDER BY i; + QUERY PLAN +----------------------------------------------- + Sort + Sort Key: i + -> Bitmap Heap Scan on test_bpchar + Recheck Cond: (i > 'abc'::bpchar) + -> Bitmap Index Scan on idx_bpchar + Index Cond: (i > 'abc'::bpchar) +(6 rows) + diff --git a/contrib/btree_gin/expected/bytea.out b/contrib/btree_gin/expected/bytea.out new file mode 100644 index 0000000..b0ed7a5 --- /dev/null +++ b/contrib/btree_gin/expected/bytea.out @@ -0,0 +1,46 @@ +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; + i +----- + a + ab + abb +(3 rows) + +SELECT * FROM test_bytea WHERE i<='abc'::bytea ORDER BY i; + i +----- + a + ab + abb + abc +(4 rows) + +SELECT * FROM test_bytea WHERE i='abc'::bytea ORDER BY i; + i +----- + abc +(1 row) + +SELECT * FROM test_bytea WHERE i>='abc'::bytea ORDER BY i; + i +----- + abc + axy + xyz +(3 rows) + +SELECT * FROM test_bytea WHERE i>'abc'::bytea ORDER BY i; + i +----- + axy + xyz +(2 rows) + diff --git a/contrib/btree_gin/expected/char.out b/contrib/btree_gin/expected/char.out new file mode 100644 index 0000000..6563546 --- /dev/null +++ b/contrib/btree_gin/expected/char.out @@ -0,0 +1,44 @@ +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; + i +--- + a + b + c +(3 rows) + +SELECT * FROM test_char WHERE i<='d'::"char" ORDER BY i; + i +--- + a + b + c + d +(4 rows) + +SELECT * FROM test_char WHERE i='d'::"char" ORDER BY i; + i +--- + d +(1 row) + +SELECT * FROM test_char WHERE i>='d'::"char" ORDER BY i; + i +--- + d + e + f +(3 rows) + +SELECT * FROM test_char WHERE i>'d'::"char" ORDER BY i; + i +--- + e + f +(2 rows) + diff --git a/contrib/btree_gin/expected/cidr.out b/contrib/btree_gin/expected/cidr.out new file mode 100644 index 0000000..3d1198a --- /dev/null +++ b/contrib/btree_gin/expected/cidr.out @@ -0,0 +1,51 @@ +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; + i +------------ + 1.2.3.4/32 + 1.2.4.4/32 + 1.2.5.4/32 +(3 rows) + +SELECT * FROM test_cidr WHERE i<='1.2.6.4'::cidr ORDER BY i; + i +------------ + 1.2.3.4/32 + 1.2.4.4/32 + 1.2.5.4/32 + 1.2.6.4/32 +(4 rows) + +SELECT * FROM test_cidr WHERE i='1.2.6.4'::cidr ORDER BY i; + i +------------ + 1.2.6.4/32 +(1 row) + +SELECT * FROM test_cidr WHERE i>='1.2.6.4'::cidr ORDER BY i; + i +------------ + 1.2.6.4/32 + 1.2.7.4/32 + 1.2.8.4/32 +(3 rows) + +SELECT * FROM test_cidr WHERE i>'1.2.6.4'::cidr ORDER BY i; + i +------------ + 1.2.7.4/32 + 1.2.8.4/32 +(2 rows) + diff --git a/contrib/btree_gin/expected/date.out b/contrib/btree_gin/expected/date.out new file mode 100644 index 0000000..40dfa30 --- /dev/null +++ b/contrib/btree_gin/expected/date.out @@ -0,0 +1,51 @@ +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; + i +------------ + 10-23-2004 + 10-24-2004 + 10-25-2004 +(3 rows) + +SELECT * FROM test_date WHERE i<='2004-10-26'::date ORDER BY i; + i +------------ + 10-23-2004 + 10-24-2004 + 10-25-2004 + 10-26-2004 +(4 rows) + +SELECT * FROM test_date WHERE i='2004-10-26'::date ORDER BY i; + i +------------ + 10-26-2004 +(1 row) + +SELECT * FROM test_date WHERE i>='2004-10-26'::date ORDER BY i; + i +------------ + 10-26-2004 + 10-27-2004 + 10-28-2004 +(3 rows) + +SELECT * FROM test_date WHERE i>'2004-10-26'::date ORDER BY i; + i +------------ + 10-27-2004 + 10-28-2004 +(2 rows) + diff --git a/contrib/btree_gin/expected/enum.out b/contrib/btree_gin/expected/enum.out new file mode 100644 index 0000000..71e0c4b --- /dev/null +++ b/contrib/btree_gin/expected/enum.out @@ -0,0 +1,63 @@ +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; + i +--- + r + o + y +(3 rows) + +SELECT * FROM test_enum WHERE i<='g'::rainbow ORDER BY i; + i +--- + r + o + y + g +(4 rows) + +SELECT * FROM test_enum WHERE i='g'::rainbow ORDER BY i; + i +--- + g +(1 row) + +SELECT * FROM test_enum WHERE i>='g'::rainbow ORDER BY i; + i +--- + g + b + i + v +(4 rows) + +SELECT * FROM test_enum WHERE i>'g'::rainbow ORDER BY i; + i +--- + b + i + v +(3 rows) + +explain (costs off) SELECT * FROM test_enum WHERE i>='g'::rainbow ORDER BY i; + QUERY PLAN +----------------------------------------------- + Sort + Sort Key: i + -> Bitmap Heap Scan on test_enum + Recheck Cond: (i >= 'g'::rainbow) + -> Bitmap Index Scan on idx_enum + Index Cond: (i >= 'g'::rainbow) +(6 rows) + +-- 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/expected/float4.out b/contrib/btree_gin/expected/float4.out new file mode 100644 index 0000000..7b9134f --- /dev/null +++ b/contrib/btree_gin/expected/float4.out @@ -0,0 +1,44 @@ +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; + i +---- + -2 + -1 + 0 +(3 rows) + +SELECT * FROM test_float4 WHERE i<=1::float4 ORDER BY i; + i +---- + -2 + -1 + 0 + 1 +(4 rows) + +SELECT * FROM test_float4 WHERE i=1::float4 ORDER BY i; + i +--- + 1 +(1 row) + +SELECT * FROM test_float4 WHERE i>=1::float4 ORDER BY i; + i +--- + 1 + 2 + 3 +(3 rows) + +SELECT * FROM test_float4 WHERE i>1::float4 ORDER BY i; + i +--- + 2 + 3 +(2 rows) + diff --git a/contrib/btree_gin/expected/float8.out b/contrib/btree_gin/expected/float8.out new file mode 100644 index 0000000..a41d4f9 --- /dev/null +++ b/contrib/btree_gin/expected/float8.out @@ -0,0 +1,44 @@ +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; + i +---- + -2 + -1 + 0 +(3 rows) + +SELECT * FROM test_float8 WHERE i<=1::float8 ORDER BY i; + i +---- + -2 + -1 + 0 + 1 +(4 rows) + +SELECT * FROM test_float8 WHERE i=1::float8 ORDER BY i; + i +--- + 1 +(1 row) + +SELECT * FROM test_float8 WHERE i>=1::float8 ORDER BY i; + i +--- + 1 + 2 + 3 +(3 rows) + +SELECT * FROM test_float8 WHERE i>1::float8 ORDER BY i; + i +--- + 2 + 3 +(2 rows) + diff --git a/contrib/btree_gin/expected/inet.out b/contrib/btree_gin/expected/inet.out new file mode 100644 index 0000000..aa6147f --- /dev/null +++ b/contrib/btree_gin/expected/inet.out @@ -0,0 +1,51 @@ +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; + i +------------ + 1.2.3.4/16 + 1.2.4.4/16 + 1.2.5.4/16 +(3 rows) + +SELECT * FROM test_inet WHERE i<='1.2.6.4/16'::inet ORDER BY i; + i +------------ + 1.2.3.4/16 + 1.2.4.4/16 + 1.2.5.4/16 + 1.2.6.4/16 +(4 rows) + +SELECT * FROM test_inet WHERE i='1.2.6.4/16'::inet ORDER BY i; + i +------------ + 1.2.6.4/16 +(1 row) + +SELECT * FROM test_inet WHERE i>='1.2.6.4/16'::inet ORDER BY i; + i +------------ + 1.2.6.4/16 + 1.2.7.4/16 + 1.2.8.4/16 +(3 rows) + +SELECT * FROM test_inet WHERE i>'1.2.6.4/16'::inet ORDER BY i; + i +------------ + 1.2.7.4/16 + 1.2.8.4/16 +(2 rows) + diff --git a/contrib/btree_gin/expected/install_btree_gin.out b/contrib/btree_gin/expected/install_btree_gin.out new file mode 100644 index 0000000..631a0df --- /dev/null +++ b/contrib/btree_gin/expected/install_btree_gin.out @@ -0,0 +1,9 @@ +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); + amname | opcname +--------+--------- +(0 rows) + diff --git a/contrib/btree_gin/expected/int2.out b/contrib/btree_gin/expected/int2.out new file mode 100644 index 0000000..20d66a1 --- /dev/null +++ b/contrib/btree_gin/expected/int2.out @@ -0,0 +1,44 @@ +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; + i +---- + -2 + -1 + 0 +(3 rows) + +SELECT * FROM test_int2 WHERE i<=1::int2 ORDER BY i; + i +---- + -2 + -1 + 0 + 1 +(4 rows) + +SELECT * FROM test_int2 WHERE i=1::int2 ORDER BY i; + i +--- + 1 +(1 row) + +SELECT * FROM test_int2 WHERE i>=1::int2 ORDER BY i; + i +--- + 1 + 2 + 3 +(3 rows) + +SELECT * FROM test_int2 WHERE i>1::int2 ORDER BY i; + i +--- + 2 + 3 +(2 rows) + diff --git a/contrib/btree_gin/expected/int4.out b/contrib/btree_gin/expected/int4.out new file mode 100644 index 0000000..0f0122c --- /dev/null +++ b/contrib/btree_gin/expected/int4.out @@ -0,0 +1,44 @@ +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; + i +---- + -2 + -1 + 0 +(3 rows) + +SELECT * FROM test_int4 WHERE i<=1::int4 ORDER BY i; + i +---- + -2 + -1 + 0 + 1 +(4 rows) + +SELECT * FROM test_int4 WHERE i=1::int4 ORDER BY i; + i +--- + 1 +(1 row) + +SELECT * FROM test_int4 WHERE i>=1::int4 ORDER BY i; + i +--- + 1 + 2 + 3 +(3 rows) + +SELECT * FROM test_int4 WHERE i>1::int4 ORDER BY i; + i +--- + 2 + 3 +(2 rows) + diff --git a/contrib/btree_gin/expected/int8.out b/contrib/btree_gin/expected/int8.out new file mode 100644 index 0000000..307e19e --- /dev/null +++ b/contrib/btree_gin/expected/int8.out @@ -0,0 +1,44 @@ +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; + i +---- + -2 + -1 + 0 +(3 rows) + +SELECT * FROM test_int8 WHERE i<=1::int8 ORDER BY i; + i +---- + -2 + -1 + 0 + 1 +(4 rows) + +SELECT * FROM test_int8 WHERE i=1::int8 ORDER BY i; + i +--- + 1 +(1 row) + +SELECT * FROM test_int8 WHERE i>=1::int8 ORDER BY i; + i +--- + 1 + 2 + 3 +(3 rows) + +SELECT * FROM test_int8 WHERE i>1::int8 ORDER BY i; + i +--- + 2 + 3 +(2 rows) + diff --git a/contrib/btree_gin/expected/interval.out b/contrib/btree_gin/expected/interval.out new file mode 100644 index 0000000..8bb9806 --- /dev/null +++ b/contrib/btree_gin/expected/interval.out @@ -0,0 +1,57 @@ +set enable_seqscan=off; +CREATE TABLE test_interval ( + i interval +); +INSERT INTO test_interval VALUES + ( '-178000000 years' ), + ( '03:55:08' ), + ( '04:55:08' ), + ( '05:55:08' ), + ( '08:55:08' ), + ( '09:55:08' ), + ( '10:55:08' ), + ( '178000000 years' ) +; +CREATE INDEX idx_interval ON test_interval USING gin (i); +SELECT * FROM test_interval WHERE i<'08:55:08'::interval ORDER BY i; + i +-------------------------- + @ 178000000 years ago + @ 3 hours 55 mins 8 secs + @ 4 hours 55 mins 8 secs + @ 5 hours 55 mins 8 secs +(4 rows) + +SELECT * FROM test_interval WHERE i<='08:55:08'::interval ORDER BY i; + i +-------------------------- + @ 178000000 years ago + @ 3 hours 55 mins 8 secs + @ 4 hours 55 mins 8 secs + @ 5 hours 55 mins 8 secs + @ 8 hours 55 mins 8 secs +(5 rows) + +SELECT * FROM test_interval WHERE i='08:55:08'::interval ORDER BY i; + i +-------------------------- + @ 8 hours 55 mins 8 secs +(1 row) + +SELECT * FROM test_interval WHERE i>='08:55:08'::interval ORDER BY i; + i +--------------------------- + @ 8 hours 55 mins 8 secs + @ 9 hours 55 mins 8 secs + @ 10 hours 55 mins 8 secs + @ 178000000 years +(4 rows) + +SELECT * FROM test_interval WHERE i>'08:55:08'::interval ORDER BY i; + i +--------------------------- + @ 9 hours 55 mins 8 secs + @ 10 hours 55 mins 8 secs + @ 178000000 years +(3 rows) + diff --git a/contrib/btree_gin/expected/macaddr.out b/contrib/btree_gin/expected/macaddr.out new file mode 100644 index 0000000..ebceb01 --- /dev/null +++ b/contrib/btree_gin/expected/macaddr.out @@ -0,0 +1,51 @@ +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; + i +------------------- + 22:00:5c:03:55:08 + 22:00:5c:04:55:08 + 22:00:5c:05:55:08 +(3 rows) + +SELECT * FROM test_macaddr WHERE i<='22:00:5c:08:55:08'::macaddr ORDER BY i; + i +------------------- + 22:00:5c:03:55:08 + 22:00:5c:04:55:08 + 22:00:5c:05:55:08 + 22:00:5c:08:55:08 +(4 rows) + +SELECT * FROM test_macaddr WHERE i='22:00:5c:08:55:08'::macaddr ORDER BY i; + i +------------------- + 22:00:5c:08:55:08 +(1 row) + +SELECT * FROM test_macaddr WHERE i>='22:00:5c:08:55:08'::macaddr ORDER BY i; + i +------------------- + 22:00:5c:08:55:08 + 22:00:5c:09:55:08 + 22:00:5c:10:55:08 +(3 rows) + +SELECT * FROM test_macaddr WHERE i>'22:00:5c:08:55:08'::macaddr ORDER BY i; + i +------------------- + 22:00:5c:09:55:08 + 22:00:5c:10:55:08 +(2 rows) + diff --git a/contrib/btree_gin/expected/macaddr8.out b/contrib/btree_gin/expected/macaddr8.out new file mode 100644 index 0000000..025b0c1 --- /dev/null +++ b/contrib/btree_gin/expected/macaddr8.out @@ -0,0 +1,51 @@ +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; + i +------------------------- + 22:00:5c:03:55:08:01:02 + 22:00:5c:04:55:08:01:02 + 22:00:5c:05:55:08:01:02 +(3 rows) + +SELECT * FROM test_macaddr8 WHERE i<='22:00:5c:08:55:08:01:02'::macaddr8 ORDER BY i; + i +------------------------- + 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 +(4 rows) + +SELECT * FROM test_macaddr8 WHERE i='22:00:5c:08:55:08:01:02'::macaddr8 ORDER BY i; + i +------------------------- + 22:00:5c:08:55:08:01:02 +(1 row) + +SELECT * FROM test_macaddr8 WHERE i>='22:00:5c:08:55:08:01:02'::macaddr8 ORDER BY i; + i +------------------------- + 22:00:5c:08:55:08:01:02 + 22:00:5c:09:55:08:01:02 + 22:00:5c:10:55:08:01:02 +(3 rows) + +SELECT * FROM test_macaddr8 WHERE i>'22:00:5c:08:55:08:01:02'::macaddr8 ORDER BY i; + i +------------------------- + 22:00:5c:09:55:08:01:02 + 22:00:5c:10:55:08:01:02 +(2 rows) + diff --git a/contrib/btree_gin/expected/money.out b/contrib/btree_gin/expected/money.out new file mode 100644 index 0000000..a0ba571 --- /dev/null +++ b/contrib/btree_gin/expected/money.out @@ -0,0 +1,44 @@ +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; + i +-------- + -$2.00 + -$1.00 + $0.00 +(3 rows) + +SELECT * FROM test_money WHERE i<='1'::money ORDER BY i; + i +-------- + -$2.00 + -$1.00 + $0.00 + $1.00 +(4 rows) + +SELECT * FROM test_money WHERE i='1'::money ORDER BY i; + i +------- + $1.00 +(1 row) + +SELECT * FROM test_money WHERE i>='1'::money ORDER BY i; + i +------- + $1.00 + $2.00 + $3.00 +(3 rows) + +SELECT * FROM test_money WHERE i>'1'::money ORDER BY i; + i +------- + $2.00 + $3.00 +(2 rows) + diff --git a/contrib/btree_gin/expected/name.out b/contrib/btree_gin/expected/name.out new file mode 100644 index 0000000..174de65 --- /dev/null +++ b/contrib/btree_gin/expected/name.out @@ -0,0 +1,97 @@ +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; + i +----- + a + ab + abb +(3 rows) + +SELECT * FROM test_name WHERE i<='abc' ORDER BY i; + i +----- + a + ab + abb + abc +(4 rows) + +SELECT * FROM test_name WHERE i='abc' ORDER BY i; + i +----- + abc +(1 row) + +SELECT * FROM test_name WHERE i>='abc' ORDER BY i; + i +----- + abc + axy + xyz +(3 rows) + +SELECT * FROM test_name WHERE i>'abc' ORDER BY i; + i +----- + axy + xyz +(2 rows) + +EXPLAIN (COSTS OFF) SELECT * FROM test_name WHERE i<'abc' ORDER BY i; + QUERY PLAN +--------------------------------------------- + Sort + Sort Key: i + -> Bitmap Heap Scan on test_name + Recheck Cond: (i < 'abc'::name) + -> Bitmap Index Scan on idx_name + Index Cond: (i < 'abc'::name) +(6 rows) + +EXPLAIN (COSTS OFF) SELECT * FROM test_name WHERE i<='abc' ORDER BY i; + QUERY PLAN +---------------------------------------------- + Sort + Sort Key: i + -> Bitmap Heap Scan on test_name + Recheck Cond: (i <= 'abc'::name) + -> Bitmap Index Scan on idx_name + Index Cond: (i <= 'abc'::name) +(6 rows) + +EXPLAIN (COSTS OFF) SELECT * FROM test_name WHERE i='abc' ORDER BY i; + QUERY PLAN +--------------------------------------- + Bitmap Heap Scan on test_name + Recheck Cond: (i = 'abc'::name) + -> Bitmap Index Scan on idx_name + Index Cond: (i = 'abc'::name) +(4 rows) + +EXPLAIN (COSTS OFF) SELECT * FROM test_name WHERE i>='abc' ORDER BY i; + QUERY PLAN +---------------------------------------------- + Sort + Sort Key: i + -> Bitmap Heap Scan on test_name + Recheck Cond: (i >= 'abc'::name) + -> Bitmap Index Scan on idx_name + Index Cond: (i >= 'abc'::name) +(6 rows) + +EXPLAIN (COSTS OFF) SELECT * FROM test_name WHERE i>'abc' ORDER BY i; + QUERY PLAN +--------------------------------------------- + Sort + Sort Key: i + -> Bitmap Heap Scan on test_name + Recheck Cond: (i > 'abc'::name) + -> Bitmap Index Scan on idx_name + Index Cond: (i > 'abc'::name) +(6 rows) + diff --git a/contrib/btree_gin/expected/numeric.out b/contrib/btree_gin/expected/numeric.out new file mode 100644 index 0000000..f10a672 --- /dev/null +++ b/contrib/btree_gin/expected/numeric.out @@ -0,0 +1,44 @@ +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; + i +---- + -2 + -1 + 0 +(3 rows) + +SELECT * FROM test_numeric WHERE i<='1'::numeric ORDER BY i; + i +---- + -2 + -1 + 0 + 1 +(4 rows) + +SELECT * FROM test_numeric WHERE i='1'::numeric ORDER BY i; + i +--- + 1 +(1 row) + +SELECT * FROM test_numeric WHERE i>='1'::numeric ORDER BY i; + i +--- + 1 + 2 + 3 +(3 rows) + +SELECT * FROM test_numeric WHERE i>'1'::numeric ORDER BY i; + i +--- + 2 + 3 +(2 rows) + diff --git a/contrib/btree_gin/expected/oid.out b/contrib/btree_gin/expected/oid.out new file mode 100644 index 0000000..19e15c7 --- /dev/null +++ b/contrib/btree_gin/expected/oid.out @@ -0,0 +1,44 @@ +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; + i +--- + 0 + 1 + 2 +(3 rows) + +SELECT * FROM test_oid WHERE i<=3::oid ORDER BY i; + i +--- + 0 + 1 + 2 + 3 +(4 rows) + +SELECT * FROM test_oid WHERE i=3::oid ORDER BY i; + i +--- + 3 +(1 row) + +SELECT * FROM test_oid WHERE i>=3::oid ORDER BY i; + i +--- + 3 + 4 + 5 +(3 rows) + +SELECT * FROM test_oid WHERE i>3::oid ORDER BY i; + i +--- + 4 + 5 +(2 rows) + diff --git a/contrib/btree_gin/expected/text.out b/contrib/btree_gin/expected/text.out new file mode 100644 index 0000000..3e31ad7 --- /dev/null +++ b/contrib/btree_gin/expected/text.out @@ -0,0 +1,44 @@ +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; + i +----- + a + ab + abb +(3 rows) + +SELECT * FROM test_text WHERE i<='abc' ORDER BY i; + i +----- + a + ab + abb + abc +(4 rows) + +SELECT * FROM test_text WHERE i='abc' ORDER BY i; + i +----- + abc +(1 row) + +SELECT * FROM test_text WHERE i>='abc' ORDER BY i; + i +----- + abc + axy + xyz +(3 rows) + +SELECT * FROM test_text WHERE i>'abc' ORDER BY i; + i +----- + axy + xyz +(2 rows) + diff --git a/contrib/btree_gin/expected/time.out b/contrib/btree_gin/expected/time.out new file mode 100644 index 0000000..be6b084 --- /dev/null +++ b/contrib/btree_gin/expected/time.out @@ -0,0 +1,51 @@ +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; + i +---------- + 03:55:08 + 04:55:08 + 05:55:08 +(3 rows) + +SELECT * FROM test_time WHERE i<='08:55:08'::time ORDER BY i; + i +---------- + 03:55:08 + 04:55:08 + 05:55:08 + 08:55:08 +(4 rows) + +SELECT * FROM test_time WHERE i='08:55:08'::time ORDER BY i; + i +---------- + 08:55:08 +(1 row) + +SELECT * FROM test_time WHERE i>='08:55:08'::time ORDER BY i; + i +---------- + 08:55:08 + 09:55:08 + 10:55:08 +(3 rows) + +SELECT * FROM test_time WHERE i>'08:55:08'::time ORDER BY i; + i +---------- + 09:55:08 + 10:55:08 +(2 rows) + diff --git a/contrib/btree_gin/expected/timestamp.out b/contrib/btree_gin/expected/timestamp.out new file mode 100644 index 0000000..a236cdc --- /dev/null +++ b/contrib/btree_gin/expected/timestamp.out @@ -0,0 +1,51 @@ +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; + i +-------------------------- + Tue Oct 26 03:55:08 2004 + Tue Oct 26 04:55:08 2004 + Tue Oct 26 05:55:08 2004 +(3 rows) + +SELECT * FROM test_timestamp WHERE i<='2004-10-26 08:55:08'::timestamp ORDER BY i; + i +-------------------------- + Tue Oct 26 03:55:08 2004 + Tue Oct 26 04:55:08 2004 + Tue Oct 26 05:55:08 2004 + Tue Oct 26 08:55:08 2004 +(4 rows) + +SELECT * FROM test_timestamp WHERE i='2004-10-26 08:55:08'::timestamp ORDER BY i; + i +-------------------------- + Tue Oct 26 08:55:08 2004 +(1 row) + +SELECT * FROM test_timestamp WHERE i>='2004-10-26 08:55:08'::timestamp ORDER BY i; + i +-------------------------- + Tue Oct 26 08:55:08 2004 + Tue Oct 26 09:55:08 2004 + Tue Oct 26 10:55:08 2004 +(3 rows) + +SELECT * FROM test_timestamp WHERE i>'2004-10-26 08:55:08'::timestamp ORDER BY i; + i +-------------------------- + Tue Oct 26 09:55:08 2004 + Tue Oct 26 10:55:08 2004 +(2 rows) + diff --git a/contrib/btree_gin/expected/timestamptz.out b/contrib/btree_gin/expected/timestamptz.out new file mode 100644 index 0000000..d53963d --- /dev/null +++ b/contrib/btree_gin/expected/timestamptz.out @@ -0,0 +1,51 @@ +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; + i +------------------------------ + Tue Oct 26 03:55:08 2004 PDT + Tue Oct 26 04:55:08 2004 PDT + Tue Oct 26 05:55:08 2004 PDT +(3 rows) + +SELECT * FROM test_timestamptz WHERE i<='2004-10-26 08:55:08'::timestamptz ORDER BY i; + i +------------------------------ + Tue Oct 26 03:55:08 2004 PDT + Tue Oct 26 04:55:08 2004 PDT + Tue Oct 26 05:55:08 2004 PDT + Tue Oct 26 08:55:08 2004 PDT +(4 rows) + +SELECT * FROM test_timestamptz WHERE i='2004-10-26 08:55:08'::timestamptz ORDER BY i; + i +------------------------------ + Tue Oct 26 08:55:08 2004 PDT +(1 row) + +SELECT * FROM test_timestamptz WHERE i>='2004-10-26 08:55:08'::timestamptz ORDER BY i; + i +------------------------------ + Tue Oct 26 08:55:08 2004 PDT + Tue Oct 26 09:55:08 2004 PDT + Tue Oct 26 10:55:08 2004 PDT +(3 rows) + +SELECT * FROM test_timestamptz WHERE i>'2004-10-26 08:55:08'::timestamptz ORDER BY i; + i +------------------------------ + Tue Oct 26 09:55:08 2004 PDT + Tue Oct 26 10:55:08 2004 PDT +(2 rows) + diff --git a/contrib/btree_gin/expected/timetz.out b/contrib/btree_gin/expected/timetz.out new file mode 100644 index 0000000..45aee71 --- /dev/null +++ b/contrib/btree_gin/expected/timetz.out @@ -0,0 +1,51 @@ +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; + i +------------- + 03:55:08-02 + 04:55:08-02 + 05:55:08-02 +(3 rows) + +SELECT * FROM test_timetz WHERE i<='08:55:08 GMT+2'::timetz ORDER BY i; + i +------------- + 03:55:08-02 + 04:55:08-02 + 05:55:08-02 + 08:55:08-02 +(4 rows) + +SELECT * FROM test_timetz WHERE i='08:55:08 GMT+2'::timetz ORDER BY i; + i +------------- + 08:55:08-02 +(1 row) + +SELECT * FROM test_timetz WHERE i>='08:55:08 GMT+2'::timetz ORDER BY i; + i +------------- + 08:55:08-02 + 09:55:08-02 + 10:55:08-02 +(3 rows) + +SELECT * FROM test_timetz WHERE i>'08:55:08 GMT+2'::timetz ORDER BY i; + i +------------- + 09:55:08-02 + 10:55:08-02 +(2 rows) + diff --git a/contrib/btree_gin/expected/uuid.out b/contrib/btree_gin/expected/uuid.out new file mode 100644 index 0000000..60fd8d6 --- /dev/null +++ b/contrib/btree_gin/expected/uuid.out @@ -0,0 +1,104 @@ +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; + i +-------------------------------------- + 00000000-0000-0000-0000-000000000000 + 299bc99f-2f79-4e3e-bfea-2cbfd62a7c27 + 6264af33-0d43-4337-bf4e-43509b8a4be8 +(3 rows) + +SELECT * FROM test_uuid WHERE i<='ce41c936-6acb-4feb-8c91-852a673e5a5c'::uuid ORDER BY i; + i +-------------------------------------- + 00000000-0000-0000-0000-000000000000 + 299bc99f-2f79-4e3e-bfea-2cbfd62a7c27 + 6264af33-0d43-4337-bf4e-43509b8a4be8 + ce41c936-6acb-4feb-8c91-852a673e5a5c +(4 rows) + +SELECT * FROM test_uuid WHERE i='ce41c936-6acb-4feb-8c91-852a673e5a5c'::uuid ORDER BY i; + i +-------------------------------------- + ce41c936-6acb-4feb-8c91-852a673e5a5c +(1 row) + +SELECT * FROM test_uuid WHERE i>='ce41c936-6acb-4feb-8c91-852a673e5a5c'::uuid ORDER BY i; + i +-------------------------------------- + ce41c936-6acb-4feb-8c91-852a673e5a5c + d2ce731f-f2a8-4a2b-be37-8f0ba637427f + ffffffff-ffff-ffff-ffff-ffffffffffff +(3 rows) + +SELECT * FROM test_uuid WHERE i>'ce41c936-6acb-4feb-8c91-852a673e5a5c'::uuid ORDER BY i; + i +-------------------------------------- + d2ce731f-f2a8-4a2b-be37-8f0ba637427f + ffffffff-ffff-ffff-ffff-ffffffffffff +(2 rows) + +EXPLAIN (COSTS OFF) SELECT * FROM test_uuid WHERE i<'ce41c936-6acb-4feb-8c91-852a673e5a5c'::uuid ORDER BY i; + QUERY PLAN +------------------------------------------------------------------------------ + Sort + Sort Key: i + -> Bitmap Heap Scan on test_uuid + Recheck Cond: (i < 'ce41c936-6acb-4feb-8c91-852a673e5a5c'::uuid) + -> Bitmap Index Scan on idx_uuid + Index Cond: (i < 'ce41c936-6acb-4feb-8c91-852a673e5a5c'::uuid) +(6 rows) + +EXPLAIN (COSTS OFF) SELECT * FROM test_uuid WHERE i<='ce41c936-6acb-4feb-8c91-852a673e5a5c'::uuid ORDER BY i; + QUERY PLAN +------------------------------------------------------------------------------- + Sort + Sort Key: i + -> Bitmap Heap Scan on test_uuid + Recheck Cond: (i <= 'ce41c936-6acb-4feb-8c91-852a673e5a5c'::uuid) + -> Bitmap Index Scan on idx_uuid + Index Cond: (i <= 'ce41c936-6acb-4feb-8c91-852a673e5a5c'::uuid) +(6 rows) + +EXPLAIN (COSTS OFF) SELECT * FROM test_uuid WHERE i='ce41c936-6acb-4feb-8c91-852a673e5a5c'::uuid ORDER BY i; + QUERY PLAN +------------------------------------------------------------------------ + Bitmap Heap Scan on test_uuid + Recheck Cond: (i = 'ce41c936-6acb-4feb-8c91-852a673e5a5c'::uuid) + -> Bitmap Index Scan on idx_uuid + Index Cond: (i = 'ce41c936-6acb-4feb-8c91-852a673e5a5c'::uuid) +(4 rows) + +EXPLAIN (COSTS OFF) SELECT * FROM test_uuid WHERE i>='ce41c936-6acb-4feb-8c91-852a673e5a5c'::uuid ORDER BY i; + QUERY PLAN +------------------------------------------------------------------------------- + Sort + Sort Key: i + -> Bitmap Heap Scan on test_uuid + Recheck Cond: (i >= 'ce41c936-6acb-4feb-8c91-852a673e5a5c'::uuid) + -> Bitmap Index Scan on idx_uuid + Index Cond: (i >= 'ce41c936-6acb-4feb-8c91-852a673e5a5c'::uuid) +(6 rows) + +EXPLAIN (COSTS OFF) SELECT * FROM test_uuid WHERE i>'ce41c936-6acb-4feb-8c91-852a673e5a5c'::uuid ORDER BY i; + QUERY PLAN +------------------------------------------------------------------------------ + Sort + Sort Key: i + -> Bitmap Heap Scan on test_uuid + Recheck Cond: (i > 'ce41c936-6acb-4feb-8c91-852a673e5a5c'::uuid) + -> Bitmap Index Scan on idx_uuid + Index Cond: (i > 'ce41c936-6acb-4feb-8c91-852a673e5a5c'::uuid) +(6 rows) + diff --git a/contrib/btree_gin/expected/varbit.out b/contrib/btree_gin/expected/varbit.out new file mode 100644 index 0000000..e8a7718 --- /dev/null +++ b/contrib/btree_gin/expected/varbit.out @@ -0,0 +1,44 @@ +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; + i +----- + 001 + 010 + 011 +(3 rows) + +SELECT * FROM test_varbit WHERE i<='100'::varbit ORDER BY i; + i +----- + 001 + 010 + 011 + 100 +(4 rows) + +SELECT * FROM test_varbit WHERE i='100'::varbit ORDER BY i; + i +----- + 100 +(1 row) + +SELECT * FROM test_varbit WHERE i>='100'::varbit ORDER BY i; + i +----- + 100 + 101 + 110 +(3 rows) + +SELECT * FROM test_varbit WHERE i>'100'::varbit ORDER BY i; + i +----- + 101 + 110 +(2 rows) + diff --git a/contrib/btree_gin/expected/varchar.out b/contrib/btree_gin/expected/varchar.out new file mode 100644 index 0000000..086afbc --- /dev/null +++ b/contrib/btree_gin/expected/varchar.out @@ -0,0 +1,44 @@ +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; + i +----- + a + ab + abb +(3 rows) + +SELECT * FROM test_varchar WHERE i<='abc'::varchar ORDER BY i; + i +----- + a + ab + abb + abc +(4 rows) + +SELECT * FROM test_varchar WHERE i='abc'::varchar ORDER BY i; + i +----- + abc +(1 row) + +SELECT * FROM test_varchar WHERE i>='abc'::varchar ORDER BY i; + i +----- + abc + axy + xyz +(3 rows) + +SELECT * FROM test_varchar WHERE i>'abc'::varchar ORDER BY i; + i +----- + axy + xyz +(2 rows) + 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..7a2f3ac --- /dev/null +++ b/contrib/btree_gin/sql/interval.sql @@ -0,0 +1,24 @@ +set enable_seqscan=off; + +CREATE TABLE test_interval ( + i interval +); + +INSERT INTO test_interval VALUES + ( '-178000000 years' ), + ( '03:55:08' ), + ( '04:55:08' ), + ( '05:55:08' ), + ( '08:55:08' ), + ( '09:55:08' ), + ( '10:55:08' ), + ( '178000000 years' ) +; + +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; |