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 CABs1bs2c-rW_mAzNjaAMooWVsEbn-=Pg42LUA896BEiyVD-Svg@mail.gmail.com
Whole thread Raw
In response to Re: Correct syntax to create partial index on a boolean column  (Craig Ringer <ringerc@ringerc.id.au>)
Responses Re: Correct syntax to create partial index on a boolean column
List pgsql-general
On Thu, Dec 15, 2011 at 6:00 AM, Craig Ringer <ringerc@ringerc.id.au> wrote:
> On 12/15/2011 03:53 PM, Alban Hertroys wrote:
>>
>> 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 could be very handy if you have an extremely high selectivity index (say
> 1:1000 or more) where you want to keep the index tiny, fast, and very quick
> to scan.
>
> I guess ideally Pg would be able to deduce that the index value is always
> the same and just store a page list rather than a b-tree, but it's a bit of
> a tiny use case.

The partial index is definitely a lot smaller.

BTW, this table (RecipeMetadata) will only ever be used in a join.  I
will never query it directly.  But I'll query Recipes and join in
RecipeMetadata.

pgsql-general by date:

Previous
From: Carlos Mennens
Date:
Subject: Re: Server/Data Migration Advice
Next
From: Adrian Klaver
Date:
Subject: Re: Server/Data Migration Advice