From: Richard Huxton <dev@archonet.com>
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
Cc: pgsql-performance@postgresql.org
Subject: Re: is file size relevant in choosing index or table scan?
Date: Tue, 22 May 2007 10:42:18 +0100
Message-ID: <4652BAFA.5060206@archonet.com> (raw)
In-Reply-To: <A3D1526C98B7C1409A687E0943EAC410605FF1@obelix.askesis.nl>
References: <A3D1526C98B7C1409A687E0943EAC410605FF1@obelix.askesis.nl>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dev@archonet.com, J.Kraaijeveld@Askesis.nl
Subject: Re: is file size relevant in choosing index or table scan?
In-Reply-To: <4652BAFA.5060206@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox