Re: multicolumn index join

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!