Re: PostgreSQL doesn't use indexes even is enable_seqscan

On Sun, 7 Jul 2002, Hans-Juergen Schoenig wrote:

> I have a severe problem with PostgreSQL 7.2.1.
> I have a table containing 500mio records (for testing purposes).
>
> I have indexed the table:
>
> CREATE UNIQUE INDEX idx_one_id ON one(id);
> CREATE INDEX idx_one_xmod ON one(xmod);
>
> The index was created properly but somehow it isn't used:
>
> cluster=# \d one
>           Table "one"
>   Column |  Type   | Modifiers
> --------+---------+-----------
>   id     | bigint  |
>   even   | boolean |
>   xmod   | integer |
> Indexes: idx_one_xmod
> Unique keys: idx_one_id
>
> cluster=# SET enable_seqscan TO off;
> SET VARIABLE
> cluster=# SELECT * FROM one WHERE id=300000;

You'll need to quote or explicitly cast the
constant to bigint for the indexes to get used.
It's a problem with the way numeric constants are
handled.  They'll get prematurely forced to int4
if they fit, and won't promote for index use.