Обсуждение: index is not used

Hello,
i need help for my strange problem.

I have a table of ~ 9 000 000 rows,

      Table "mytable"
  Column  |  Type   | Modifiers
----------+---------+-----------
 field1   | integer | not null
 field2   | integer | not null
 field3   | real    |
Indexes: mytable_field1_key,
         mytable_field3_key
Primary key: mytable_field1_field2_pkey

But the indices are not used, even when I added a separate index on
field1 !

  trc=# explain select *  from mytable where field1 = 6;
  NOTICE:  QUERY PLAN:

  Seq Scan on mytable  (cost=0.00..182382.74 rows=49579 width=4)

  EXPLAIN

OR:

  trc=# explain select * from mytable where field3 = 0.3;
  NOTICE:  QUERY PLAN:

  Seq Scan on mytable  (cost=0.00..182382.74 rows=49579 width=4)

  EXPLAIN

I am using postgres 7.2.1 on RedHat.

On the same installation in another database similar table of 100 rows
uses  the indices when needed.

thanks in advance,
mila.

mila boldareva <pierro@dds.nl> writes:
> But the indices are not used, even when I added a separate index on
> field1 !

Have you run ANALYZE (or VACUUM ANALYZE) on that table?

            regards, tom lane

i was surprised a few weeks ago by this effect.
it seems postgres uses sequential scans for queries when a huge number of
affected rows is expected.
i have read somewhere that it would be slower to use an index in this case.

greetingz

Thilo Hille
resourcery GbR.
Habsburgerstr. 11
79104 Freiburg
Tel.: 0761-4567807
Fax.: 0761-4567805
thilo@resourcery.de

----- Original Message -----
From: Tom Lane <tgl@sss.pgh.pa.us>
To: mila boldareva <pierro@dds.nl>
Cc: <pgsql-novice@postgresql.org>
Sent: Tuesday, August 20, 2002 4:50 PM
Subject: Re: [NOVICE] index is not used


> mila boldareva <pierro@dds.nl> writes:
> > But the indices are not used, even when I added a separate index on
> > field1 !
>
> Have you run ANALYZE (or VACUUM ANALYZE) on that table?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

> mila boldareva <pierro@dds.nl> writes:
>> But the indices are not used, even when I added a separate index on
>> field1 !

> Have you run ANALYZE (or VACUUM ANALYZE) on that table?

>                         regards, tom lane

Yes, VACUUM [full] alone did not help, but, as advised (thanks!), I've
VACUUM ANALYZEd, and then it started using index on integer field1 and
field2 as expected, but the index on real field3 is used only when I
ask ordering, and a query like below remains seq. scan:

    trc=# explain select * from mytable where field3 = 0.1 limit 1;
    NOTICE:  QUERY PLAN:

    Limit  (cost=0.00..11.25 rows=1 width=12)
      ->  Seq Scan on mytable  (cost=0.00..182277.74 rows=16201 width=12)

    EXPLAIN
    trc=#

Thilo Hille suggested that postgres uses sequential scans for queries
when a huge number of affected rows is expected, but it uses seq.scan
even when I ask for a value that is definitely out the range of values
I have.

Anyway, thanks for help,
Mila

mila boldareva <pierro@dds.nl> writes:
> ... but the index on real field3 is used only when I
> ask ordering, and a query like below remains seq. scan:

>     trc=# explain select * from mytable where field3 = 0.1 limit 1;

Unadorned "0.1" will be taken as a float8 (double precision) constant,
and the system is not currently smart enough to convert a cross-datatype
comparison (viz, float4 vs float8) into an indexscan.  You can either
change the field type to double precision or write an explicit coercion:

    select * from mytable where field3 = 0.1::real limit 1;

A hack that some people use instead is to quote the constant, even
though it's numeric:

    select * from mytable where field3 = '0.1' limit 1;

It turns out that this causes the system to postpone assigning a
specific type to the constant until late enough in processing the query
that it knows float4 is the best choice instead of float8.

This is basically the same story as for int2 and int8 columns.  It's
pretty ugly, and fixing it is on the TODO list.

            regards, tom lane