From: Jeremy Haile <jhaile@fastmail.fm>
To: pgsql-performance@postgresql.org
Subject: Inner join vs where-clause subquery
Date: Tue, 19 Dec 2006 09:32:17 -0500
Message-ID: <1166538737.31684.281175363@webmail.messagingengine.com> (raw)
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'
If I change the where clause to have the return value of the subquery it
runs very fast:
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 > 402123456
and dtCreateDate < '2006-12-18 9:10'
If I change the query to the following, it runs fast:
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
inner join ( select max(end_nlogid) as previous_nlogid from
activity_log_import_history) as a
on activity_log_facts.nlogid > a.previous_nlogid
where dtCreateDate < ${IMPORT_TIMESTAMP}
I am running PG 8.2. Why is that this the case? Shouldn't the query
planner be smart enough to know that the first query is the same as the
second and third? The inner query does not refer to any columns outside
of itself. I personally find the first query easiest to read and wish
it performed well.
Jeremy Haile
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jhaile@fastmail.fm
Subject: Re: Inner join vs where-clause subquery
In-Reply-To: <1166538737.31684.281175363@webmail.messagingengine.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox