Re: Optimizer picks an ineffient plan - Mailing list pgsql-general

From Peter Childs
Subject Re: Optimizer picks an ineffient plan
Date
Msg-id Pine.LNX.4.44.0309041123010.5333-100000@RedDragon.Childs
Whole thread Raw
In response to Re: Optimizer picks an ineffient plan  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
On Thu, 4 Sep 2003, Tom Lane wrote:

> Greg Stark <gsstark@mit.edu> writes:
> > Tom Lane <tgl@sss.pgh.pa.us> writes:
> >> Yes, that's the real crux of the matter.  Should the optimizer spend
> >> cycles on *every* query to detect cases where the user has written
> >> useless sort keys?  I've got grave doubts that it's a win.
>
> > Well I'm sure the same arguments were made 30 years ago about optimizing
> > compilers. But thankfully the optimizer-geeks won the argument.
>
> Um ... I *am* an optimizer-geek.  You can find my name in the credits
> for Bliss/11, which was the best optimizing compiler on the planet about
> thirty years ago.  I stand by my comment that there's a tradeoff between
> the potential gain from an optimization and the time spent to find it.
>
> PG is at a disadvantage compared to typical compilation scenarios, in
> that a compiler assumes its output will be executed many times, while
> SQL queries often are planned and then executed but once.  There's been
> some talk of working harder when planning a "prepared statement", but
> so far I've not seen very many places where I'd really want to alter
> the planner's behavior on that basis.
>

    An intresting point. Perhaps storing some stats on Views would
help. Maybe adding a cache facility for views would speed some things up.
I don't really see anything against storing stats on "Prepared
Statements" and Views like we do on Tables.
    Maybe indexs on View would be useful but keeping them uptodate
would be a hazard.

Peter Childs


pgsql-general by date:

Previous
From: Csaba Nagy
Date:
Subject: Re: Replaceing records
Next
From: "Shridhar Daithankar"
Date:
Subject: Re: Replaceing records