Re: Optimizer & boolean syntax

On Thu, 21 Nov 2002, Christopher Kings-Lynne wrote:

> > > > "col" isn't of the general form "indexkey op constant" or "constant op
> > > > indexkey" which I presume it's looking for given the comments in
> > > > indxpath.c. I'm not sure what the best way to make it work would be
> given
> > > > that presumably we'd want to make col IS TRUE/FALSE use an index at
> the
> > > > same time (since that appears to not do so as well).
> > >
> > > Not that I see the point of indexing booleans, but hey :)
> >
> > also, in reference to my last message, even if the % was 50/50, if the
> > table was such that the bool was in a table next to a text field with 20k
> > or text in it, an index on the bool would be much faster to go through
> > than to seq scan the table.
>
> Hmmm...I'm not sure about that. Postgres's storage strategry with text will
> be to keep it in a side table (or you can use ALTER TABLE/SET STORAGE) and
> it will only be retrieved if it's in the select parameters.

True, but replace that text with 1500 integers. :)

The only problem with the partial index solution is that it seems to still
only work for the same method of asking for the result, so if you make an
index where col=true, using col IS TRUE or col in a query doesn't seem to
use it.