Re: Indexes not used in 7.1RC4: Bug?

> I have the following table, containing about 570000 Rows, but some
> indexes are not used, on 7.1RC4, freshly vacuumed (analyse). It was the
> same at least in 7.1RC1
> CREATE TABLE access_log(
> access_time timestamp NOT NULL DEFAULT NOW(),
> method_num int2 NOT NULL,
> url_id int4 NOT NULL REFERENCES urls(id),
> );
> CREATE INDEX method_idx ON access_log(method_num);
> CREATE INDEX url_idx ON access_log(url_id);
> url_idx seems OK:
> But the others not:
> logger=# EXPLAIN SELECT * FROM access_log WHERE method_num = 0;
> Seq Scan on access_log (cost=0.00..16443.71 rows=559371 width=89)

The parser does not know that your int4 constant "0" can be represented
as an int2. Try

SELECT * FROM access_log WHERE method_num = int2 '0';

(note the type coersion on the constant; there are other ways of
specifying the same thing).

For the other cases, PostgreSQL is estimating the query cost to be lower
with a sequential scan. For the "SELECT 1" subselect case, it may be
that the optimizer does not cheat and determine that there will be only
one row returned, or that the query can be reformulated to use a simple
constant.

HTH

- Thomas