I have to do a multicolumn self-join to find the median. I am using postgres
8.2. How do I force postgres to use an index scan? I have a multicolumn
index but postgres is not using it.
Query
------------------------------------
explain select e.time
from page_view e, page_view d
where e.test_run_id = d.test_run_id and e.web_resource_id =
d.web_resource_id
and e.web_resource_id = 3961 and e.test_run_id = 2
1 index
-----------------------------------------------------------------------
create index page_view_page_idx ON page_view(test_run_id, web_resource_id);
primar key
--------------------------------------
(test_run_id,page_view_id)
plan
--------------------------------------------------------------
"Nested Loop (cost=127.58..13592618.11 rows=33489369 width=8)"
" -> Seq Scan on page_view e (cost=0.00..3291.26 rows=5787 width=16)"
" Filter: ((web_resource_id = 3961) AND (test_run_id = 2))"
" -> Bitmap Heap Scan on page_view d (cost=127.58..2290.38 rows=5787
width=8)"
" Recheck Cond: ((3961 = web_resource_id) AND (2 = test_run_id))"
" -> Bitmap Index Scan on page_view_page_idx (cost=0.00..126.13
rows=5787 width=0)"
" Index Cond: ((3961 = web_resource_id) AND (2 = test_
Steve