Re: Query Hints? No thanks. Data hints?

On Sun, May 04, 2008 at 09:44:58PM +0200, Dimitri Fontaine wrote:
> IIRC, I've read here in the past some attempts to begin a proposal on
> the topic of data hints, allowing the user to describe his data in a
> way ANALYZE can't figure out by itself, as e.g. "this column value is
> tied to this other column value in this way".

There has been talk about this in the past, things like being able to
store cross-table correlations which isn't know at all. The problem is
two-fold:

- How do you calculate the stats?
- How do you use them?

Cross-table correlations are easy for the second part, because it's
fairly simple to see where it could be used. However, no-one has come
up with an algorithm to produce a useful number to use. For others it's
harder.

> I'm thinking we could have a new set of commands to tell PostgreSQL
> some "high-level" facts about the data, e.g. "there's a injective
> function such as f(t.colA) = t.colB" or any useful thing to be found
> in the firsts proposed step.

In general postgres could use many bits of information not currently
available. For example: A=B implies lower(A)=lower(B), hence an index
on lower(A) could be used to optimise comparisons against A. Certain
operations preserve order, which may also be useful.

You can always propose something, see where it goes.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.