On Tue, May 13, 2008 at 9:27 AM, Stephen Ince <[EMAIL PROTECTED]> 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
