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.
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.
regards, tom lane