index usage - Mailing list pgsql-novice

From Andrei Ivanov
Subject index usage
Date
Msg-id Pine.LNX.4.58.0310222142370.31834@webdev.ines.ro
Whole thread Raw
Responses Re: index usage
List 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 by date:

Previous
From: Michael Glaesmann
Date:
Subject: Re: Functional index problems. (Was: Many joins: monthly summaries S-L--O--W)
Next
From: Tom Lane
Date:
Subject: Re: Functional index problems. (Was: Many joins: monthly summaries S-L--O--W)