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 1BA7F30FB82 for ; Wed, 25 Jun 2003 12:00:48 +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 22193-10 for ; Wed, 25 Jun 2003 09:00:37 -0300 (ADT) Received: from mwinf0304.wanadoo.fr (smtp6.wanadoo.fr [193.252.22.28]) by svr1.postgresql.org (Postfix) with ESMTP id 1A59A30FAD4 for ; Wed, 25 Jun 2003 09:00:36 -0300 (ADT) Received: from Paris (unknown [80.15.41.168]) by mwinf0304.wanadoo.fr (SMTP Server) with ESMTP id 69DB3A80411A; Wed, 25 Jun 2003 14:00:35 +0200 (CEST) Reply-To: From: "Michael Mattox" To: "Rod Taylor" Cc: "Postgresql Performance" Subject: Re: How to optimize monstrous query, sorts instead of using index Date: Wed, 25 Jun 2003 14:00:39 +0200 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" 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 In-Reply-To: <1056542059.69651.73.camel@jester> X-Archive-Number: 200306/158 X-Sequence-Number: 2242 Sorry, I neglected to say the version, yes I'm using Postgres 7.3.2 on Linux. Here's the output of explain analyze. The query typically takes 0-4 seconds depending on the time frame. It's run very frequently especially to process the nightly reports. veriguard=# explain analyze 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; QUERY PLAN ---------------------------------------------------------------------------- ---------------------------------------------------------------------------- ---------------------------- Sort (cost=9498.96..9500.27 rows=525 width=788) (actual time=6720.91..6721.44 rows=623 loops=1) Sort Key: ms.datex -> Nested Loop (cost=0.00..9475.26 rows=525 width=788) (actual time=145.16..6718.65 rows=623 loops=1) -> Nested Loop (cost=0.00..7887.69 rows=525 width=123) (actual time=126.84..4528.85 rows=623 loops=1) -> Nested Loop (cost=0.00..6300.13 rows=525 width=107) (actual time=95.37..3470.55 rows=623 loops=1) -> Nested Loop (cost=0.00..4712.13 rows=525 width=91) (actual time=40.44..1892.06 rows=625 loops=1) -> Index Scan using monitorx_id_index on monitorx (cost=0.00..5.48 rows=1 width=8) (actual time=0.25..19.90 rows=1 loops=1) 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) (actual time=40.17..1868.12 rows=625 loops=1) 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) (actual time=2.51..2.51 rows=1 loops=625) Index Cond: ("outer".jdoidx = monitorstatus_statusitemsx.jdoidx) -> Index Scan using monitorstatusitejdoid7db0befci on monitorstatusitemlistd8ea58a5x (cost=0.00..3.01 rows=1 width=16) (actual time=1.68..1.69 rows=1 loops=623) Index Cond: ("outer".statusitemsx = monitorstatusitemlistd8ea58a5x.jdoidx) -> Index Scan using monitorstatusitemx_pkey on monitorstatusitemx msi (cost=0.00..3.01 rows=1 width=665) (actual time=3.50..3.50 rows=1 loops=623) Index Cond: ("outer".statusitemlistx = msi.jdoidx) Total runtime: 6722.43 msec (18 rows)