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 ++
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
