On May 13, 2008, at 5:27 PM, Stephen Ince wrote:
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
How does that get you the median? It just gives you all the records
in page_view e with the same (test_run_id, web_resource_id) tuple.
AFAICS you could leave out the entire self-join and get the same
results. No wonder it uses a seqscan...
Personally to find the median I'd use a scrolling cursor. Execute
your query, ordered by time, using a scrolling cursor; scroll to the
last row; determine the row_count from that; scroll back to half way
your result set now that you know what size it is.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,482bd864927661472788033!
