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?
