-----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

Reply via email to