Re: Indeces vs small tables

From: Francisco Reyes <lists(at)natserv(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Pgsql Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Indeces vs small tables
Date: 2001-08-05 22:04:22
Message-ID: 20010805175448.W36941-100000@zoraida.natserv.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

On Sun, 5 Aug 2001, Tom Lane wrote:

> Francisco Reyes <lists(at)natserv(dot)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(at)cybaea(dot)com>
To: Francisco Reyes <lists(at)natserv(dot)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.

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Francisco Reyes 2001-08-05 22:15:45 Re: select vs varchar
Previous Message harrold 2001-08-05 20:55:59 nextval/dbi question