From: Richard Huxton <dev@archonet.com>
To: Jeremy Haile <jhaile@fastmail.fm>
Cc: pgsql-performance@postgresql.org
Subject: Re: Inner join vs where-clause subquery
Date: Tue, 19 Dec 2006 18:23:06 +0000
Message-ID: <45882E0A.8070807@archonet.com> (raw)
In-Reply-To: <1166549705.21302.281208465@webmail.messagingengine.com>
References: <1166538737.31684.281175363@webmail.messagingengine.com>
<458813ED.50108@archonet.com>
<1166549705.21302.281208465@webmail.messagingengine.com>
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 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 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 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)
Hmm - it's using the indexes on dtCreateDate and nlogid which seems
broadly sensible, and then plans to limit the results for min()/max().
However, it's clearly wrong about how many rows will satisfy
nlogid > (select max(a.end_nlogid) from activity_log_import_history a)
>>> 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 you run explain on the other forms of your query, I'd guess it's much
more accurate. There's a simple way to see if that is the issue. Run the
sub-query and substitute the actual value returned into the query above.
Then, try the same but with a prepared query. If it's down to nlogid
estimates then the first should be fast and the second slow.
--
Richard Huxton
Archonet Ltd
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: dev@archonet.com, jhaile@fastmail.fm
Subject: Re: Inner join vs where-clause subquery
In-Reply-To: <45882E0A.8070807@archonet.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