Re: PostgreSQL doesn't use indexes even is enable_seqscan

Postgres thinks 300000 is an int4 and not int8 so it is not using the
index on the int8.

Either cast by doing

EXPLAIN SELECT * FROM one WHERE id = 300000::bigint;

or

EXPLAIN SELECT * FROM one WHERE id = '300000';

Darren

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;
> Cancel request sent
> ERROR: Query was cancelled.
> cluster=# EXPLAIN SELECT * FROM one WHERE id=300000;
> NOTICE: QUERY PLAN:
>
> Seq Scan on one (cost=100000000.00..109434714.00 rows=1 width=13)
>
> EXPLAIN
> cluster=# SELECT version();
> version
> -------------------------------------------------------------
> PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96
> (1 row)
>
> When sequential scans are turned off PostgreSQL should use an index but
> it doesn't.
> Is it a bug? Have I done something one?
> Did anybody face a similar problem?
>
> Hans
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>

--
Darren Ferguson