Re: Big disappointment with Postgres
Date: Mon, 14 Feb 2011 13:32:10 -0000
"Serge Rielau" <srielau_at_ca.ibm.com> wrote in message news:8rq7imFacfU1_at_mid.individual.net...
> On 2/13/2011 7:51 AM, Jonathan Lewis wrote:
>> Which brings us back to the need for hints even for very simple
>> queries -
>> when my
>> question was: "how do you do it without hinting".
> I don't think we differ conceptually.
> My point is that the hint should supply the missing information (e.g.
> selectivity) and not dictate the plan.
> It's still a hint.
Fair enough, but my comment was addressed to the argument that "the optimizer doesn't need hints".
Mind you, the "missing information" is a lot harder than your response to my first trivial example.
Take a 4 table join, with simple filter predicates and join predicates.
You may be able to say
selectivity(table1 xxx) ... selectivity(table4 yyyy) but then you need to be able to say
selectivity(table1,table2 nnnn) - because you know it's much smaller
than the optimizer things
but you also may have to say
because you know those are much larger than the optimizer things then you have to worry about three table selectivities to stop the picking the wrong third table .. and so on.
Or you might decide that you need to be able to say:
selectivity (t1, t2, based on colX = coly, selectivity) -- or something similar.
Ultimately it's much harder (and much less reasonable) for the DBA to decide what the right information is for a particular query than it is to know enough to determine a sensible path.
-- Regards Jonathan Lewis http://jonathanlewis.wordpress.comReceived on Mon Feb 14 2011 - 07:32:10 CST