Re: Big disappointment with Postgres

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 14 Feb 2011 13:32:10 -0000
Message-ID: <OP6dnRafI4I9s8TQnZ2dnUVZ8v6dnZ2d_at_bt.com>

"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

    selectivity(table1,table3 mmmm)
    selectivity(table1,table4 mmmm)
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.com

Received on Mon Feb 14 2011 - 07:32:10 CST