From: | "Ulrich Meis" <u(dot)meis(at)gmx(dot)de> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Can't get planner to use multicolumn index on large table |
Date: | 2004-11-24 12:33:04 |
Message-ID: | 27306.1101299584@www1.gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 ++
From | Date | Subject | |
---|---|---|---|
Next Message | Neil Conway | 2004-11-24 13:06:38 | Re: Can't get planner to use multicolumn index on large |
Previous Message | Francis Reed | 2004-11-24 12:30:28 | Moving/Using Postgres Binaries on multiple machines |