Re: separating improperly grouped page views - Mailing list pgsql-sql

On Mon, 18 Jun 2007, Tom Lane wrote:

> Jeff Frost <jeff@frostconsultingllc.com> writes:
>> [ umpteen million iterations of: ]
>>               ->  Limit  (cost=0.00..367.09 rows=1 width=8)
>>                     ->  Index Scan Backward using page_view_stamp_idx on page_view pv2  (cost=0.00..158215.86
rows=431width=8)
 
>>                           Index Cond: (stamp < $1)
>>                           Filter: ((stamp IS NOT NULL) AND (visit_id = $0))
>
> Perhaps an index on (visit_id, stamp) would help.  This one is doing the
> best it can, but if the visit_id's you want are thinly scattered, it'll
> still suck...

Good idea Tom!  In fact the planner seems to like that much better:
 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)
                                                             QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------
SeqScan on page_view pv1  (cost=0.00..11529031.34 rows=3580205 width=239)   SubPlan     ->  Result  (cost=1.58..1.59
rows=1width=0)           InitPlan             ->  Limit  (cost=0.00..1.58 rows=1 width=8)                   ->  Index
ScanBackward 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:



-------------------------------------------------------------------------------------------------------------------------------
SeqScan on page_view pv1  (cost=0.00..2622541458.55 rows=3596473 width=237)   SubPlan     ->  Result
(cost=364.56..364.57rows=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=421width=8)                         Index Cond: (stamp < $1)                         Filter: ((stamp IS NOT NULL)
AND(visit_id = $0))
 
(14 rows)



-- 
Jeff Frost, Owner     <jeff@frostconsultingllc.com>
Frost Consulting, LLC     http://www.frostconsultingllc.com/
Phone: 650-780-7908    FAX: 650-649-1954