From: | Rod Taylor <rbt(at)rbt(dot)ca> |
---|---|
To: | michael(dot)mattox(at)verideon(dot)com |
Cc: | Postgresql Performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: How to optimize monstrous query, sorts instead of |
Date: | 2003-06-25 14:28:25 |
Message-ID: | 1056551304.25587.52.camel@jester |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
> 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?
By using it aliased and non-aliased (2 different references to the same
table) you've caused it to join itself.
Try this:
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 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-06-20 08:57:21.36'
AND '2003-06-29 08:57:21.36'
AND m.idx = 'M-TEST_1444-TEST_00_10560561260561463219352'
ORDER BY ms.datex DESC;
--
Rod Taylor <rbt(at)rbt(dot)ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Mattox | 2003-06-25 14:48:46 | Re: How to optimize monstrous query, sorts instead of using index |
Previous Message | SZUCS Gábor | 2003-06-25 14:20:32 | Re: How to optimize monstrous query, sorts instead of |