Re: Indeces vs small tables

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 is of the opinion that it doesn't ;-)

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.
If the table is only a couple of disk pages in size, there's no
way that doing the extra I/O to read an index (also a couple of
pages) can pay for itself.

On a really heavily used table, this cost model might break down
because the pages would all remain in shared memory anyway. But
that's how the planner will bet, so you'd have to go out of your
way to persuade it to use the index.

My take on it is that for such a small table, it hardly matters
which plan is chosen...

regards, tom lane