From: | Rod Taylor <rbt(at)rbt(dot)ca> |
---|---|
To: | michael(dot)mattox(at)verideon(dot)com |
Cc: | Postgresql Performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: How to optimize monstrous query, sorts instead of |
Date: | 2003-06-25 12:12:09 |
Message-ID: | 1056543129.69651.82.camel@jester |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)rbt(dot)ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Mattox | 2003-06-25 12:48:15 | Re: How to optimize monstrous query, sorts instead of |
Previous Message | Michael Mattox | 2003-06-25 12:00:39 | Re: How to optimize monstrous query, sorts instead of using index |