Re: query plan ignoring check constraints

On Tue, Jun 21, 2005 at 21:54:34 +1000,
John Hansen <john(at)geeknet(dot)com(dot)au> wrote:
> Bruno Wolff III [mailto:bruno(at)wolff(dot)to] Wrote
>
> > I think the real problem is that check constraints on tables
> > aren't used by the optimizer. Given that, what you have below
> > is expected.
> > There has been talk about that in the past, but I haven't
> > heard anything recently about someone considering implenting that.
> >
> > For your problem consider not using a partial index. It isn't
> > going to save anything if it has a constraint matching that
> > of the table.
>
>
> Ahh, I get it now,...
>
> If a column has a CHECK (col IN (1,2,3)) and a query says .. WHERE col
> = 4; then the planner should
> know that the query will return 0 rows, right?

In an ideal world yes; in the current world no. However if you have a
normal index on the table, an index scan that finds no rows isn't
terribly expensive.

You only want to use partial indexes when they don't cover the whole
table. They make sense to enforce uniqueness of a column under some
condition and when you can save significant space (becuase the condition
is only satisfied for a small fraction of rows).