Skip to content

Commit 57e3c51

Browse files
tvondratvondra
committed
Add bool GiST opclass to btree_gist
Adds bool opclass to btree_gist extension, to allow creating GiST indexes on bool columns. GiST indexes on a single bool column don't seem particularly useful, but this allows defining exclusion constraings involving a bool column, for example. Author: Emre Hasegeli Reviewed-by: Andrey Borodin Discussion: https://postgr.es/m/CAE2gYzyDKJBZngssR84VGZEN=Ux=V9FV23QfPgo+7-yYnKKg4g@mail.gmail.com
1 parent dafcf88 commit 57e3c51

File tree

8 files changed

+382
-3
lines changed

8 files changed

+382
-3
lines changed

‎contrib/btree_gist/Makefile

+3-2
Original file line numberDiff line numberDiff line change
@@ -5,6 +5,7 @@ MODULE_big = btree_gist
55
OBJS = \
66
$(WIN32RES) \
77
btree_bit.o \
8+
btree_bool.o \
89
btree_bytea.o \
910
btree_cash.o \
1011
btree_date.o \
@@ -32,12 +33,12 @@ EXTENSION = btree_gist
3233
DATA = btree_gist--1.0--1.1.sql \
3334
btree_gist--1.1--1.2.sql btree_gist--1.2.sql btree_gist--1.2--1.3.sql \
3435
btree_gist--1.3--1.4.sql btree_gist--1.4--1.5.sql \
35-
btree_gist--1.5--1.6.sql
36+
btree_gist--1.5--1.6.sql btree_gist--1.6--1.7.sql
3637
PGFILEDESC = "btree_gist - B-tree equivalent GiST operator classes"
3738

3839
REGRESS = init int2 int4 int8 float4 float8 cash oid timestamp timestamptz \
3940
time timetz date interval macaddr macaddr8 inet cidr text varchar char \
40-
bytea bit varbit numeric uuid not_equal enum
41+
bytea bit varbit numeric uuid not_equal enum bool
4142

4243
SHLIB_LINK += $(filter -lm, $(LIBS))
4344

‎contrib/btree_gist/btree_bool.c

