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.

Andrew Snow writes:

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?

You can't get the "answer straight out of the index". You always need to
check back in the real table to see whether the row is visible to your
transaction.

--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter

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

Hrmm... I have 26 rows in mine at the moment, and after vacuum
analyzing, it uses a seq. scan. How come yours used the index? I
thought mine wasn't using an index because postgres won't use an index
until the table is "big enough".

But if an index page is already in cache.. surely it'd be faster using
it than doing a seq. scan.

(Yes, I know its a small table, but I think the worst case for seq. scan
would be a fair bit worse than for the index, and every little bit
counts, right?)

- Andrew

On Fri, 31 Aug 2001, Andrew Snow wrote:

Hrmm... I have 26 rows in mine at the moment, and after vacuum
analyzing, it uses a seq. scan. How come yours used the index? I
thought mine wasn't using an index because postgres won't use an index
until the table is "big enough".

But if an index page is already in cache.. surely it'd be faster using
it than doing a seq. scan.

(Yes, I know its a small table, but I think the worst case for seq. scan
would be a fair bit worse than for the index, and every little bit
counts, right?)

If the table is small enough to fit in one page, a sequence scan across
those rows may be faster than the index scan since the index scan will
need to read two pages (one for the index, one for the heap -- the
visibility info is only in the heap so that must be consulted for
each index match)