Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id C07379F9FB2 for ; Tue, 19 Dec 2006 13:35:15 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 31500-07 for ; Tue, 19 Dec 2006 13:35:10 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from out4.smtp.messagingengine.com (out4.smtp.messagingengine.com [66.111.4.28]) by postgresql.org (Postfix) with ESMTP id B7D0E9F9F08 for ; Tue, 19 Dec 2006 13:35:10 -0400 (AST) Received: from out1.internal (unknown [10.202.2.149]) by out1.messagingengine.com (Postfix) with ESMTP id 914795520A; Tue, 19 Dec 2006 12:35:05 -0500 (EST) Received: from web1.messagingengine.com ([10.202.2.210]) by out1.internal (MEProxy); Tue, 19 Dec 2006 12:35:05 -0500 Received: by web1.messagingengine.com (Postfix, from userid 99) id 72E1328BDE; Tue, 19 Dec 2006 12:35:05 -0500 (EST) Message-Id: <1166549705.21302.281208465@webmail.messagingengine.com> X-Sasl-Enc: S4jOQgB+egAEbUY0VxlkBI/lAw134Ymr1u2IDGeXVd8B 1166549705 From: "Jeremy Haile" To: "Richard Huxton" Cc: pgsql-performance@postgresql.org Content-Disposition: inline Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset="ISO-8859-1" MIME-Version: 1.0 X-Mailer: MessagingEngine.com Webmail Interface References: <1166538737.31684.281175363@webmail.messagingengine.com> <458813ED.50108@archonet.com> Subject: Re: Inner join vs where-clause subquery In-Reply-To: <458813ED.50108@archonet.com> Date: Tue, 19 Dec 2006 12:35:05 -0500 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/477 X-Sequence-Number: 22533 Here is the explain analyze output: Result (cost=9.45..9.46 rows=1 width=0) (actual time=156589.390..156589.391 rows=1 loops=1) InitPlan -> Result (cost=0.04..0.05 rows=1 width=0) (actual time=0.034..0.034 rows=1 loops=1) InitPlan -> Limit (cost=0.00..0.04 rows=1 width=4) (actual time=0.029..0.030 rows=1 loops=1) -> Index Scan Backward using activity_log_import_history_end_nlogid_idx on activity_log_import_history a (cost=0.00..113.43 rows=2877 width=4) (actual time=0.027..0.027 rows=1 loops=1) Filter: (end_nlogid IS NOT NULL) -> Limit (cost=0.00..1.19 rows=1 width=12) (actual time=0.052..0.052 rows=0 loops=1) -> Index Scan using activity_log_facts_pkey on activity_log_facts (cost=0.00..1831613.82 rows=1539298 width=12) (actual time=0.050..0.050 rows=0 loops=1) Index Cond: (nlogid > $1) Filter: ((nlogid IS NOT NULL) AND (dtcreatedate < '2006-12-18 09:10:00'::timestamp without time zone)) -> Limit (cost=0.00..1.19 rows=1 width=12) (actual time=0.006..0.006 rows=0 loops=1) -> Index Scan Backward using activity_log_facts_pkey on activity_log_facts (cost=0.00..1831613.82 rows=1539298 width=12) (actual time=0.004..0.004 rows=0 loops=1) Index Cond: (nlogid > $1) Filter: ((nlogid IS NOT NULL) AND (dtcreatedate < '2006-12-18 09:10:00'::timestamp without time zone)) -> Limit (cost=0.00..3.51 rows=1 width=12) (actual time=100221.955..100221.955 rows=0 loops=1) -> Index Scan using activity_log_facts_dtcreatedate_idx on activity_log_facts (cost=0.00..5406927.50 rows=1539298 width=12) (actual time=100221.953..100221.953 rows=0 loops=1) Index Cond: (dtcreatedate < '2006-12-18 09:10:00'::timestamp without time zone) Filter: ((dtcreatedate IS NOT NULL) AND (nlogid > $1)) -> Limit (cost=0.00..3.51 rows=1 width=12) (actual time=56367.367..56367.367 rows=0 loops=1) -> Index Scan Backward using activity_log_facts_dtcreatedate_idx on activity_log_facts (cost=0.00..5406927.50 rows=1539298 width=12) (actual time=56367.364..56367.364 rows=0 loops=1) Index Cond: (dtcreatedate < '2006-12-18 09:10:00'::timestamp without time zone) Filter: ((dtcreatedate IS NOT NULL) AND (nlogid > $1)) Total runtime: 156589.605 ms On Tue, 19 Dec 2006 16:31:41 +0000, "Richard Huxton" said: > Jeremy Haile wrote: > > I have the following query which performs extremely slow: > > select min(nlogid) as start_nlogid, > > max(nlogid) as end_nlogid, > > min(dtCreateDate) as start_transaction_timestamp, > > max(dtCreateDate) as end_transaction_timestamp > > from activity_log_facts > > where nlogid > ( select max(a.end_nlogid) from > > activity_log_import_history a) > > and dtCreateDate < '2006-12-18 9:10' > > Can you post the EXPLAIN ANALYSE for this one please? That'll show us > exactly what it's doing. > > -- > Richard Huxton > Archonet Ltd