Skip to content

Navigation Menu

Sign in

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit 57e3c51

Browse filesBrowse 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
Copy full SHA for 57e3c51

File tree

8 files changed

+382
-3
lines changed
Filter options

8 files changed

+382
-3
lines changed

‎contrib/btree_gist/Makefile

Copy file name to clipboardExpand all lines: contrib/btree_gist/Makefile
+3-2Lines changed: 3 additions & 2 deletions
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

Copy file name to clipboard
+169Lines changed: 169 additions & 0 deletions
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

Copy file name to clipboard
+62Lines changed: 62 additions & 0 deletions
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

Copy file name to clipboard
+1-1Lines changed: 1 addition & 1 deletion
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

Copy file name to clipboardExpand all lines: contrib/btree_gist/btree_gist.h
+1Lines changed: 1 addition & 0 deletions
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

Copy file name to clipboardExpand all lines: contrib/btree_gist/btree_utils_num.c
+8Lines changed: 8 additions & 0 deletions
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

Copy file name to clipboard
+96Lines changed: 96 additions & 0 deletions
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 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.