Re: Inner join vs where-clause subquery

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