On Sun, 5 Aug 2001, Tom Lane wrote:
> Francisco Reyes <lists@natserv.com> writes:
> > For small tables, less than 100 rows, does it pay off to have an index?
...
> The planner's cost model says that disk fetches cost way more than
> comparison operations, so an index is unlikely to be considered
> profitable unless scanning it saves more fetches than it costs.
> On a really heavily used table, this cost model might break down
> because the pages would all remain in shared memory anyway.
>
> My take on it is that for such a small table, it hardly matters
> which plan is chosen...
>Regards, tom lane
Thanks for the feedback. I am currently designing the tables so only have
a few records. This may be the primary reason for the sequential scan.
Allan Engelhardt, did a quick test and it seems the optimizer did choose
the index. See his message below (minus part of my message removed).
The approach I am planning to take is to create the indices. After a few
days of production operationa and doing regular vacuum analyze then I will do
an explain query to see what the optimizer decides.
-------
Date: Sun, 05 Aug 2001 21:23:22 +0100
From: Allan Engelhardt <allane@cybaea.com>
To: Francisco Reyes <lists@natserv.com>
Newsgroups: comp.databases.postgresql.novice
Subject: Re: Indeces vs small tables
Francisco Reyes wrote:
> For small tables, less than 100 rows, does it pay off to have an index?
> [...]
It does seem to make a difference on my installation (similar to your
example, I think?):
test=# create table system as select relname from pg_class;
SELECT
test=# alter table system add column system serial; -- doesn't really do much....
ALTER
test=# explain select * from system where relname = 'foo';
NOTICE: QUERY PLAN:
Seq Scan on system (cost=0.00..22.50 rows=10 width=36)
EXPLAIN
test=# create unique index system_foo on system(relname);
CREATE
test=# explain select * from system where relname = 'foo';
NOTICE: QUERY PLAN:
Index Scan using system_foo on system (cost=0.00..2.01 rows=1 width=36)
EXPLAIN
test=#
Did you not do a VACUUM ANALYZE after your 100 INSERTs or something?
--- Allan.
