Re: How to optimize monstrous query, sorts instead of using index

Sorry, I neglected to say the version, yes I'm using Postgres 7.3.2 on
Linux.

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.

veriguard=# explain analyze select ms.averageconnecttimex as
ms_averageconnecttime, ms.averagedurationx as ms_averageduration, ms.datex
as ms_date, ms.idx as ms_id, ms.statusstringx as ms_statusstring, ms.statusx
as ms_status, msi.actualcontentx as msi_actualcontent, msi.connecttimex as
msi_connecttime, msi.correctcontentx as msi_correctcontent, msi.datex as
msi_date, msi.descriptionx as msi_description, msi.durationx as
msi_duration, msi.errorcontentx as msi_errorcontent, msi.idx as msi_id,
msi.monitorlocationx as msi_monitorlocation, msi.statusstringx as
msi_statusstring, msi.statusx as msi_status from monitorstatusx ms,
monitorstatusitemx msi where monitorx.idx =
'M-TEST_1444-TEST_00_10560561260561463219352' AND monitorx.jdoidx =
ms.monitorx AND ms.datex >= '2003-06-20 08:57:21.36' AND ms.datex <=
'2003-06-29 08:57:21.36' AND ms.jdoidx = monitorstatus_statusitemsx.jdoidx
AND monitorstatus_statusitemsx.statusitemsx =
monitorstatusitemlistd8ea58a5x.jdoidx AND
monitorstatusitemlistd8ea58a5x.statusitemlistx = msi.jdoidx ORDER BY ms_date
DESC;

QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------
Sort (cost=9498.96..9500.27 rows=525 width=788) (actual
time=6720.91..6721.44 rows=623 loops=1)
Sort Key: ms.datex
-> Nested Loop (cost=0.00..9475.26 rows=525 width=788) (actual
time=145.16..6718.65 rows=623 loops=1)
-> Nested Loop (cost=0.00..7887.69 rows=525 width=123) (actual
time=126.84..4528.85 rows=623 loops=1)
-> Nested Loop (cost=0.00..6300.13 rows=525 width=107)
(actual time=95.37..3470.55 rows=623 loops=1)
-> Nested Loop (cost=0.00..4712.13 rows=525 width=91)
(actual time=40.44..1892.06 rows=625 loops=1)
-> Index Scan using monitorx_id_index on
monitorx (cost=0.00..5.48 rows=1 width=8) (actual time=0.25..19.90 rows=1
loops=1)
Index Cond: (idx =
'M-TEST_1444-TEST_00_10560561260561463219352'::character varying)
-> 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))
-> Index Scan using monitorstatus_stjdoidb742c9b3i on
monitorstatus_statusitemsx (cost=0.00..3.01 rows=1 width=16) (actual
time=2.51..2.51 rows=1 loops=625)
Index Cond: ("outer".jdoidx =
monitorstatus_statusitemsx.jdoidx)
-> Index Scan using monitorstatusitejdoid7db0befci on
monitorstatusitemlistd8ea58a5x (cost=0.00..3.01 rows=1 width=16) (actual
time=1.68..1.69 rows=1 loops=623)
Index Cond: ("outer".statusitemsx =
monitorstatusitemlistd8ea58a5x.jdoidx)
-> Index Scan using monitorstatusitemx_pkey on monitorstatusitemx
msi (cost=0.00..3.01 rows=1 width=665) (actual time=3.50..3.50 rows=1
loops=623)
Index Cond: ("outer".statusitemlistx = msi.jdoidx)
Total runtime: 6722.43 msec
(18 rows)