index usage
От | Andrei Ivanov |
---|---|
Тема | index usage |
Дата | |
Msg-id | Pine.LNX.4.58.0310222142370.31834@webdev.ines.ro обсуждение исходный текст |
Ответы |
Re: index usage
|
Список | pgsql-novice |
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.
В списке pgsql-novice по дате отправления: