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

> Try this:

Rod, you improved my query last week (thank you very much) but I'm not sure
why but my performance is getting worse.  I think I know what happened, when
I did my load testing I created data that all had the same date, so sorting
on the date was very fast.  But now I've been running the system for a few
weeks I've got a range of dates and now the sort is very costly.  I'm
curious if it's possible to optimize this with an index?  I've tried
creating some indexes but they're never used.

explain analyze SELECT mx.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 monitorstatusx AS ms , monitorstatusitemx AS msi , monitorx
AS mx , monitorstatus_statusitemsx AS mssisx ,
monitorstatusitemlistd8ea58a5x AS litem WHERE ms.jdoidx = mssisx.jdoidx AND
mssisx.statusitemsx = litem.jdoidx AND litem.statusitemlistx = msi.jdoidx
AND mx.jdoidx = ms.monitorx AND ms.datex BETWEEN '2003-07-01
00:00:00.000000+01' AND '2003-07-01 23:59:59.000000+01' AND mx.idx =
'M-TEST_150-TEST_01_10560776551771895174239' ORDER BY ms.datex DESC;


QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
-----------------------------------
 Sort  (cost=6882.84..6883.08 rows=97 width=827) (actual
time=16712.46..16712.65 rows=225 loops=1)
   Sort Key: ms.datex
   ->  Nested Loop  (cost=0.00..6879.66 rows=97 width=827) (actual
time=4413.12..16711.62 rows=225 loops=1)
         ->  Nested Loop  (cost=0.00..6587.53 rows=97 width=162) (actual
time=4406.06..15941.16 rows=225 loops=1)
               ->  Nested Loop  (cost=0.00..6295.38 rows=97 width=146)
(actual time=4383.59..15424.96 rows=225 loops=1)
                     ->  Nested Loop  (cost=0.00..6003.22 rows=97 width=130)
(actual time=4383.53..14938.02 rows=225 loops=1)
                           ->  Index Scan using monitorx_id_index on
monitorx mx  (cost=0.00..5.01 rows=1 width=46) (actual time=0.13..0.21
rows=1 loops=1)
                                 Index Cond: (idx =
'M-TEST_150-TEST_01_10560776551771895174239'::character varying)
                           ->  Index Scan using monitorstatusxmonitori on
monitorstatusx ms  (cost=0.00..5996.18 rows=163 width=84) (actual
time=4383.38..14936.39 rows=225 loops=1)
                                 Index Cond: ("outer".jdoidx = ms.monitorx)
                                 Filter: ((datex >= '2003-07-01
00:00:00'::timestamp without time zone) AND (datex <= '2003-07-01
23:59:59'::timestamp without time zone))
                     ->  Index Scan using monitorstatus_stjdoidb742c9b3i on
monitorstatus_statusitemsx mssisx  (cost=0.00..3.01 rows=1 width=16) (actual
time=2.15..2.15 rows=1 loops=225)
                           Index Cond: ("outer".jdoidx = mssisx.jdoidx)
               ->  Index Scan using monitorstatusitejdoid7db0befci on
monitorstatusitemlistd8ea58a5x litem  (cost=0.00..3.01 rows=1 width=16)
(actual time=2.28..2.28 rows=1 loops=225)
                     Index Cond: ("outer".statusitemsx = litem.jdoidx)
         ->  Index Scan using monitorstatusitemx_pkey on monitorstatusitemx
msi  (cost=0.00..3.01 rows=1 width=665) (actual time=3.41..3.41 rows=1
loops=225)
               Index Cond: ("outer".statusitemlistx = msi.jdoidx)
 Total runtime: 16713.25 msec
(18 rows)

As you can see it takes 16 seconds to return only 18 rows.  The
monitorstatusx table has over 7 million rows, and for each monitor status
there's one row in each of the monitorstatusitemx and the join tables.  So I
think the size of the database is just too high for this sort.  I run my
reports offline, but what I'm finding is that at 16 seconds per report, the
reports aren't finished by morning.  My postgresql.conf is attached in case
I have it configured incorrectly.

Thanks,
Michael