Jekabs Andrushaitis (jeecha(at)one(dot)lv) reports a bug with a severity of 2
The lower the number the more severe it is.
Optimizer does not want to use an index for large table
I have a table with large number of records (100000). Data from this table is mostly selected using one field, on which there is an index.
Explain plan does not show that query engine will use this index, however, which is odd, since statistics clearly show that index fits in a single page, while whole table is about 600 pages. The most common select on the table would retrieve about 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...but Explain 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.
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);
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
pgsql-bugs by date
|Next:||From: Rod Taylor||Date: 2002-09-29 13:46:43|
|Subject: Re: Bug #790: Optimizer does not want to use an index for|
|Previous:||From: Bruce Momjian||Date: 2002-09-29 05:19:28|
|Subject: Re: Bug #789: Transaction Archival Logging -- Hot Backups|