Hi,
I have system here with Debian/Testing and the latest 8.2 and 8.3
database installed.
on a blank database I create two very simple tables
Table "public.foo"
Column | Type | Modifiers
--------+-------------------+------------------------------------------------------
foo_id | integer | not null default
nextval('foo_foo_id_seq'::regclass)
test | character varying |
Indexes:
"foo_pkey" PRIMARY KEY, btree (foo_id)
Table "public.bar"
Column | Type | Modifiers
--------+-------------------+------------------------------------------------------
bar_id | integer | not null default
nextval('bar_bar_id_seq'::regclass)
foo_id | integer | not null
test | character varying |
Indexes:
"bar_pkey" PRIMARY KEY, btree (bar_id)
"bar_foo_id_idx" btree (foo_id)
Foreign-key constraints:
"bar_foo_id_fkey" FOREIGN KEY (foo_id) REFERENCES foo(foo_id) MATCH
FULL ON UPDATE CASCADE ON DELETE CASCADE
now if I run a simple join query over both tables Postgres 8.2 gives
this back for the explain:
# explain select * from foo f, bar b where f.foo_id = b.foo_id;
QUERY PLAN
------------------------------------------------------------------------------------
Nested Loop (cost=0.00..33.14 rows=3 width=76)
-> Index Scan using bar_foo_id_idx on bar b (cost=0.00..12.30
rows=3 width=40)
-> Index Scan using foo_pkey on foo f (cost=0.00..6.93 rows=1 width=36)
Index Cond: (f.foo_id = b.foo_id)
but on the 8.3 version i get this back
# explain select * from foo f, bar b where f.foo_id = b.foo_id;
QUERY PLAN
------------------------------------------------------------------
Hash Join (cost=1.07..2.14 rows=3 width=24)
Hash Cond: (b.foo_id = f.foo_id)
-> Seq Scan on bar b (cost=0.00..1.03 rows=3 width=14)
-> Hash (cost=1.03..1.03 rows=3 width=10)
-> Seq Scan on foo f (cost=0.00..1.03 rows=3 width=10)
once I insert a million rows he does use the index:
# explain select * from foo f, bar b where f.foo_id = b.foo_id;
QUERY PLAN
-----------------------------------------------------------------------------------
Nested Loop (cost=0.00..26.39 rows=9 width=35)
-> Seq Scan on foo f (cost=0.00..1.03 rows=3 width=21)
-> Index Scan using bar_foo_id_idx on bar b (cost=0.00..8.42 rows=3
width=14)
Index Cond: (b.foo_id = f.foo_id)
I have seen this behavior on all of my postgres 8.3 installs. The
indexes are there, auto vacuum is turned on. even a reindex of the
tables does not help. The configuration files are identical in grounds
of memory usage, query planning, etc.
I see this on RPM packages for RedHat Enterprise, self compiled for
FreeBSD 4, and debian packages. I am seriously very very confused.
What can I do to debug this further, or find out why this happens?
Does this mean Postgres 8.3 thinks a sequence scan is faster than an
index scan? Even on tables with hundred thousands rows?
--
[ Clemens Schwaighofer -----=====:::::~ ]
[ IT Engineer/Manager ]
[ E-Graphics Communications, TEQUILA\ Japan IT Group ]
[ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ]
[ Tel: +81-(0)3-3545-7706 Fax: +81-(0)3-3545-7343 ]
[ http://www.tequila.jp ]
Attachment:
signature.asc
Description: PGP signature
Attachment:
signature.asc
Description: OpenPGP digital signature
