Обсуждение: PostgreSQL doesn't use indexes even is enable_seqscan = off

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

On Sun, 07 Jul 2002 10:48:51 +0200, Hans-Juergen Schoenig
<hs@cybertec.at> wrote:
>I have a severe problem with PostgreSQL 7.2.1.
>CREATE UNIQUE INDEX idx_one_id ON one(id);
>  Column |  Type   | Modifiers
>--------+---------+-----------
>  id     | bigint  |
>cluster=# SELECT * FROM one WHERE id=300000;

Hans, try
    SELECT * FROM one WHERE id='300000';
or
    SELECT * FROM one WHERE id=300000::bigint;

HTH.
Servus
 Manfred

On Sunday 07 Jul 2002 9:48 am, Hans-Juergen Schoenig wrote:
> 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;

try... WHERE id=300000::bigint
or... WHERE id='300000'

Postgres thinks the 300000 is of type int then fails to find an index of that
type.

Search the archives for "bigint" and "index" for loads on this.

- Richard Huxton

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.

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@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>

--
Darren Ferguson

On Sun, Jul 07, 2002 at 10:48:51AM +0200, Hans-Juergen Schoenig wrote:
> cluster=# \d one
>          Table "one"
>  Column |  Type   | Modifiers
> --------+---------+-----------
>  id     | bigint  |
>  even   | boolean |
>  xmod   | integer |
> Indexes: idx_one_xmod
> Unique keys: idx_one_id

> cluster=# EXPLAIN SELECT * FROM one WHERE id=300000;
> NOTICE:  QUERY PLAN:
>
> Seq Scan on one  (cost=100000000.00..109434714.00 rows=1 width=13)

Use single quotes around the int8 literal -- i.e.

SELECT * FROM one WHERE id = '300000';

This is an FAQ.

Cheers,

Neil

--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

    Hi there,

> > I have a severe problem with PostgreSQL 7.2.1.
> > I have a table containing 500mio records (for testing purposes).
> > 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.

    Perhaps this should be added to the Documentation in the indexes
part. I had the same problem last week and I guess it's a very common and
frequent question.

    []'s
    Ricardo.