index usage

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.

Responses

Browse pgsql-novice by date

  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)