+169
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,169 @@
1+
/*
2+
* contrib/btree_gist/btree_bool.c
3+
*/
4+
#include "postgres.h"
5+
6+
#include "btree_gist.h"
7+
#include "btree_utils_num.h"
8+
#include "common/int.h"
9+
10+
typedef struct boolkey
11+
{
12+
bool lower;
13+
bool upper;
14+
} boolKEY;
15+
16+
/*
17+
** bool ops
18+
*/
19+
PG_FUNCTION_INFO_V1(gbt_bool_compress);
20+
PG_FUNCTION_INFO_V1(gbt_bool_fetch);
21+
PG_FUNCTION_INFO_V1(gbt_bool_union);
22+
PG_FUNCTION_INFO_V1(gbt_bool_picksplit);
23+
PG_FUNCTION_INFO_V1(gbt_bool_consistent);
24+
PG_FUNCTION_INFO_V1(gbt_bool_penalty);
25+
PG_FUNCTION_INFO_V1(gbt_bool_same);
26+
27+
static bool
28+
gbt_boolgt(const void *a, const void *b, FmgrInfo *flinfo)
29+
{
30+
return (*((const bool *) a) > *((const bool *) b));
31+
}
32+
static bool
33+
gbt_boolge(const void *a, const void *b, FmgrInfo *flinfo)
34+
{
35+
return (*((const bool *) a) >= *((const bool *) b));
36+
}
37+
static bool
38+
gbt_booleq(const void *a, const void *b, FmgrInfo *flinfo)
39+
{
40+
return (*((const bool *) a) == *((const bool *) b));
41+
}
42+
static bool
43+
gbt_boolle(const void *a, const void *b, FmgrInfo *flinfo)
44+
{
45+
return (*((const bool *) a) <= *((const bool *) b));
46+
}
47+
static bool
48+
gbt_boollt(const void *a, const void *b, FmgrInfo *flinfo)
49+
{
50+
return (*((const bool *) a) < *((const bool *) b));
51+
}
52+
53+
static int
54+
gbt_boolkey_cmp(const void *a, const void *b, FmgrInfo *flinfo)
55+
{
56+
boolKEY *ia = (boolKEY *) (((const Nsrt *) a)->t);
57+
boolKEY *ib = (boolKEY *) (((const Nsrt *) b)->t);
58+
59+
if (ia->lower == ib->lower)
60+
{
61+
if (ia->upper == ib->upper)
62+
return 0;
63+
64+
return (ia->upper > ib->upper) ? 1 : -1;
65+
}
66+
67+
return (ia->lower > ib->lower) ? 1 : -1;
68+
}
69+
70+
71+
static const gbtree_ninfo tinfo =
72+
{
73+
gbt_t_bool,
74+
sizeof(bool),
75+
4, /* sizeof(gbtreekey4) */
76+
gbt_boolgt,
77+
gbt_boolge,
78+
gbt_booleq,
79+
gbt_boolle,
80+
gbt_boollt,
81+
gbt_boolkey_cmp,
82+
};
83+
84+
85+
/**************************************************
86+
* bool ops
87+
**************************************************/
88+
89+
90+
Datum
91+
gbt_bool_compress(PG_FUNCTION_ARGS)
92+
{
93+
GISTENTRY *entry = (GISTENTRY *) PG_GETARG_POINTER(0);
94+
95+
PG_RETURN_POINTER(gbt_num_compress(entry, &tinfo));
96+
}
97+
98+
Datum
99+
gbt_bool_fetch(PG_FUNCTION_ARGS)
100+
{
101+
GISTENTRY *entry = (GISTENTRY *) PG_GETARG_POINTER(0);
102+
103+
PG_RETURN_POINTER(gbt_num_fetch(entry, &tinfo));
104+
}
105+
106+
Datum
107+
gbt_bool_consistent(PG_FUNCTION_ARGS)
108+
{
109+
GISTENTRY *entry = (GISTENTRY *) PG_GETARG_POINTER(0);
110+
bool query = PG_GETARG_INT16(1);
111+
StrategyNumber strategy = (StrategyNumber) PG_GETARG_UINT16(2);
112+
113+
/* Oid subtype = PG_GETARG_OID(3); */
114+
bool *recheck = (bool *) PG_GETARG_POINTER(4);
115+
boolKEY *kkk = (boolKEY *) DatumGetPointer(entry->key);
116+
GBT_NUMKEY_R key;
117+
118+
/* All cases served by this function are exact */
119+
*recheck = false;
120+
121+
key.lower = (GBT_NUMKEY *) &kkk->lower;
122+
key.upper = (GBT_NUMKEY *) &kkk->upper;
123+
124+
PG_RETURN_BOOL(gbt_num_consistent(&key, (void *) &query, &strategy,
125+
GIST_LEAF(entry), &tinfo, fcinfo->flinfo));
126+
}
127+
128+
129+
Datum
130+
gbt_bool_union(PG_FUNCTION_ARGS)
131+
{
132+
GistEntryVector *entryvec = (GistEntryVector *) PG_GETARG_POINTER(0);
133+
void *out = palloc(sizeof(boolKEY));
134+
135+
*(int *) PG_GETARG_POINTER(1) = sizeof(boolKEY);
136+
PG_RETURN_POINTER(gbt_num_union((void *) out, entryvec, &tinfo, fcinfo->flinfo));
137+
}
138+
139+
140+
Datum
141+
gbt_bool_penalty(PG_FUNCTION_ARGS)
142+
{
143+
boolKEY *origentry = (boolKEY *) DatumGetPointer(((GISTENTRY *) PG_GETARG_POINTER(0))->key);
144+
boolKEY *newentry = (boolKEY *) DatumGetPointer(((GISTENTRY *) PG_GETARG_POINTER(1))->key);
145+
float *result = (float *) PG_GETARG_POINTER(2);
146+
147+
penalty_num(result, origentry->lower, origentry->upper, newentry->lower, newentry->upper);
148+
149+
PG_RETURN_POINTER(result);
150+
}
151+
152+
Datum
153+
gbt_bool_picksplit(PG_FUNCTION_ARGS)
154+
{
155+
PG_RETURN_POINTER(gbt_num_picksplit((GistEntryVector *) PG_GETARG_POINTER(0),
156+
(GIST_SPLITVEC *) PG_GETARG_POINTER(1),
157+
&tinfo, fcinfo->flinfo));
158+
}
159+
160+
Datum
161+
gbt_bool_same(PG_FUNCTION_ARGS)
162+
{
163+
boolKEY *b1 = (boolKEY *) PG_GETARG_POINTER(0);
164+
boolKEY *b2 = (boolKEY *) PG_GETARG_POINTER(1);
165+
bool *result = (bool *) PG_GETARG_POINTER(2);
166+
167+
*result = gbt_num_same((void *) b1, (void *) b2, &tinfo, fcinfo->flinfo);
168+
PG_RETURN_POINTER(result);
169+
}

