On 15 Dec 2011, at 5:43, Mike Christensen 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?
Correct, but... That's not a particularly useful index to create. That index just contains values of true where the associated column equals true - you're storing the same information twice. It's generally more useful to index a column with values that you're likely to be interested in for limiting the result set further or for sorting or some-such, as long as the operation performed benefits from using an index. From your later example, for instance: SELECT RecipeId from RecipeMetadata where diet_glutenfree; If you plan to use this query in a join, an index like this would be more useful: CREATE INDEX recipemetadata_recipeid_glutenfree_idx ON RecipeMetadata(RecipeId) WHERE diet_glutenfree; That's a bit similar to creating an index on (RecipeId, diet_glutenfree), except that the latter also contains entries that are not gluten-free of course. Alban Hertroys -- The scale of a problem often equals the size of an ego. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
