Re: 8.1 -- very slow query time because of "BETWEEN" (dbmail)
От | Tom Lane |
---|---|
Тема | Re: 8.1 -- very slow query time because of "BETWEEN" (dbmail) |
Дата | |
Msg-id | 11548.1182483197@sss.pgh.pa.us обсуждение исходный текст |
Ответ на | Re: 8.1 -- very slow query time because of "BETWEEN" (dbmail) (Brian Neu <proclivity76@yahoo.com>) |
Ответы |
Re: 8.1 -- very slow query time because of "BETWEEN" (dbmail)
|
Список | pgsql-novice |
Brian Neu <proclivity76@yahoo.com> writes: > Ahhh. I knew troubleshooting this would lead to cool new discoveries and troubleshooting tools. I apologize if Yahoojacks the formatting up: It's still readable ... seems the core of the problem is here: > " -> Bitmap Heap Scan on dbmail_headervalue v (cost=84.09..15910.01 rows=7454 width=48) (actual time=13.653..13.678rows=17 loops=1)" > " Recheck Cond: (v.physmessage_id = "outer".physmessage_id)" > " -> Bitmap Index Scan on dbmail_headervalue_1 (cost=0.00..84.09 rows=7454 width=0) (actual time=13.589..13.589rows=17 loops=1)" > " Index Cond: (v.physmessage_id = "outer".physmessage_id)" In the slow case, the planner estimates it would have to do this scan 3 times not just once, when once is correct. (This is because range estimation is a bit fuzzier than equality estimation. Estimating 3 matching rows instead of 1 is still well within reasonable error though.) The problem is that it's estimating 7454 matching dbmail_headervalue rows per outer row, when the truth is only 17; and that results in a large overestimate of the cost of doing this scan, which convinces it that it doesn't want to do it more than once. So basically the trick here is to get that 7454 number closer to reality. Has this table been ANALYZEd lately? If so, could we see the pg_stats entry for the physmessage_id column? regards, tom lane
В списке pgsql-novice по дате отправления: