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
