Can't get planner to use multicolumn index on large table - Mailing list pgsql-general

From Ulrich Meis
Subject Can't get planner to use multicolumn index on large table
Date
Msg-id 27306.1101299584@www1.gmx.net
Whole thread Raw
Responses Re: Can't get planner to use multicolumn index on large
List pgsql-general
Hi!

I am trying to get postgres to use my index but it just doesn't. Please
Help!
It follows the table definition and a series of commands I thought would
ensure usage of my index.

CREATE TABLE data.question_result (
    id                  bigserial   PRIMARY KEY,
    trial_id            bigint      NOT NULL REFERENCES data.trial(id),
    question_id         bigint      REFERENCES content.question(id),
<two more columns>,
);

mydb=# set enable_seqscan=false;
SET
mydb=# create unique index bothcols on data.question_result
(trial_id,question_id);
CREATE INDEX
mydb=# analyze verbose data.question_result;
INFO:  analyzing "data.question_result"
INFO:  "question_result": 4657 pages, 3000 rows sampled, 591439 estimated
total rows
ANALYZE
mydb=# explain analyze select * from data.question_result where trial_id=1
and question_id=2;
                                                          QUERY
PLAN

------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on question_result  (cost=100000000.00..100013528.58 rows=1
width=30) (actual time=883.641..883.641 rows=0 loops=1)
   Filter: ((trial_id = 1) AND (question_id = 2))
 Total runtime: 883.858 ms
(3 rows)

I inserted the data (not the schema) from a dump if that is of any
relevance.
I am running postgres 7.4.5 on gentoo linux 2.6.8.

Thanks for any Help,

Uli

P.S.: just did CLUSTER bothcols on data.question_result, analyze, explain
analyze...didn't help :-(

--
Geschenkt: 3 Monate GMX ProMail + 3 Top-Spielfilme auf DVD
++ Jetzt kostenlos testen http://www.gmx.net/de/go/mail ++

pgsql-general by date:

Previous
From: Francis Reed
Date:
Subject: Moving/Using Postgres Binaries on multiple machines
Next
From: Neil Conway
Date:
Subject: Re: Can't get planner to use multicolumn index on large