On Mon, 18 Jun 2007, Tom Lane wrote:
Jeff Frost <[EMAIL PROTECTED]> 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=431 width=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
-------------------------------------------------------------------------------------------------------------------------------------
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)
--
Jeff Frost, Owner <[EMAIL PROTECTED]>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
