Re: How to optimize monstrous query, sorts instead of

> Oh, and using tables in your where clause that aren't in the from clause
> is non-portable and often hides bugs:
>
> from monitorstatusx ms
> , monitorstatusitemx msi
> where monitorx.idx = 'M-TEST_1444-TEST_00_10560561260561463219352'
>
> Are you sure you sure you don't have any duplicated constraints by
> pulling information in from other tables that you don't need to?
> Removing some of those nested loops would make a significant impact to
> the results.

I didn't notice that before, thanks for pointing that out. I just tried
adding monitorx.idx to the select and it ended up making my query take
several minutes long. Any ideas how I can fix this and keep my performance?

new query:

veriguard=# explain select m.idx, 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 monitorx m, monitorstatusx
ms, monitorstatusitemx msi where m.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=1653384.42..1655402.97 rows=807418 width=826)
Sort Key: ms.datex
-> Hash Join (cost=820308.66..1112670.42 rows=807418 width=826)
Hash Cond: ("outer".monitorx = "inner".jdoidx)
-> Merge Join (cost=820132.71..1098364.65 rows=807418 width=780)
Merge Cond: ("outer".jdoidx = "inner".statusitemlistx)
-> Index Scan using monitorstatusitemx_pkey on
monitorstatusitemx msi (cost=0.00..247616.27 rows=6596084 width=665)
-> Sort (cost=820132.71..822151.59 rows=807554 width=115)
Sort Key:
monitorstatusitemlistd8ea58a5x.statusitemlistx
-> Hash Join (cost=461310.87..685820.13 rows=807554
width=115)
Hash Cond: ("outer".jdoidx =
"inner".statusitemsx)
-> Seq Scan on monitorstatusitemlistd8ea58a5x
(cost=0.00..104778.90 rows=6597190 width=16)
-> Hash (cost=447067.98..447067.98 rows=807554
width=99)
-> Merge Join (cost=0.00..447067.98
rows=807554 width=99)
Merge Cond: ("outer".jdoidx =
"inner".jdoidx)
-> Index Scan using
monitorstatusx_pkey on monitorstatusx ms (cost=0.00..272308.56 rows=811754
width=83)
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..146215.58 rows=6596680 width=16)
-> Hash (cost=172.22..172.22 rows=1493 width=46)
-> Nested Loop (cost=0.00..172.22 rows=1493 width=46)
-> Index Scan using monitorx_id_index on monitorx m
(cost=0.00..5.36 rows=1 width=38)
Index Cond: (idx =
'M-TEST_1444-TEST_00_10560561260561463219352'::character varying)
-> Seq Scan on monitorx (cost=0.00..151.93 rows=1493
width=8)
(23 rows)

old query:

veriguard=# explain 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=9590.52..9591.87 rows=541 width=788)
Sort Key: ms.datex
-> Nested Loop (cost=0.00..9565.97 rows=541 width=788)
-> Nested Loop (cost=0.00..7929.22 rows=541 width=123)
-> Nested Loop (cost=0.00..6292.48 rows=541 width=107)
-> Nested Loop (cost=0.00..4647.22 rows=544 width=91)
-> Index Scan using monitorx_id_index on
monitorx (cost=0.00..5.36 rows=1 width=8)
Index Cond: (idx =
'M-TEST_1444-TEST_00_10560561260561463219352'::character varying)
-> Index Scan using monitorstatusxmonitori on
monitorstatusx ms (cost=0.00..4630.29 rows=926 width=83)
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)
Index Cond: ("outer".jdoidx =
monitorstatus_statusitemsx.jdoidx)
-> Index Scan using monitorstatusitejdoid7db0befci on
monitorstatusitemlistd8ea58a5x (cost=0.00..3.01 rows=1 width=16)
Index Cond: ("outer".statusitemsx =
monitorstatusitemlistd8ea58a5x.jdoidx)
-> Index Scan using monitorstatusitemx_pkey on monitorstatusitemx
msi (cost=0.00..3.01 rows=1 width=665)
Index Cond: ("outer".statusitemlistx = msi.jdoidx)
(17 rows)

veriguard=#