Re: multicolumn index join

On Tue, May 13, 2008 at 9:27 AM, Stephen Ince <since@opendemand.com> 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.

While it's quite acceptable to force index usage during testing, it's
a bad idea to do so on a production server unless you have to.

From the psql command line do a "show all;" and look for the
enable_xxx settings.  Those allow you to do things like turn off seq
scans (actually it makes them really expensive so that they don't get
chosen, usually).

enable_indexscan
enable_seqscan

Are the two to look for.

> 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

I'd rewrite this as:

explain analyze
select e.time
from page_view e
join page_view d
on ((e.test_run_id, e.web_resource_id) = (d.test_run_id,d.web_resource_id))
where e.web_resource_id = 3961 and e.test_run_id = 2

Which makes it more readable.  Don't know if that syntax makes the
planner smarter or not.  Note the addition of analyze up there.  that
will help you know what's actually happening.