Re: Optimizer picks an ineffient plan

On Wed, 3 Sep 2003, Bupp Phillips wrote:

> Well, it's unfortunate that you feel that way, because SQL Server handles it
> correctly.

For some definition of correctly.  If you're in a system which gets
penalized .001 seconds for each query planning that uses a multi-column
order by and you do 100 million of them that this doesn't apply to, and
one that it does which save you 30 seconds, is that correct?


> "Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
> news:4375.1062643465@sss.pgh.pa.us...
> > Greg Stark <gsstark@mit.edu> writes:
> > > "Bupp Phillips" <hello@noname.com> writes:
> > >> select * from customer order by customer_id, first_name;
> > >> [ where customer_id is the primary key ]
> >
> > > However you do have a point. In this case I don't think postgres even
> > > considers using the index.
> >
> > It will not, since the index does not appear to provide the correct sort
> > order.
> >
> > > However I'm not sure I see a lot of cases where this would come up.
> >
> > 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.  ISTM such
> > an optimization penalizes the folk who write their queries well to
> > benefit those who are careless.
> >
> > regards, tom lane
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 8: explain analyze is your friend
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>