Thread: PostgreSQL doesn't use indexes even is enable_seqscan = off

PostgreSQL doesn't use indexes even is enable_seqscan = off

From

Hans-Juergen Schoenig

Date:

08 July 2002, 13:02:28

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

Re: PostgreSQL doesn't use indexes even is enable_seqscan = off

From

Manfred Koizar

Date:

08 July 2002, 19:58:26

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

Re: PostgreSQL doesn't use indexes even is enable_seqscan = off

From

Richard Huxton

Date:

08 July 2002, 21:47:07

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

Re: PostgreSQL doesn't use indexes even is enable_seqscan

From

Stephan Szabo

Date:

08 July 2002, 23:07:11

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.

Re: PostgreSQL doesn't use indexes even is enable_seqscan

From

Darren Ferguson

Date:

09 July 2002, 00:19:05

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

Re: PostgreSQL doesn't use indexes even is enable_seqscan = off

From

nconway@klamath.dyndns.org (Neil Conway)

Date:

09 July 2002, 00:46:25

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

Re: PostgreSQL doesn't use indexes even is enable_seqscan

From

Ricardo Junior

Date:

09 July 2002, 07:10:39

    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.