Re: quick question: index optimisations on small tables

Andrew Snow wrote:
>
> If I have:
>
> CREATE TABLE small (
> key integer PRIMARY KEY,
> value text
> );
>
> and assuming there are only enough rows to fit in one page, doesn't it
> make sense to use the index instead of a seq. scan for queries of type
>
> SELECT value FROM small WHERE key = 12345;
>

Since you have declared the column 'key' as PRIMARY KEY there is an
index on column 'key' anyway and SELECT value FROM small where key =
12345
will use that index: on my system psql said:

omicron=# EXPLAIN SELECT value FROM small WHERE key = 12345;
NOTICE: QUERY PLAN:

Index Scan using small_pkey on small (cost=0.00..8.14 rows=10 width=12)

> Since it can get the answer straight out of the index, and if there are
> potentially numerous rows, looking up a b-tree is faster than a linear
> search?

Looking up from an index is of course faster than a seq. scan
(in almost all cases).

Arne.

> TIP 4: Don't 'kill -9' the postmaster