Re: Optimizer picks an ineffient plan

Regardless of your argument, all I know is that SQL Server handles it
correctly (giving me the data in the fastest possible way) and postgresql
doesn't.
And your argument about how it will increase other queries is pointless to
me. Will you still stand by this argument when the PG folks find a situation
where the optimizer creates a terrible plan and the only way to fix is to
add additional logic, based on what you are saying, it's not worth it
because it could add .001 seconds of additional processing.
Again, like I said, if the PG folks don't see a need for this type of
optimization, then that's fine. What I'm stating is that SQLServer is a very
fast and robust database that also handles this, as you may call it, odd
situation.

Thanks

"Stephan Szabo" <sszabo(at)megazone(dot)bigpanda(dot)com> wrote in message
news:20030905071003(dot)P73820-100000(at)megazone(dot)bigpanda(dot)com(dot)(dot)(dot)
>
> On Thu, 4 Sep 2003, Relaxin wrote:
>
> > What the heck are you talking about?
>
> Whether or not trying every optimization is worthwhile in every potential
> situation where it might apply, in theory.
>
> > SQLServer returns the query immediately (milliseconds) where as PGSQL
> > returns 6 seconds!!
> > So, yes I would say that SQLServer did it correctly in this situation.
The
> > optimizer did what it was suppose to do...find the quickest way to get
me
> > the data.
>
> *If* you had other queries that took 6 seconds rather than milliseconds
> because it was trying to work out if the optimization applied, would you
> still think it was correct to be trying the optimization on those queries?
> What about if it added 50 ms to every query you did with an order by, and
> you never used redundant sort columns?
>
> > Now if you guys here a PGSQL don't believe it's necessary to change the
> > optimizer for this purpose, that's fine, but don't start putting other
DBMS
> > down because they DO handle this situation.
>
> Where did I put down another DBMS? (I've left my text below). I said (not
> in so many words), some optimizations take time, always doing every
> available optimization may not be "correct" because it may affect the
> running time of other queries adversely.
>
> I don't really know if this particular case warrents doing, I don't know
> if the time involved in checking it is entirely negligible or not for
> complicated queries. I do know that there are people who use order by
> however and do not use redundant sort columns and that if there is a
> non-negligible cost, they're the ones that are really going to be paying
> for it. Tom believed that the cost was non-negligible for its benefit, if
> someone else wants to do it and get numbers and the costs are negligible,
> it's likely to get put in.
>
> > "Stephan Szabo" <sszabo(at)megazone(dot)bigpanda(dot)com> wrote in message
> > news:20030904132334(dot)E44205-100000(at)megazone(dot)bigpanda(dot)com(dot)(dot)(dot)
> > > 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?
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>