Inner join vs where-clause subquery

Поиск
Список
Период
Сортировка
От Jeremy Haile
Тема Inner join vs where-clause subquery
Дата
Msg-id 1166538737.31684.281175363@webmail.messagingengine.com
обсуждение исходный текст
Ответы Re: Inner join vs where-clause subquery  (Richard Huxton <dev@archonet.com>)
Список pgsql-performance
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

В списке pgsql-performance по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Insertion to temp table deteriorating over time
Следующее
От: "Steven Flatt"
Дата:
Сообщение: Re: Insertion to temp table deteriorating over time