Hello,
I'm running PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.96
and I have a table like this:
CREATE TABLE types (
id SERIAL PRIMARY KEY,
type INTEGER NOT NULL,
stype INTEGER NOT NULL
);
CREATE UNIQUE INDEX types_idx ON types(type, stype);
The table contains ~140 rows and I've ran vacuum full analyze.
explain analyze SELECT * FROM types WHERE type = 33 AND stype = 1;
QUERY PLAN
----------------------------------------------------------------------------------------------
Seq Scan on types (cost=0.00..3.12 rows=1 width=12) (actual
time=0.22..0.41 rows=1 loops=1)
Filter: (("type" = 33) AND (stype = 1))
Total runtime: 0.40 msec
(3 rows)
If I do SET ENABLE_SEQSCAN TO OFF, I get:
explain analyze SELECT * FROM types WHERE type = 33 AND stype = 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Index Scan using types_idx on types (cost=0.00..4.28 rows=1 width=12)
(actual time=0.06..0.07 rows=1 loops=1)
Index Cond: (("type" = 33) AND (stype = 1))
Total runtime: 0.17 msec
(3 rows)
Why do I have to tweak it manually to use an index for a thing so simple,
especially that it really does worth using the index... (I've read
http://www.postgresql.org/docs/7.3/interactive/indexes-examine.html).
And by the way, why does explain think it returns 3 rows, when the query
only returns 1 row ?
Thank you.
