Message-ID: <4588455B.8040906@archonet.com>
Date: Tue, 19 Dec 2006 20:02:35 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 1.5.0.8 (X11/20061115)
MIME-Version: 1.0
To: Jeremy Haile <jhaile@fastmail.fm>
Cc: pgsql-performance@postgresql.org
Subject: Re: Inner join vs where-clause subquery
References: <1166538737.31684.281175363@webmail.messagingengine.com>
	<458813ED.50108@archonet.com>
	<1166549705.21302.281208465@webmail.messagingengine.com>
	<45882E0A.8070807@archonet.com>
	<1166556890.1882.281227661@webmail.messagingengine.com>
In-Reply-To: <1166556890.1882.281227661@webmail.messagingengine.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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_transaction_timestamp
> from activity_log_facts
> where nlogid > 478287801
> and dtCreateDate < '2006-12-18 9:10'
> 
> EXPLAIN ANALYZE
> Aggregate  (cost=657.37..657.38 rows=1 width=12) (actual
> time=0.018..0.019 rows=1 loops=1)
>   ->  Index Scan using activity_log_facts_nlogid_idx on
>   activity_log_facts  (cost=0.00..652.64 rows=472 width=12) (actual
>   time=0.014..0.014 rows=0 loops=1)
>         Index Cond: (nlogid > 478287801)
>         Filter: (dtcreatedate < '2006-12-18 09:10:00'::timestamp without
>         time zone)
> Total runtime: 0.076 ms
> 
> 
> Sorry if the reason should be obvious, but I'm not the best at
> interpreting the explains.  Why is this explain so much simpler than the
> other query plan (with the subquery)?

Because it's planning it with knowledge of what "nlogid"s it's filtering 
by. It knows it isn't going to get many rows back with nlogid > 
478287801. In your previous explain it thought a large number of rows 
would match and was trying not to sequentially scan the 
activity_log_facts table.

Ideally, the planner would evaluate the subquery in your original form 
(it should know it's only getting one row back from max()). Then it 
could plan the query as above. I'm not sure how tricky that is to do though.

-- 
   Richard Huxton
   Archonet Ltd