Thread: Can't get planner to use multicolumn index on large table

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 ++

Ulrich Meis wrote:
> 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=# explain analyze select * from data.question_result where trial_id=1
> and question_id=2;

This is a well-known optimizer deficiency. You need to single-quote the
numeric literals or cast them to the type of the column, or else you
won't get index scans for non-int4 columns. In other words:

explain analyze select * from data.question_result where trial_id='1'
and question_id='2'

This is fixed in 8.0

-Neil