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
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 |