Re: Inner join vs where-clause subquery - Mailing list pgsql-performance

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@archonet.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