Re: Why is explain horribly optimistic for sorts?

You VACUUM and VACUUM ANALYZE regularly, right?

-Mitch

----- Original Message -----
From: "Ben" <bench@silentmedia.com>
To: <pgsql-general@postgresql.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@postgresql.org so that your
> message can get through to the mailing list cleanly
>