Thread: Bug #790: Optimizer does not want to use an index for large table

Jekabs Andrushaitis (jeecha@one.lv) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
Optimizer does not want to use an index for large table

Long Description
I have a table with large number of records (100000). Data from this table is mostly selected using one field, on which
thereis an index. 
Explain plan does not show that query engine will use this index, however, which is odd, since statistics clearly show
thatindex fits in a single page, while whole table is about 600 pages. The most common select on the table would
retrieveabout 10 rows of data, and using the index would be way more efficient. I read the documentation describing
indexes,explain plans and how the statistics affects queries, and according to docs, the index should have been
used...butExplain shows that it's not. I tried dropping and re-creating the index, but still it's not used! 
I am usine PostgreSQL 7.2 on Linux Mandrake.

Sample Code
CREATE TABLE obj_props(obj_id int8,name text,value text);

for i:=0 to 10000
  for j:=0 to 10
    INSERT INTO obj_props(i,'some name','some value');

CREATE INDEX obj_props_ind1 ON obj_props(obj_id);

VACUUM ANALYZE;

EXPLAIN SELECT name,value FROM obj_props WHERE obj_id=100;

... this shows that sequential scan is used, however using index obj_props_ind1 would have been VERY efficient!

No file was uploaded with this report

On Sun, 2002-09-29 at 06:25, pgsql-bugs@postgresql.org wrote:
> Jekabs Andrushaitis (jeecha@one.lv) reports a bug with a severity of 2
> The lower the number the more severe it is.
>
> Short Description
> Optimizer does not want to use an index for large table
>
> Long Description
> I have a table with large number of records (100000). Data from this table is mostly selected using one field, on
whichthere is an index. 
> Explain plan does not show that query engine will use this index, however, which is odd, since statistics clearly
showthat index fits in a single page, while whole table is about 600 pages. The most common select on the table would
retrieveabout 10 rows of data, and using the index would be way more efficient. I read the documentation describing
indexes,explain plans and how the statistics affects queries, and according to docs, the index should have been
used...butExplain shows that it's not. I tried dropping and re-creating the index, but still it's not used! 
> I am usine PostgreSQL 7.2 on Linux Mandrake.
>
> Sample Code
> CREATE TABLE obj_props(obj_id int8,name text,value text);
>
> for i:=0 to 10000
>   for j:=0 to 10
>     INSERT INTO obj_props(i,'some name','some value');
>
> CREATE INDEX obj_props_ind1 ON obj_props(obj_id);
>
> VACUUM ANALYZE;
>
> EXPLAIN SELECT name,value FROM obj_props WHERE obj_id=100;

This is one of those annoying things that has recently been (or is in
the process of being) fixed in 7.3.

End result, obj_id is an int8, while 100 is an int4.  Older versions
don't do the cast of int4 to int8 as there was the int48eq operator
available and some logic missing to cast in the right direction.

For 7.2, use obj_id='100'.  Quotes make the entry UNKNOWN which is
casted to int8 and should use the index.

--
  Rod Taylor

pgsql-bugs@postgresql.org writes:
> CREATE TABLE obj_props(obj_id int8,name text,value text);
                         ^^^^^^^^^^^

You need an explicit cast, like this:

EXPLAIN SELECT name,value FROM obj_props WHERE obj_id = 100::int8;

because the planner is not smart about converting cross-datatype
comparisons into indexscans, and unadorned "100" is taken as int4.

I think we will finally have a fix for this in 7.4.

            regards, tom lane