From: Konrad Garus <konrad.garus@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Paritioning vs. caching
Date: Mon, 8 Mar 2010 19:28:30 +0100
Message-ID: <6645f6441003081028v6dbca8f4s2a480114b86cb2a6@mail.gmail.com> (raw)
Hello,
I am evaluating a materialized view implemented as partitioned table.
At the moment the table is partitioned yearly and contains 5
numeric/timestamp columns. One of the columns is ID (but it's not what
the table is partitioned on).
Partition for one year occupies about 1200 MB. Each of the columns is
indexed, with each index weighing about 160 MB. I am trying to avoid
RAM/disk thrashing. Now I have the following questions:
1. When I query the table by ID, it performs index scan on each
partition. The result is only found in one partition, but I understand
why it needs to look in all of them. How much disk reading does it
involve? Is only the "head" of indexes for partitions that do not
include the row scanned, or are always whole indexes read? I would
like to know the general rule for index scans.
2. Is it possible to tell which PG objects are read from disk (because
they were not found in RAM)?
Thank you.
--
Konrad Garus
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: konrad.garus@gmail.com
Subject: Re: Paritioning vs. caching
In-Reply-To: <6645f6441003081028v6dbca8f4s2a480114b86cb2a6@mail.gmail.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