How to optimize search restricted by boolean column? I want to optimize a query1 of the form

SELECT yid, xid FROM x WHERE is_principal;


Here, is_principal is a non-null boolean column of x.

If I run EXPLAIN QUERY PLAN on this query, the output I get is

0|0|0|SCAN TABLE x


I'd like to turn this SCAN into a SEARCH, so created the following index:

CREATE INDEX x_is_principal_idx ON x (is_principal);


...but it made no difference: the output of EXPLAIN QUERY PLAN remains unchanged.

Is there some other way to optimize this query?



1 FWIW, this query is in fact a sub-query of a much larger query that I'm trying to optimize.
The expression is_principal is the same as is_principal &lt;&gt; 0, so the database assumes that all values except 0 are searched for, and estimates that most rows will match.

If the values in the is_principal column are proper booleans, you can get the database to assume a higher selectivity by searching for the value you actually want to search for:

SELECT yid, xid FROM x WHERE is_principal = 1


This query is likely to read the rows from the table in index order (i.e., random order), so if there is a large number of matching rows, performance will be worse.