Re: Indexes

Jake Stride <nsuk(at)users(dot)sourceforge(dot)net> writes:
> Does saying 'main' not mean where main=true as it is a boolean

It means the same, but that doesn't make it an indexable condition.
In Postgres, the index machinery is built around operators; if you
don't have a WHERE clause like "indexvar operator something" then
you don't have an indexable condition.

PG 8.1 will recognize that it can convert a boolean "main" to
"main = true" and then use an index on main, but no existing release
will do so. Here's the CVS log entry about it:

2005-03-26 18:29 tgl

* src/: backend/optimizer/path/indxpath.c,
backend/optimizer/path/orindxpath.c,
backend/optimizer/util/pathnode.c, include/catalog/pg_opclass.h,
include/optimizer/paths.h: Expand the 'special index operator'
machinery to handle special cases for boolean indexes. Previously
we would only use such an index with WHERE clauses like 'indexkey =
true' or 'indexkey = false'. The new code transforms the cases
'indexkey', 'NOT indexkey', 'indexkey IS TRUE', and 'indexkey IS
FALSE' into one of these. While this is only marginally useful in
itself, I intend soon to change constant-expression simplification
so that 'foo = true' and 'foo = false' are reduced to just 'foo'
and 'NOT foo' ... which would lose the ability to use boolean
indexes for such queries at all, if the indexscan machinery
couldn't make the reverse transformation.

regards, tom lane