Why is explain horribly optimistic for sorts?

From: Ben <bench(at)silentmedia(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Why is explain horribly optimistic for sorts?
Date: 2001-03-03 17:44:27
Message-ID: Pine.LNX.4.10.10103030927260.19743-100000@gilgamesh.eos.SilentMedia.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message will trillich 2001-03-03 17:57:26 Re: Why is explain horribly optimistic for sorts?
Previous Message Richard Huxton 2001-03-03 17:07:45 Connection/closedown triggers for backends?