Re: Why is explain horribly optimistic for sorts?

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!