alternative to bitmap index in postgresql

Question

I have a table with hundreds of millions rows with schema like below.

tabe AA {
 id integer primay key,
 prop0 boolean not null,
 prop1 boolean not null,
 prop2 smallint not null,
 ...
}

The each "property" field (prop0, prop1, ...) has a small number of distinct values. And I usually query to find "id" from the given conditions of properties fields. I think Bitmap index is best for this query. But postgresql seems not support bitmap index.

I tried b-tree index on each field but these indexes are not used according to the query explain.

Is there a good alternative way to do this?

(i'm using postgresql 9)

Answer 1

Your real problem is a bad schema design, not the index. The properties should be placed in a different table and your current table should link to that table using a many to many relation.

The BIT datatype might also be of use, just check the manual.

Answer 2

An index is only used if it actually speeds up the query which is not necessarily always the case. Especially with smallish tables (say thousands of rows) a full table scan ("seq scan" in the Postgres execution plan) might indeed be a lot faster.

How many rows did the table have when you tried the statement? How did the query look like? Maybe there are other conditions that prevent the index usage. Did you analyze the table to have up-to-date statistics?

Answer 3

Create a multicolumn index on properties which are always or almost always queried. Or several multicolumn indexes if needed.

The alternative, when you do not query the same properties almost always, is to make a tsvector column with words describing your data, maintained using trigger, for example

prop0=true
prop1=false
prop2=4

would be

'propzero nopropone proptwo4'::tsvector

index it using GIN and then use full text search for searching:

where tsv @@ 'popzero & nopropone & proptwo4'::tsquery