Create index for boolean columns

Question

I have table that has 5 boolean columns.

on_stock | paid | received_payment | on_the_way | received

How to create an index for this table? Should I do it? I want to optimize a query like this:

SELECT "order".* FROM "order" INNER JOIN "seller_users" ON "order"."seller_foreign_id" = "seller_users"."google_id" 
WHERE(((("order"."on_stock" <> true AND "order"."on_the_way" <> true ) AND "order"."paid" <> true ) AND "order"."received_payment" <> true ) AND "order"."received" <> true ) AND ("seller_users"."google_id" = 'lala@gmail.com' ) 
ORDER BY "order"."updated_at" DESC ;

When I try add this index - nothing happens. This index is not used.

 add_index :order, [:on_stock, :on_the_way, :paid, :received_payment, :received], :name => "state_index"

If I add separate index for each columns - nothing happens too.

EXPLAIN ANALYZE output: http://explain.depesz.com/s/FS2

Please could you [edit] in that EXPLAIN ANALYZE output as text. Or perhaps paste it into http://explain.depesz.com — IMSoP, May 26 '15 at 10:29
@usmanali Er, why? A partial index should be better for this use case, assuming they're always looking for `<> true` for each col. Otherwise a multi-col index is likely the right choice. — Craig Ringer, May 26 '15 at 11:24
@ViT-Vetal- If for testing you `SET enable_seqscan = off` what plan do you get then? Also please show `\dt order` from `psql` — Craig Ringer, May 26 '15 at 11:26
@CraigRinger, after `enable_seqscan = off` postgres start using my index, but gain is minimum:) — ViT-Vetal-, May 26 '15 at 11:51
gain is minimum, since boolean index has low cardinality... unless you use it to "cluster" other column,based on the current boolean — Vao Tsun, May 26 '15 at 12:11
Yup, and that's why PostgreSQL is choosing a seqscan. Indexes aren't magical go-faster juice, and you're seeing that here. A selection of partial indexes for high-selectivity subgroups might be more useful... — Craig Ringer, May 26 '15 at 13:11

score 1 · Answer 1 · answered May 27 '15 at 22:58

1

Your table has a total of 8 rows, an index is not needed in this case. It is way faster to test each of this 8 rows against the where clause than to use an index here.

answered May 27 '15 at 22:58

Clément Prévost

8,000
2
36
51

Create index for boolean columns

1 Answers1