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

> Finally, decided to do an ad-hoc adjustment. Try this, or (wild guess) try
> to completely eliminate the WHERE part by subselects on ms and monitorx.
>
> This may be faster, slower, or even give different results, based
> on whether
> I guessed the 1:N relationships right or not.

It's much slower but I appreciate you taking the time to try. I'm pretty
new to SQL so I must admin this query is very confusing for me. I'm using
Java Data Objects (JDO, an O/R mapping framework) but the implementation I'm
using (Kodo) isn't smart enough to do all the joins efficiently, which is
why I had to rewrite this query by hand.

Here's the output:

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 LEFT JOIN
monitorx ON (monitorx.jdoidx = ms.monitorx) LEFT JOIN
monitorstatus_statusitemsx ms_si ON (ms.jdoidx = ms_si.jdoidx) LEFT JOIN
monitorstatusitemlistd8ea58a5x msil ON (ms_si.statusitemsx = msil.jdoidx)
LEFT JOIN monitorstatusitemx msi ON (msil.statusitemlistx = msi.jdoidx)
where monitorx.idx = 'M-TEST_1444-TEST_00_10560561260561463219352' AND
ms.datex >= '2003-06-20 08:57:21.36' AND ms.datex <= '2003-06-29
08:57:21.36';

QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
-------------------------------------
Merge Join (cost=1006209.47..1283529.68 rows=751715 width=826)
Merge Cond: ("outer".jdoidx = "inner".statusitemlistx)
-> Index Scan using monitorstatusitemx_pkey on monitorstatusitemx msi
(cost=0.00..247679.64 rows=6595427 width=665)
-> Sort (cost=1006209.47..1008088.76 rows=751715 width=161)
Sort Key: msil.statusitemlistx
-> Merge Join (cost=697910.17..864079.59 rows=751715 width=161)
Merge Cond: ("outer".jdoidx = "inner".statusitemsx)
-> Index Scan using monitorstatusitejdoid7db0befci on
monitorstatusitemlistd8ea58a5x msil (cost=0.00..136564.80 rows=6595427
width=16)
-> Sort (cost=697910.17..699789.46 rows=751715 width=145)
Sort Key: ms_si.statusitemsx
-> Merge Join (cost=385727.49..561594.96 rows=751715
width=145)
Merge Cond: ("outer".jdoidx = "inner".jdoidx)
-> Index Scan using
monitorstatus_stjdoidb742c9b3i on monitorstatus_statusitemsx ms_si
(cost=0.00..146268.80 rows=6595427 width=16)
-> Sort (cost=385727.49..387606.78 rows=751715
width=129)
Sort Key: ms.jdoidx
-> Hash Join (cost=155.66..255240.65
rows=751715 width=129)
Hash Cond: ("outer".monitorx =
"inner".jdoidx)
Filter: ("inner".idx =
'M-TEST_1444-TEST_00_10560561260561463219352'::character varying)
-> Seq Scan on monitorstatusx ms
(cost=0.00..240050.69 rows=751715 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))
-> Hash (cost=151.93..151.93
rows=1493 width=46)
-> Seq Scan on monitorx
(cost=0.00..151.93 rows=1493 width=46)
(22 rows)

veriguard=#