Received: from maia.hub.org (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 9190A63274B
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon,  8 Mar 2010 14:28:43 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia,
	port 10024) with ESMTP id 79359-03
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon,  8 Mar 2010 18:28:30 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-qy0-f178.google.com (mail-qy0-f178.google.com
	[209.85.221.178])
	by mail.postgresql.org (Postfix) with ESMTP id D3571632F10
	for <pgsql-performance@postgresql.org>;
	Mon,  8 Mar 2010 14:28:32 -0400 (AST)
Received: by qyk8 with SMTP id 8so4201909qyk.4
	for <pgsql-performance@postgresql.org>;
	Mon, 08 Mar 2010 10:28:32 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:date:message-id:subject
	:from:to:content-type;
	bh=jowaq19PjqpqHD2k181GuztNC1zaa2ggr7joHrqusZI=;
	b=dzx7XBDk8TybnsCABdrB4zlN679sIGYD2hpn8ykrOYvQAdIAEStgvcmrm9OO3IkTrY
	eetfoYcd/G83CIUN4mVAAsG8DOghunkYKVekXlVXa2wesj0Uh2Zh6OEy7fdd4edaQn1p
	RK+dPLN00pYKGPl+vgCAEp02u1MbS3/enLu+8=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:date:message-id:subject:from:to:content-type;
	b=UOUGZL+J3qNw6T+qhGH9EpJ7WE4UG0JPoVytwVfxbfRDBNWud9YUG5uaesFVpGDuwI
	oX7VwcI2n2tiD5WFWUrSBe2tUURctcpe9eWzF5sXlzTfBNdMbHawLlevC8ug5OYIBVpy
	Iq9rM83zCEH7I94i2K1zhOhZf3sSC13udcj9Q=
MIME-Version: 1.0
Received: by 10.229.231.132 with SMTP id jq4mr616807qcb.55.1268072910719;
 Mon,
	08 Mar 2010 10:28:30 -0800 (PST)
Date: Mon, 8 Mar 2010 19:28:30 +0100
Message-ID: <6645f6441003081028v6dbca8f4s2a480114b86cb2a6@mail.gmail.com>
Subject: Paritioning vs. caching
From: Konrad Garus <konrad.garus@gmail.com>
To: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.548 tagged_above=-10 required=5 tests=AWL=1.051,
	BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 201003/75
X-Sequence-Number: 37900

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