Re: [PERFORM] Inner join vs where-clause subquery

I'm still confused as to why the inner join version ran so much faster than the where-clause version. Here's the inner join query and explain ouput: select min(nlogid) as start_nlogid, max(nlogid) as end_nlogid, min(dtCreateDate) as start_transaction_timestamp, max(dtCreateD

Re: [PERFORM] Inner join vs where-clause subquery

Makes sense. It is NOT executing the subquery more than once is it? On Tue, 19 Dec 2006 20:02:35 +, "Richard Huxton" said: > Jeremy Haile wrote: > > Here's the query and explain analyze using the result of the sub-query > > substituted: > > > > QUERY > > explain analyze select min(nlogid)

Re: [PERFORM] Inner join vs where-clause subquery

Jeremy Haile wrote: Here's the query and explain analyze using the result of the sub-query substituted: QUERY explain analyze select min(nlogid) as start_nlogid, max(nlogid) as end_nlogid, min(dtCreateDate) as start_transaction_timestamp, max(dtCreateDate) as end_transacti

Re: [PERFORM] Inner join vs where-clause subquery

Here's the query and explain analyze using the result of the sub-query substituted: QUERY explain analyze select min(nlogid) as start_nlogid, max(nlogid) as end_nlogid, min(dtCreateDate) as start_transaction_timestamp, max(dtCreateDate) as end_transaction_timestamp from activ

Re: [PERFORM] 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

Re: [PERFORM] 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

Re: [PERFORM] Inner join vs where-clause subquery

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

[PERFORM] Inner join vs where-clause subquery

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_n