I've used indexes to speed up my queries but this query escapes me.  I'm
curious if someone can suggest an index or a way to modify the query to use
the index.  The query is:

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

Here is the result of explain:

 Sort  (cost=9498.85..9500.16 rows=525 width=788)
   Sort Key: ms.datex
   ->  Nested Loop  (cost=0.00..9475.15 rows=525 width=788)
         ->  Nested Loop  (cost=0.00..7887.59 rows=525 width=123)
               ->  Nested Loop  (cost=0.00..6300.03 rows=525 width=107)
                     ->  Nested Loop  (cost=0.00..4712.02 rows=525 width=91)
                           ->  Index Scan using monitorx_id_index on
monitorx  (cost=0.00..5.37 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..4695.65 rows=880 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 =
               ->  Index Scan using monitorstatusitejdoid7db0befci on
monitorstatusitemlistd8ea58a5x  (cost=0.00..3.01 rows=1 width=16)
                     Index Cond: ("outer".statusitemsx =
         ->  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)

As you can see, it's doing a sort on ms.datex.  I created an index on the
monitorstatusx (ms) table for the datex, but it doesn't use it.  Is it
possible to create an index to prevent this sort?


Michael Mattox
[EMAIL PROTECTED] / http://www.advweb.com/michael

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [EMAIL PROTECTED] so that your
      message can get through to the mailing list cleanly

Reply via email to