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 20:02:35 +0000
Message-ID: <4588455B.8040906@archonet.com> (raw)
In-Reply-To: <1166556890.1882.281227661@webmail.messagingengine.com>
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>
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
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: <4588455B.8040906@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