Makes sense. It is NOT executing the subquery more than once is it? On Tue, 19 Dec 2006 20:02:35 +0000, "Richard Huxton" <dev@xxxxxxxxxxxx> said: > 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
