On Fri, Sep 4, 2009 at 6:29 PM, Josh Berkus<josh@xxxxxxxxxxxx> wrote: > Karl, > >> For reference, I was having SEVERE performance problems with the >> following comparison in an SQL statement where "mask" was an integer: >> >> "select ... from .... where ...... and (permission & mask = permission)" > > AFAIK, the only way to use an index on these queries is through > expression indexes. That's why a lot of folks use INTARRAY instead; it > comes with a GIN index type. > > It would probably be possible to create a new index type using GiST or > GIN which indexed bitstrings automatically, but I don't know that anyone > has done it yet. > > Changing your integer to a bitstring will not, to my knowledge, improve > this. agreed. also, gist/gin is no free lunch, maintaining these type of indexes is fairly expensive. If you are only interested in one or a very small number of cases of 'permission', you can use an expression index to target constant values: "select ... from .... where ...... and (permission & mask = permission)" create index foo_permission_xyz_idx on foo((64 & mask = 64)); select * from foo where 64 & mask = 64; --indexed! this optimizes a _particular_ case of permission into a boolean based index. this can be a big win if the # of matching cases is very small or you want to use this in a multi-column index. merlin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
