Re: Correct syntax to create partial index on a boolean column - Mailing list pgsql-general

From Mike Christensen
Subject Re: Correct syntax to create partial index on a boolean column
Date
Msg-id CABs1bs1UO8LJJsuMeEZ+kx2tZ5S7R5a4O51QyZJE2SRQ3o_Kng@mail.gmail.com
Whole thread Raw
In response to Re: Correct syntax to create partial index on a boolean column  (Mike Christensen <mike@kitchenpc.com>)
List pgsql-general
On Wed, Dec 14, 2011 at 9:54 PM, Mike Christensen <mike@kitchenpc.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

pgsql-general by date:

Previous
From: John R Pierce
Date:
Subject: Re: question about \encoding option of psql
Next
From: Alban Hertroys
Date:
Subject: Re: Correct syntax to create partial index on a boolean column