Re: Inner join vs where-clause subquery

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