Re: Indeces vs small tables

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.