Message-ID: <45882E0A.8070807@archonet.com>
Date: Tue, 19 Dec 2006 18:23:06 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 1.5.0.8 (X11/20061115)
MIME-Version: 1.0
To: Jeremy Haile <jhaile@fastmail.fm>
Cc: pgsql-performance@postgresql.org
Subject: Re: Inner join vs where-clause subquery
References: <1166538737.31684.281175363@webmail.messagingengine.com>
	<458813ED.50108@archonet.com>
	<1166549705.21302.281208465@webmail.messagingengine.com>
In-Reply-To: <1166549705.21302.281208465@webmail.messagingengine.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Jeremy Haile wrote:
> Here is the explain analyze output:

Well, the row estimates are about as far out as you can get:

>           ->  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 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 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 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)

Hmm - it's using the indexes on dtCreateDate and nlogid which seems 
broadly sensible, and then plans to limit the results for min()/max(). 
However, it's clearly wrong about how many rows will satisfy
  nlogid > (select max(a.end_nlogid) from activity_log_import_history a)

>>> 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'

If you run explain on the other forms of your query, I'd guess it's much 
more accurate. There's a simple way to see if that is the issue. Run the 
sub-query and substitute the actual value returned into the query above. 
Then, try the same but with a prepared query. If it's down to nlogid 
estimates then the first should be fast and the second slow.

-- 
   Richard Huxton
   Archonet Ltd