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

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