Re: [HACKERS] Bool btree_gin index not chosen on equality contraint, but on greater/lower?
Patric Bechtel writes: > Tom Lane schrieb am 14.02.2016 um 17:51: >> I think your problem is that the planner won't apply >> match_boolean_index_clause() or >> expand_boolean_index_clause(), because it has a hard-wired idea of which >> index opclasses could >> possibly benefit from that, cf IsBooleanOpfamily(). > If someone might give me a hint where to look, I'd be grateful. In principle, instead of using the hard-wired IsBooleanOpfamily test, we could check "op_in_opfamily(BooleanEqualOperator, opfamily)" to see whether those transforms would produce something that works with the index. The reason I didn't do it that way is that those tests are made for every WHERE clause the planner ever considers, so it seemed like adding a syscache lookup that is usually going to accomplish nothing would be pretty annoying overhead. The trick to getting an acceptable patch here would be to figure out how to arrange things to not cost much when the optimization doesn't apply. One idea worth exploring is to redefine IsBooleanOpfamily to inspect the index's AM OID as well as the opfamily, and code it along the lines of ((relam) == BTREE_AM_OID ? (opfamily) == BOOL_BTREE_FAM_OID : (relam) == HASH_AM_OID ? (opfamily) == BOOL_HASH_FAM_OID : op_in_opfamily(BooleanEqualOperator, opfamily)) so that the extra syscache lookup doesn't have to happen at all for btree and hash indexes. But I doubt that moves the needle far enough. Another idea is to try to rearrange match_clause_to_indexcol and expand_indexqual_conditions so that IsBooleanOpfamily() doesn't get hit quite so easily. I don't remember at the moment exactly why that's the first test rather than something further down. Probably at least part of the reason is an assumption that IsBooleanOpfamily() is cheap. It's possible that match_boolean_index_clause() is actually cheaper than the full-fledged version of IsBooleanOpfamily() and so the order of those tests should be reversed; moving them down to after other tests might also be appropriate. Another line of thought is to try to cache the result of the IsBooleanOpfamily() test in the IndexOptInfo structs so it doesn't have to get done so many times. But that would be more invasive and I'm not sure that it helps in simple cases. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bool btree_gin index not chosen on equality contraint, but on greater/lower?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi Tom, Tom Lane schrieb am 14.02.2016 um 17:51: > Patric Bechtel writes: >> 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. > > I think your problem is that the planner won't apply > match_boolean_index_clause() or > expand_boolean_index_clause(), because it has a hard-wired idea of which > index opclasses could > possibly benefit from that, cf IsBooleanOpfamily(). oh, sh*t... My motivation was the size of the bool indexes; they are tiny and really fast. It feels almost like bitmap indexes. I hope that's not too far over my head already... but I'll take a look. If someone might give me a hint where to look, I'd be grateful. Thanks a lot for the hint, Patric -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.22 (GNU/Linux) Comment: GnuPT 2.5.2 iEYEARECAAYFAlbA+64ACgkQfGgGu8y7ypCfVwCg81dCY9Mv70+2dk8e3+5xChyO C7cAn1fRV3NAosi0W3IisKNEmS9K9hZE =Xd+r -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
Re: [HACKERS] Bool btree_gin index not chosen on equality contraint, but on greater/lower?
Patric Bechtel writes: > 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. I think your problem is that the planner won't apply match_boolean_index_clause() or expand_boolean_index_clause(), because it has a hard-wired idea of which index opclasses could possibly benefit from that, cf IsBooleanOpfamily(). regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Bool btree_gin index not chosen on equality contraint, but on greater/lower?
-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 OPERATOR1 <, OPERATOR2 <=, OPERATOR3 =, OPERATOR4 >=, OPERATOR5 >, FUNCTION1 btboolcmp(bool,bool), FUNCTION2 gin_extract_value_bool(bool, internal), FUNCTION3 gin_extract_query_bool(bool, internal, int2, internal, internal), FUNCTION4 gin_btree_consistent(internal, int2, anyelement, int4, internal, internal), FUNCTION5 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