Re: Why is explain horribly optimistic for sorts?

You VACUUM and VACUUM ANALYZE regularly, right?

-Mitch

----- Original Message -----
From: "Ben" <bench(at)silentmedia(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Sent: Saturday, March 03, 2001 12:44 PM
Subject: Why is explain horribly optimistic for sorts?

> 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.
>
> Some background.....
>
> - We only have ~120,000 records.
> - The relevant parts of the table are:
>
> Table "jennyann"
> Attribute | Type | Modifier
> ----------------+-------------+----------
> ClientHost | text |
> LogTime | timestamp |
> target | text |
> host | text |
> Indices: jennyan_host_key,
> jennyann_clienthost_key,
> jennyann_logtime_key,
> jennyann_target_key
>
> - All indices are normal btrees.
> - ClientHost is (for the most part) an IP address.
>
> Here's what explain tells me:
>
> 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.
>
> Help? Please?
>
>
>
> ---------------------------(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
>