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

[Richard Huxton <dev(at)archonet(dot)com>]

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