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 <> 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.