From: | Andrei Ivanov <andrei(dot)ivanov(at)ines(dot)ro> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | index usage |
Date: | 2003-10-22 18:51:57 |
Message-ID: | Pine.LNX.4.58.0310222142370.31834@webdev.ines.ro |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-10-22 19:17:58 | Re: Functional index problems. (Was: Many joins: monthly summaries S-L--O--W) |
Previous Message | Michael Glaesmann | 2003-10-22 18:34:59 | Re: Functional index problems. (Was: Many joins: monthly summaries S-L--O--W) |