Re: separating improperly grouped page views - Mailing 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