Re: Index not used, performance problem

"scott.marlowe" <scott.marlowe@ihs.com> writes:
> So, create your index this way to make it smaller and faster:
> create index dxname on sometable (bool_field) where bool_field IS TRUE;

Also note that the index itself could be on some other column; for
example if you do

    create index fooi on foo (intcol) where boolcol;

then a query like

    select ... from foo where intcol >= 42 and boolcol;

could use the index to exploit both WHERE conditions.

> You have to, however, access it the same way.  the proper
> way to reference a bool field is with IS [NOT] {TRUE|FALSE}

This strikes me as pedantry.  "WHERE bool" (resp. "WHERE NOT bool") has
the same semantics and is easier to read, at least to me.  (Of course,
if you think differently, then by all means write the form that seems
clearest to you.)

But yeah, the condition appearing in the actual queries had best match
what's used in the partial-index CREATE command exactly.  The planner is
not real smart about deducing "this implies that".

            regards, tom lane