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@megazone.bigpanda.com> wrote in message
> news:20030904132334.E44205-100000@megazone.bigpanda.com...
> > 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?
