Re: WIP: cross column correlation ...

On Feb 23, 2011, at 2:58 AM, Robert Haas wrote:

> 2011/2/22 PostgreSQL - Hans-Jürgen Schönig <postgres(at)cybertec(dot)at>:
>> how does it work? we try to find suitable statistics for an arbitrary length list of conditions so that the planner can use it directly rather than multiplying all the selectivities. this should make estimates a lot more precise.
>> the current approach can be extended to work with expressions and well as "straight" conditions.
>
> /me prepares to go down in flames.
>
> Personally, I think the first thing we ought to do is add a real, bona
> fide planner hint to override the selectivity calculation manually,
> maybe something like this:
>
> WHERE (x < 5 AND y = 1) SELECTIVITY (0.1);

i thought there was an agreement that we don't want planner hints?
as tom pointed out - many broken queries come out of some query generator where even the design to make the design is broken by design.
personally i like query generators as long as other people use them ... telling people that this is the wrong way to go is actually financing my holiday next week ... ;). in general - hibernate and stuff like that is a no-go.

personally i like the type of planner hints oleg and teodor came up with - i think we should do more of those hooks they are using but hiding it in some syntax is not a good idea.
it does not change the query and it still gives a lot of room to toy around. it looks like a compromise.

however, oleg's contrib module does not fix the core problem of cross column statistics because a hint is usually static but you want flexible selectivity.

regards,

hans

--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de