-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi,
I tried to add bool support to the btree_gin contrib module, and as far as I can tell, it seems to
work (wasn't that complicated, actually).
But now I'm stuck, as PostgreSQL doesn't seem to like to use my new index, if I use equality or
unequality, just with greater and lower than.
My test subject is a table with 13690993 rows, one of them (bar) is a boolean, 376442 are true,
the others are false, no nulls. The index on bar is a btree_gin index. Table is vacuum analyzed
and all, so statistics are fresh and usable, as the estimates within the plans show.
Here's the plan if I ask for 300 rows with d, as in "select id from foo where bar":
Seq Scan on foo (cost=0.00..684709.82 rows=385495 width=8) (actual time=0.014..2657.326
rows=376442 loops=1) Filter: bar Rows Removed by Filter: 13314551Planning time: 0.309 msExecution time: 2672.559 ms
But, if I query "select if from foo where bar>'f'":
Bitmap Heap Scan on foo (cost=7955.59..313817.94 rows=385495 width=8) (actual
time=220.631..365.299 rows=376442 loops=1) Recheck Cond: (bar > false) Heap Blocks: exact=104100 -> Bitmap Index
Scanon ix_foo_gin (cost=0.00..7859.21 rows=385495 width=0) (actual
time=193.192..193.192 rows=376442 loops=1) Index Cond: (bar > false)Planning time: 0.400 msExecution time:
377.518ms
It starts using the index. The rule seems to be: as long as I'm using <, <=, >= or >, it chooses
the index. If I use = or !=, it doesn't.
Here's my definition of the bool_ops for the gin index (it's very similar to the other indexes in
the btree_gin extension):
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;
What am I overseeing?
- --
Patric
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.22 (GNU/Linux)
Comment: GnuPT 2.5.2
iEYEARECAAYFAlbAdg0ACgkQfGgGu8y7ypBHZwCg0g1JSgZTc0OBYsMzrj6w4Zy6
DTQAn38gk8hfqFf86N8hWEzwqc9afjar
=SLMC
-----END PGP SIGNATURE-----