On Wed, Dec 14, 2011 at 9:54 PM, Mike Christensen <mike@xxxxxxxxxxxxx> wrote: >> For the boolean column Foo in Table1, if I want to index all values of >> TRUE, is this syntax correct? >> >> CREATE INDEX IDX_MyIndex ON Table1(Foo) WHERE Foo; >> >> The query: >> >> SELECT * FROM Table1 WHERE Foo; >> >> should use the index, and: >> >> SELECT * FROM Table1 WHERE NOT Foo; >> >> should not, correct? >> >> I just want to make sure I don't need an operator on the WHERE clause. Thanks! > > FYI, I've posted this on StackOverflow too in case anyone wants to > score some points.. > > http://stackoverflow.com/questions/8514923/postgres-is-this-the-right-way-to-create-a-partial-index-on-a-boolean-column > > I'm 90% sure this is the right way to do it though. > > Mike I've confirmed the index works as expected. I created 10,000 rows of random data, and set `diet_glutenfree` to `random() > 0.9` so there's only a 10% chance of an `on` bit. I then re-created the indexes and tried the query again. SELECT RecipeId from RecipeMetadata where diet_glutenfree; Returns: 'Index Scan using idx_recipemetadata_glutenfree on recipemetadata (cost=0.00..135.15 rows=1030 width=16)' ' Index Cond: (diet_glutenfree = true)' And: SELECT RecipeId from RecipeMetadata where NOT diet_glutenfree; Returns: 'Seq Scan on recipemetadata (cost=0.00..214.26 rows=8996 width=16)' ' Filter: (NOT diet_glutenfree)' So, it will definitely use the index when I query for ON values. Just out of curiosity, is there a way to verify the number of rows that are indexed on a partial query? Mike -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
