Re: is file size relevant in choosing index or table scan?

Joost Kraaijeveld wrote:
> Hi,
>
> I have a table with a file size of 400 MB with an index of 100 MB.
> Does PostgreSQL take the file sizes of both the table and the index
> into account when determing if it should do a table or an index scan?

In effect yes, although it will think in terms of row sizes and disk
blocks. It also considers how many rows it thinks it will fetch and
whether the rows it wants are together or spread amongst many blocks. It
also tries to estimate what the chances are of those blocks being cached
in RAM vs still on disk.

So: 1 row from a 4 million row table, accessed by primary key => index.
20 rows from a 200 row table => seq scan (probably).
In between => depends on your postgresql.conf

--
Richard Huxton
Archonet Ltd