Re: How to optimize monstrous query, sorts instead of

With a slight correction (you had m & mx so I changed them to be all mx, I
hope this is what you intended) this query works.  It's exactly the same
speed, but it doesn't give me the warnings I was getting:

NOTICE:  Adding missing FROM-clause entry for table "monitorx"
NOTICE:  Adding missing FROM-clause entry for table
"monitorstatus_statusitemsx"
NOTICE:  Adding missing FROM-clause entry for table
"monitorstatusitemlistd8ea58a5x"

I never knew what those were from, I even searched Google trying to find out
and I couldn't understand it so I gave up.  Thanks for pointing this out for
me, and thanks for fixing my query.

Michael


> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Rod Taylor
> Sent: Wednesday, June 25, 2003 4:28 PM
> To: michael.mattox@verideon.com
> Cc: Postgresql Performance
> Subject: Re: [PERFORM] How to optimize monstrous query, sorts instead of
>
>
> > 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@rbt.ca>
>
> PGP Key: http://www.rbt.ca/rbtpub.asc
>