Hello !
Sorry if this has been discussed before, it is just hard to find in the archives using the words "or" or "in" :-o
I use postgres-8.0 beta4 for windows.
I broke down my problem to a very simple table - two columns "primary_key" and "secondary_key". Creates and Insert you will find below.
If I query the _empty_ freshly created table I get the following explain result:
select * from tt where seckey = 1; Index Scan using seckey_key on tt (cost=0.00..17.07 rows=5 width=12) Index Cond: (seckey = 1)
If I use "OR" (or IN) things get worse:
select * from tt where seckey = 1 or seckey = 2 Seq Scan on tt (cost=0.00..0.00 rows=1 width=12) Filter: ((seckey = 1) OR (seckey = 2))
Note the "Seq Scan" instead of using the index.
After populating the table with 8920 records and "analyze" the scenario gets even worser:
select * from tt where seckey = 1;
Seq Scan on tt (cost=0.00..168.50 rows=1669 width=12) (actual time=0.000..15.000 rows=1784 loops=1)
Filter: (seckey = 1)
Total runtime: 31.000 ms
Now also this simple query uses a "Seq Scan".
Now the questions are: a) Why is the index not used if I use "OR" or "IN" b) Why is the index not used after "analyze" ?
Any help is very appreciated!
Thanks, Mario
// The table and data
CREATE TABLE tt (
pkey int4 NOT NULL DEFAULT nextval('public."tt_PKEY_seq"'::text),
seckey int8,
CONSTRAINT pkey_key PRIMARY KEY (pkey)
)
WITHOUT OIDS;CREATE INDEX seckey_key ON tt USING btree (seckey);
// inserted many-many times insert into tt values (default, 1); insert into tt values (default, 2); insert into tt values (default, 3); insert into tt values (default, 4); insert into tt values (default, 5);
---------------------------(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
