Re: How to optimize monstrous query, sorts instead of using index - Mailing list pgsql-performance
From | Rod Taylor |
---|---|
Subject | Re: How to optimize monstrous query, sorts instead of using index |
Date | |
Msg-id | 1056542059.69651.73.camel@jester Whole thread Raw |
In response to | How to optimize monstrous query, sorts instead of using index ("Michael Mattox" <michael.mattox@verideon.com>) |
Responses |
Re: How to optimize monstrous query, sorts instead of using index
|
List | pgsql-performance |
Is this 7.3.x? Can we see explain analyze output for the query? On Wed, 2003-06-25 at 07:46, Michael Mattox wrote: > 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 > DESC; > > 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 = > 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) > > 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? > > Thanks, > Michael > > > Michael Mattox > cunparis@yahoo.fr / http://www.advweb.com/michael > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Attachment
pgsql-performance by date: