Thread: Indexes not working (bug in 7.0.2?)
Hi!
This is my first post (not counting those failed because I posted them
from the other email address) so please be gentle.
I have recently started playing with PostgreSQL and found what I think
is a bug in postgres. I'm using 7.0.2 from Debian Linux package
(woody) if its of any difference.
I have a table like this:
imenik=# \d subscriber
Table "subscriber"
Attribute | Type | Modifier
-----------+--------------+----------
id | integer | not null
prefix | char(2) | not null
name | varchar(128) | not null
number | varchar(8) | not null
title | integer |
street | integer | not null
num | varchar(8) | not null
city | integer | not null
Index: idx_number
First I populated the table, then I created index on the number field
with "CREATE INDEX idx_number on subscriber(number);"
The trouble is when I do SELECT specifying a number, postgres reads a
whole table ie. it is not using the index. EXPLAIN reveals it is
indeed doing a sequential scan.
test=# EXPLAIN SELECT * from subscriber where number = '123456';
NOTICE: QUERY PLAN:
Seq Scan on subscriber (cost=0.00..38677.28 rows=15564 width=64)
EXPLAIN
What am I doing wrong???
Second example:
Today I stumbled upon a similar problem with completely different set
of data, but even more confusing.
Consider two tables 'filenew' and 'fileold' that have same fields and
indices:
filedb=# \d fileold
Table "fileold"
Attribute | Type | Modifier
-----------+---------------+----------
file | varchar(1024) | not null
mode | integer | not null
uid | integer | not null
gid | integer | not null
size | bigint | not null
mtime | integer | not null
ctime | integer | not null
Index: fileold_file_key
s/fileold/filenew and you know the schema for filenew. Idea is to
populate fileold once with filesystem information and then later, when
things on FS change, populate filenew with a new data and search for
differences.
As you see, tables are almost the same, but...
*** Looking for new files:
filedb=# explain select file from filenew where not exists (select 1 from fileold where filenew.file = fileold.file);
NOTICE: QUERY PLAN:
Seq Scan on filenew (cost=0.00..0.00 rows=1 width=12)
SubPlan
-> Index Scan using fileold_file_key on fileold (cost=0.00..2935.96 rows=1329 width=4)
EXPLAIN
*** Looking for deleted files:
filedb=# explain select file from fileold where not exists (select 1 from filenew where fileold.file = filenew.file);
NOTICE: QUERY PLAN:
Seq Scan on fileold (cost=0.00..3155.26 rows=1 width=12)
SubPlan
-> Seq Scan on filenew (cost=0.00..0.00 rows=1 width=4)
EXPLAIN
It is now NOT using the index, and I don't understand why? Queries are
practically the same, tables are practically the same, why is postgres
using indexes in the first case and not in the second?
TIA,
--
Zlatko
* Zlatko Calusic <zlatko@iskon.hr> [000903 07:59] wrote: > Hi! > > This is my first post (not counting those failed because I posted them > from the other email address) so please be gentle. > > I have recently started playing with PostgreSQL and found what I think > is a bug in postgres. I'm using 7.0.2 from Debian Linux package > (woody) if its of any difference. > > I have a table like this: [snip] Two things: Try "vacuum analyze subscriber;" if that doesn't work you may want to try "set enable seqscan off;" -Alfred
Zlatko Calusic <zlatko@iskon.hr> writes: > It is now NOT using the index, and I don't understand why? Queries are > practically the same, tables are practically the same, why is postgres > using indexes in the first case and not in the second? Because it has substantially different ideas about the sizes of the two tables --- notice the different estimated row counts. If you haven't "vacuum analyzed" these tables recently, do so to bring the planner's statistics up-to-date, and then see what you get. You may also care to read the user's manual chapter about EXPLAIN, http://www.postgresql.org/users-lounge/docs/7.0/postgres/c4888.htm regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > Zlatko Calusic <zlatko@iskon.hr> writes: > > It is now NOT using the index, and I don't understand why? Queries are > > practically the same, tables are practically the same, why is postgres > > using indexes in the first case and not in the second? > > Because it has substantially different ideas about the sizes of the > two tables --- notice the different estimated row counts. If you > haven't "vacuum analyzed" these tables recently, do so to bring the > planner's statistics up-to-date, and then see what you get. You may > also care to read the user's manual chapter about EXPLAIN, > http://www.postgresql.org/users-lounge/docs/7.0/postgres/c4888.htm > Yes, thanks to all who helped. 'vacuum analyze' was the magical incantation that helped. I still have one uncertainty. Is it possible that after some time postgres once again decides not to use indices (assuming I haven't run 'vacuum analyze' again)? -- Zlatko
I'm searching for the SOURCES of JDBC DRIVER (for 7.02), precompiled driver
have a bug.
Thanks,
Enrico
Manual says you should use it after initial data entry, after entering a large amount of rows and periodically. At 23:02 4.9.2000 , Zlatko Calusic wrote: >Tom Lane <tgl@sss.pgh.pa.us> writes: > >> Zlatko Calusic <zlatko@iskon.hr> writes: >> > It is now NOT using the index, and I don't understand why? Queries are >> > practically the same, tables are practically the same, why is postgres >> > using indexes in the first case and not in the second? >> >> Because it has substantially different ideas about the sizes of the >> two tables --- notice the different estimated row counts. If you >> haven't "vacuum analyzed" these tables recently, do so to bring the >> planner's statistics up-to-date, and then see what you get. You may >> also care to read the user's manual chapter about EXPLAIN, >> http://www.postgresql.org/users-lounge/docs/7.0/postgres/c4888.htm >> > >Yes, thanks to all who helped. 'vacuum analyze' was the magical >incantation that helped. > >I still have one uncertainty. Is it possible that after some time >postgres once again decides not to use indices (assuming I haven't run >'vacuum analyze' again)? > >-- >Zlatko