Re: CHECK constraints and optimizations

On Thu, May 06, 2004 at 09:02:21PM -0400, Tom Lane wrote:
> Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> > I can't see why this wouldn't work.
>
> Doubtless you could do it. The problem with the idea is that those
> inference tests are pretty expensive. I think that any such thing would
> waste significant numbers of cycles on ordinary queries while only being
> a win on a few poorly-written queries.

Is it really that expensive? From the index code I remember playing
with way back when I was fiddling with the partial index stuff, there
is a table where it takes:

X OPa Val1
X OPb Val2

and it has a lookup table on (OPa,OPb) to provide an OPc that can be
applied to (Val1,Val2) to determine if one implies the other. I was
very impressed actually, quite a neat idea. Quite simple I thought.

I wasn't really considering anything more complicated than this. No
subclauses, only ANDs.

> We do have to make a tradeoff between planning time and execution time,
> and I fear that this idea is not going to be a win in those terms.
> If you feel like experimenting, though, go for it ...

Ofcourse, my ulterior motive is that I want table partitioning based on
values within the tuple. And then have queries avoid scanning tables
that query things that are not in those tables as inferred by parts of
the WHERE clause. Think phone calls with a different subtable for each
year, automatically.

I toyed with creating a script that would generate the RULEs necessary
to implement it in the current system, but splitting a table into four
peices would require around 50+ RULEs (4 subtables x 4 conditions x 3
query types), obviously massively more inefficient that what's being
suggested here.

The solution is to build it right into the storage manager, but I
haven't tried that yet.

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.