Re: WIP: cross column correlation ...

On Feb 24, 2011, at 2:09 AM, Josh Berkus wrote:

>
>> 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);
>>
>> Then, having provided a method for the DBA to extinguish the raging
>> flames of searing agony which are consuming them while a crocodile
>> chews off their leg and their boss asks them why they didn't use
>> Oracle, we can continue bikeshedding about the best way of fixing this
>> problem in a more user-transparent fashion.
>
> Is there some way we can do that without adding the selectivity hint to
> the query itself? That's the biggest issue with hints.
>

well, you could hide this hint in the system table - say; instead of decorating the query you could store the decoration in some system relation ... but, if you get it right, you call this decoration histogram ;).
i think the patch with a multi-dim histogram is good (i have seen something similar for PostGIS).
what is still needed in our patch is a.) multi-dim sampling (no idea how to get it right) and b.) investigating how to deal with joins and expressions (e.g. cos(id) ).
hints into the right direction are highly welcome.

many thanks,

hans

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