Re: WIP: cross column correlation ...

2011/2/23 PostgreSQL - Hans-Jürgen Schönig <postgres(at)cybertec(dot)at>:
>> Those are real problems, but I still want it.  The last time I hit
>> this problem I spent two days redesigning my schema and adding
>> triggers all over the place to make things work.  If I had been
>> dealing with a 30TB database instead of a 300MB database I would have
>> been royally up a creek.
>>
>> To put that another way, it's true that some people can't adjust their
>> queries, but also some people can.  It's true that nonstandard stuff
>> sucks, but queries that don't work suck, too.  And as for better
>> solutions, how many major release cycles do we expect people to wait
>> for them?  Even one major release cycle is an eternity when you're
>> trying to get the application working before your company runs out of
>> money, and this particular problem has had a lot of cycles expended on
>> it without producing anything very tangible (proposed patch, which
>> like you I can't spare a lot of cycles to look at just now, possibly
>> excepted).
>
> cheapest and easiest solution if you run into this: add "fake" functions which the planner cannot estimate properly.
> use OR to artificially prop up estimates or use AND to artificially lower them. there is actually no need to redesign the schema to get around it but it is such an ugly solution that it does not even deserve to be called "ugly" ...
> however, fast and reliable way to get around it.

We couldn't possibly design a hint mechanism that would be uglier or
less future-proof than this workaround (which, by the way, I'll keep
in mind for the next time I get bitten by this).

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company