‎contrib/btree_gist/btree_gist--1.6--1.7.sql

+62
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,62 @@
1+
/* contrib/btree_gist/btree_gist--1.6--1.7.sql */
2+
3+
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
4+
\echo Use "ALTER EXTENSION btree_gist UPDATE TO '1.7'" to load this file. \quit
5+
6+
-- This upgrade scripts add support for bool.
7+
8+
-- Define the GiST support methods
9+
CREATE FUNCTION gbt_bool_consistent(internal,bool,int2,oid,internal)
10+
RETURNS bool
11+
AS 'MODULE_PATHNAME'
12+
LANGUAGE C IMMUTABLE STRICT;
13+
14+
CREATE FUNCTION gbt_bool_compress(internal)
15+
RETURNS internal
16+
AS 'MODULE_PATHNAME'
17+
LANGUAGE C IMMUTABLE STRICT;
18+
19+
CREATE FUNCTION gbt_bool_fetch(internal)
20+
RETURNS internal
21+
AS 'MODULE_PATHNAME'
22+
LANGUAGE C IMMUTABLE STRICT;
23+
24+
CREATE FUNCTION gbt_bool_penalty(internal,internal,internal)
25+
RETURNS internal
26+
AS 'MODULE_PATHNAME'
27+
LANGUAGE C IMMUTABLE STRICT;
28+
29+
CREATE FUNCTION gbt_bool_picksplit(internal, internal)
30+
RETURNS internal
31+
AS 'MODULE_PATHNAME'
32+
LANGUAGE C IMMUTABLE STRICT;
33+
34+
CREATE FUNCTION gbt_bool_union(internal, internal)
35+
RETURNS gbtreekey8
36+
AS 'MODULE_PATHNAME'
37+
LANGUAGE C IMMUTABLE STRICT;
38+
39+
CREATE FUNCTION gbt_bool_same(gbtreekey8, gbtreekey8, internal)
40+
RETURNS internal
41+
AS 'MODULE_PATHNAME'
42+
LANGUAGE C IMMUTABLE STRICT;
43+
44+
-- Create the operator class
45+
CREATE OPERATOR CLASS gist_bool_ops
46+
DEFAULT FOR TYPE bool USING gist
47+
AS
48+
OPERATOR 1 < ,
49+
OPERATOR 2 <= ,
50+
OPERATOR 3 = ,
51+
OPERATOR 4 >= ,
52+
OPERATOR 5 > ,
53+
OPERATOR 6 <> ,
54+
FUNCTION 1 gbt_bool_consistent (internal, bool, int2, oid, internal),
55+
FUNCTION 2 gbt_bool_union (internal, internal),
56+
FUNCTION 3 gbt_bool_compress (internal),
57+
FUNCTION 4 gbt_decompress (internal),
58+
FUNCTION 5 gbt_bool_penalty (internal, internal, internal),
59+
FUNCTION 6 gbt_bool_picksplit (internal, internal),
60+
FUNCTION 7 gbt_bool_same (gbtreekey8, gbtreekey8, internal),
61+
FUNCTION 9 gbt_bool_fetch (internal),
62+
STORAGE gbtreekey8;

‎contrib/btree_gist/btree_gist.control

+1-1
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
11
# btree_gist extension
22
comment = 'support for indexing common datatypes in GiST'
3-
default_version = '1.6'
3+
default_version = '1.7'
44
module_pathname = '$libdir/btree_gist'
55
relocatable = true
66
trusted = true

‎contrib/btree_gist/btree_gist.h

