Re: Correct syntax to create partial index on a boolean column

On Wed, Dec 14, 2011 at 9:54 PM, Mike Christensen <mike(at)kitchenpc(dot)com> 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