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.
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 |