Re: Why is explain horribly optimistic for sorts?

While this does get me results faster, it still takes a couple minutes,
and I don't see why this should be the case.

On Sat, 3 Mar 2001, will trillich wrote:

> On Sat, Mar 03, 2001 at 09:44:27AM -0800, Ben wrote:
> > Hello all. We are logging our web server traffic to postgresql 7.0.3, and
> > that's working well. What's not working so well is retrieving our data in
> > reasonable times if I try to order it. When I run our queries through
> > explain, it *looks* like they will run in reasonable times, but in fact
> > they take several minutes. That's not so good. I'm wondering why explain
> > is so horribly wrong when it comes to sorts? For that matter, I'm
> > wondering why sorts take so incredibly long.
>
> [snip]
>
> > explain SELECT * FROM jennyann where target like '/music/%' order by "LogTime" limit 1000;
> > NOTICE: QUERY PLAN:
> >
> > Sort (cost=119.88..119.88 rows=2085 width=136)
> > -> Index Scan using jennyann_target_key on jennyann (cost=0.00..4.94 rows=2085 width=136)
> >
> > A cost of 119 seems pretty good, and usually takes just a couple seconds
> > for other queries I've made. Unfortuantely, it's completely wrong. This
> > query takes several minutes to complete. If I drop the "order by" clause
> > then things get to be reasonable speeds, but I rather need that clause
> > there.
>
> <guessing>
> would this work?
>
> create temp table QRY as select * from jennyann where ...;
> create index ORD on qry("LogTime");
> select * from QRY order by "Logtime" limit 1000;
>
> if the select is fast but the order is slow, maybe this'd help?
> </guessing>
>
> --
> It is always hazardous to ask "Why?" in science, but it is often
> interesting to do so just the same.
> -- Isaac Asimov, 'The Genetic Code'
>
> will(at)serensoft(dot)com
> http://groups.yahoo.com/group/newbieDoc -- we need your brain!
> http://www.dontUthink.com/ -- your brain needs us!
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>