-----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: 13314551
Planning time: 0.309 ms
Execution 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 Scan on 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 ms
Execution time: 377.518 ms
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-----
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
