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" = '[email protected]' ) 
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 explain.depesz.com — IMSoP
– IMSoP, Commented May 26, 2015 at 10:29
gain is minimum, since boolean index has low cardinality... unless you use it to "cluster" other column,based on the current boolean — Vao Tsun
– Vao Tsun, Commented May 26, 2015 at 12:11

Clément Prévost · Accepted Answer · 2015-05-27 22:58:03Z

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, 2015 at 22:58

Clément Prévost

8,6702 gold badges39 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create index for boolean columns

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related