pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedis file size relevant in choosing index or table scan?
2+ messages / 2 participants
[nested] [flat]
* is file size relevant in choosing index or table scan?
@ 2007-05-22 09:29 Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
0 siblings, 1 reply; 2+ messages in thread
From: Joost Kraaijeveld @ 2007-05-22 09:29 UTC (permalink / raw)
To: pgsql-performance
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?
TIA
Joost
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: is file size relevant in choosing index or table scan?
@ 2007-05-22 09:42 Richard Huxton <dev@archonet.com>
parent: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
0 siblings, 0 replies; 2+ messages in thread
From: Richard Huxton @ 2007-05-22 09:42 UTC (permalink / raw)
To: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-05-22 09:42 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-22 09:29 is file size relevant in choosing index or table scan? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2007-05-22 09:42 ` Richard Huxton <dev@archonet.com>
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