+1
Original file line numberDiff line numberDiff line change
@@ -32,6 +32,7 @@ enum gbtree_type
3232
gbt_t_bpchar,
3333
gbt_t_bytea,
3434
gbt_t_bit,
35+
gbt_t_bool,
3536
gbt_t_inet,
3637
gbt_t_uuid,
3738
gbt_t_enum

‎contrib/btree_gist/btree_utils_num.c

+8
Original file line numberDiff line numberDiff line change
@@ -19,6 +19,7 @@ gbt_num_compress(GISTENTRY *entry, const gbtree_ninfo *tinfo)
1919
{
2020
union
2121
{
22+
bool bo;
2223
int16 i2;
2324
int32 i4;
2425
int64 i8;
@@ -35,6 +36,10 @@ gbt_num_compress(GISTENTRY *entry, const gbtree_ninfo *tinfo)
3536

3637
switch (tinfo->t)
3738
{
39+
case gbt_t_bool:
40+
v.bo = DatumGetBool(entry->key);
41+
leaf = &v.bo;
42+
break;
3843
case gbt_t_int2:
3944
v.i2 = DatumGetInt16(entry->key);
4045
leaf = &v.i2;
@@ -113,6 +118,9 @@ gbt_num_fetch(GISTENTRY *entry, const gbtree_ninfo *tinfo)
113118
*/
114119
switch (tinfo->t)
115120
{
121+
case gbt_t_bool:
122+
datum = BoolGetDatum(*(bool *) entry->key);
123+
break;
116124
case gbt_t_int2:
117125
datum = Int16GetDatum(*(int16 *) entry->key);
118126
break;

‎contrib/btree_gist/expected/bool.out

+96
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,96 @@
1+
-- bool check
2+
CREATE TABLE booltmp (a bool);
3+
INSERT INTO booltmp VALUES (false), (true);
4+
SET enable_seqscan=on;
5+
SELECT count(*) FROM booltmp WHERE a < true;
6+
count
7+
-------
8+
1
9+
(1 row)
10+
11+
SELECT count(*) FROM booltmp WHERE a <= true;
12+
count
13+
-------
14+
2
15+
(1 row)
16+
17+
SELECT count(*) FROM booltmp WHERE a = true;
18+
count
19+
-------
20+
1
21+
(1 row)
22+
23+
SELECT count(*) FROM booltmp WHERE a >= true;
24+
count
25+
-------
26+
1
27+
(1 row)
28+
29+
SELECT count(*) FROM booltmp WHERE a > true;
30+
count
31+
-------
32+
0
33+
(1 row)
34+
35+
CREATE INDEX boolidx ON booltmp USING gist ( a );
36+
SET enable_seqscan=off;
37+
SELECT count(*) FROM booltmp WHERE a < true;
38+
count
39+
-------
40+
1
41+
(1 row)
42+
43+
SELECT count(*) FROM booltmp WHERE a <= true;
44+
count
45+
-------
46+
2
47+
(1 row)
48+
49+
SELECT count(*) FROM booltmp WHERE a = true;
50+
count
51+
-------
52+
1
53+
(1 row)
54+
55+
SELECT count(*) FROM booltmp WHERE a >= true;
56+
count
57+
-------
58+
1
59+
(1 row)
60+
61+
SELECT count(*) FROM booltmp WHERE a > true;
62+
count
63+
-------
64+
0
65+
(1 row)
66+
67+
-- Test index-only scans
68+
SET enable_bitmapscan=off;
69+
EXPLAIN (COSTS OFF)
70+
SELECT * FROM booltmp WHERE a;
71+
QUERY PLAN
72+
------------------------------------------
73+
Index Only Scan using boolidx on booltmp
74+
Filter: a
75+
(2 rows)
76+
77+
SELECT * FROM booltmp WHERE a;
78+
a
79+
---
80+
t
81+
(1 row)
82+
83+
EXPLAIN (COSTS OFF)
84+
SELECT * FROM booltmp WHERE NOT a;
85+
QUERY PLAN
86+
------------------------------------------
87+
Index Only Scan using boolidx on booltmp
88+
Filter: (NOT a)
89+
(2 rows)
90+
91+
SELECT * FROM booltmp WHERE NOT a;
92+
a
93+
---
94+
f
95+
(1 row)
96+

0 commit comments

Comments
 (0)