Re: How to optimize monstrous query, sorts instead of using index - Mailing list pgsql-performance

> 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=#