Re: Why is explain horribly optimistic for sorts?
От | Mitch Vincent |
---|---|
Тема | Re: Why is explain horribly optimistic for sorts? |
Дата | |
Msg-id | 001901c0a40c$6c84f6f0$0200000a@windows обсуждение исходный текст |
Ответ на | Why is explain horribly optimistic for sorts? (Ben <bench@silentmedia.com>) |
Ответы |
Re: Why is explain horribly optimistic for sorts?
|
Список | pgsql-general |
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 >
В списке pgsql-general по дате отправления: