Why is explain horribly optimistic for sorts?
От | Ben |
---|---|
Тема | Why is explain horribly optimistic for sorts? |
Дата | |
Msg-id | Pine.LNX.4.10.10103030927260.19743-100000@gilgamesh.eos.SilentMedia.com обсуждение исходный текст |
Ответы |
Re: Why is explain horribly optimistic for sorts?
Re: Why is explain horribly optimistic for sorts? |
Список | 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?
В списке pgsql-general по дате отправления: