Re: Inner join vs where-clause subquery

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" <dev(at)archonet(dot)com>
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