Re: How to optimize monstrous query, sorts instead of
От | Rod Taylor |
---|---|
Тема | Re: How to optimize monstrous query, sorts instead of |
Дата | |
Msg-id | 1056543129.69651.82.camel@jester обсуждение исходный текст |
Ответ на | Re: How to optimize monstrous query, sorts instead of using index ("Michael Mattox" <michael.mattox@verideon.com>) |
Ответы |
Re: How to optimize monstrous query, sorts instead of
|
Список | pgsql-performance |
> Here's the output of explain analyze. The query typically takes 0-4 seconds > depending on the time frame. It's run very frequently especially to process > the nightly reports. The plan picked seems reasonable (estimated costs / tuples is close to actual). I think the biggest hit is this index scan. Thats a substantial cost to pull out less than a thousand lines: -> Index Scan using monitorstatusxmonitori on monitorstatusx ms (cost=0.00..4695.65 rows=880 width=83) (actual time=40.17..1868.12 rows=625 loops=1) Index Cond: ("outer".jdoidx = ms.monitorx) Filter: ((datex >= '2003-06-20 08:57:21.36'::timestamp without time zone) AND (datex <= '2003-06-29 08:57:21.36'::timestamp without time zone)) Are jdoidx and monitorx integers? You might try a multi-column index on (ms.monitorx, ms.datex). Are monitorx assigned roughly ordered by date? It must be, otherwise the sort step would not be so cheap (hardly any impact on the query -- see actual cost number). The multi-column index above should give you a bit of a boost. Depending on the data in the table, the index (ms.datex, monitorx) may give better results along with a single index on (ms.monitorx) as you currently have. It's not very likely though. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Вложения
В списке pgsql-performance по дате отправления: