Re: separating improperly grouped page views - Mailing list pgsql-sql
From | Jeff Frost |
---|---|
Subject | Re: separating improperly grouped page views |
Date | |
Msg-id | Pine.LNX.4.64.0706191018050.24164@glacier.frostconsultingllc.com Whole thread Raw |
In response to | Re: separating improperly grouped page views (Jeff Frost <jeff@frostconsultingllc.com>) |
List | pgsql-sql |
On Sun, 17 Jun 2007, Jeff Frost wrote: > ------------------------------------------------------------------------------------------------------------------------------------- > Seq Scan on page_view pv1 (cost=0.00..11529031.34 rows=3580205 width=239) > SubPlan > -> Result (cost=1.58..1.59 rows=1 width=0) > InitPlan > -> Limit (cost=0.00..1.58 rows=1 width=8) > -> Index Scan Backward using page_view_visit_id_stamp_idx > on page_view pv2 (cost=0.00..625.39 rows=397 width=8) > Index Cond: ((visit_id = $0) AND (stamp < $1)) > Filter: (stamp IS NOT NULL) > -> Result (cost=1.58..1.59 rows=1 width=0) > InitPlan > -> Limit (cost=0.00..1.58 rows=1 width=8) > -> Index Scan Backward using page_view_visit_id_stamp_idx > on page_view pv2 (cost=0.00..625.39 rows=397 width=8) > Index Cond: ((visit_id = $0) AND (stamp < $1)) > Filter: (stamp IS NOT NULL) > (14 rows) > > Compared to: > > > ------------------------------------------------------------------------------------------------------------------------------- > Seq Scan on page_view pv1 (cost=0.00..2622541458.55 rows=3596473 width=237) > SubPlan > -> Result (cost=364.56..364.57 rows=1 width=0) > InitPlan > -> Limit (cost=0.00..364.56 rows=1 width=8) > -> Index Scan Backward using page_view_stamp_idx on > page_view pv2 (cost=0.00..153481.58 rows=421 width=8) > Index Cond: (stamp < $1) > Filter: ((stamp IS NOT NULL) AND (visit_id = $0)) > -> Result (cost=364.56..364.57 rows=1 width=0) > InitPlan > -> Limit (cost=0.00..364.56 rows=1 width=8) > -> Index Scan Backward using page_view_stamp_idx on > page_view pv2 (cost=0.00..153481.58 rows=421 width=8) > Index Cond: (stamp < $1) > Filter: ((stamp IS NOT NULL) AND (visit_id = $0)) > (14 rows) And throwing the ORDER BY back in reduces the cost even more! QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------- SubqueryScan x (cost=0.00..5815824.15 rows=3629753 width=1186) -> Index Scan using page_view_visit_idx on page_view pv1 (cost=0.00..5743229.09 rows=3629753 width=237) SubPlan -> Result (cost=1.51..1.52 rows=1 width=0) InitPlan -> Limit (cost=0.00..1.51 rows=1 width=8) -> Index Scan Backward using page_view_visit_id_stamp_idx on page_view pv2 (cost=0.00..608.41 rows=402 width=8) Index Cond: ((visit_id = $0) AND (stamp < $1)) Filter: (stamp IS NOTNULL) (9 rows) Now we only have to do that index scan once. :-) I had foolishly taken that out to see if the sort was killing me and forgot to put it back in. So now it's: Subquery Scan x (cost=0.00..5815824.15 rows=3629753 width=1186) vs Seq Scan on page_view pv1 (cost=0.00..11529031.34 rows=3580205 width=239) vs Seq Scan on page_view pv1 (cost=0.00..2622541458.55 rows=3596473 width=237) -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954