multicolumn index join - Mailing list pgsql-general

From Stephen Ince
Subject multicolumn index join
Date
Msg-id 12d001c8b50d$ea91b8c0$6e00a8c0@desktop2
Whole thread Raw
In response to Stripping out slony after / before / during pg_restore?  (Glyn Astill <glynastill@yahoo.co.uk>)
Responses Re: multicolumn index join
Re: multicolumn index join
List pgsql-general
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


pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Making sure \timing is on
Next
From: "Merlin Moncure"
Date:
Subject: Re: PG -v- MySQL