multicolumn index join

From: "Stephen Ince" <since(at)opendemand(dot)com>
To: "Postgres General List" <pgsql-general(at)postgresql(dot)org>
Subject: multicolumn index join
Date: 2008-05-13 15:27:54
Message-ID: 12d001c8b50d$ea91b8c0$6e00a8c0@desktop2
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Justin 2008-05-13 15:30:48 Re: rounding problems
Previous Message David Wall 2008-05-13 15:27:47 Re: pg_standby / WAL archive-restore through system restarts