On Mon, Sep 11, 2000 at 12:22:39PM -0400, Tom Lane wrote:
> Jules Bean <jules@jellybean.co.uk> writes:
> > However, it does seem to me that PostgreSQL /should/
> > be able to make these transformations (at least, it should IMO
> > recognise that given an expression of the form a + b - c + d < e - f
> > + g where exactly one of a..g is a column name, and the rest are
> > constant, that is a candidate for using the index).
>
> Mumble. I think that'd be a very difficult thing to do without losing
> the datatype extensibility of the system. Right now, the only reason
> that "a < b" is considered indexable is that the optimizer has a table
> that tells it "<" is an indexable operator for btree indexes with
> certain datatypes (opclasses). Neither the optimizer nor the btree code
> has any real understanding of the relationships between "<" and "-", say.
> There is no part of the system anywhere with understanding of algebraic
> identities like "a - b < c can be transformed to a < b + c", and no way
> I can see to add such knowledge without making it *substantially* harder
> to add new datatypes and operators.
Yes, actually something like this occurred to me after I sent the
above email. I had forgotten about the (rather pretty) extensible
type system; I can see that makes spotting optimisations such as the
above much more difficult. Seems like it might make a nice subject for
a paper, actually.
>
> Between that and the runtime that would be wasted during typical queries
> (IMHO searching for rearrangeable clauses would usually be fruitless),
> I really doubt that this is a good goal to pursue in Postgres.
I'm afraid I can't buy that second argument ;-) The time it takes to
optimise a query is asymptotically irrelevant, after all...
Jules
