X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id 5678B30FB82 for ; Wed, 25 Jun 2003 11:46:55 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 28533-08 for ; Wed, 25 Jun 2003 08:46:44 -0300 (ADT) Received: from mwinf0301.wanadoo.fr (smtp6.wanadoo.fr [193.252.22.28]) by svr1.postgresql.org (Postfix) with ESMTP id 8629230FAD4 for ; Wed, 25 Jun 2003 08:46:44 -0300 (ADT) Received: from Paris (ABoulogne-110-1-3-168.w80-15.abo.wanadoo.fr [80.15.41.168]) by mwinf0301.wanadoo.fr (SMTP Server) with ESMTP id 135E840082D for ; Wed, 25 Jun 2003 13:46:44 +0200 (CEST) Reply-To: From: "Michael Mattox" To: "Postgresql Performance" Subject: How to optimize monstrous query, sorts instead of using index Date: Wed, 25 Jun 2003 13:46:48 +0200 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Importance: Normal X-Archive-Number: 200306/155 X-Sequence-Number: 2239 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