pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedParitioning vs. caching
3+ messages / 3 participants
[nested] [flat]
* Paritioning vs. caching
@ 2010-03-08 18:28 Konrad Garus <konrad.garus@gmail.com>
2010-03-08 19:27 ` Re: Paritioning vs. caching Anj Adu <fotographs@gmail.com>
2010-03-08 20:30 ` Re: Paritioning vs. caching Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 3+ messages in thread
From: Konrad Garus @ 2010-03-08 18:28 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Paritioning vs. caching
2010-03-08 18:28 Paritioning vs. caching Konrad Garus <konrad.garus@gmail.com>
@ 2010-03-08 19:27 ` Anj Adu <fotographs@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Anj Adu @ 2010-03-08 19:27 UTC (permalink / raw)
To: Konrad Garus <konrad.garus@gmail.com>; +Cc: pgsql-performance
If the partitioned column in your where clause does not use hardcoded
values ...e.g datecolumn between 'year1' and 'year2' ..the query
planner will check all partitions ..this is a known issue with the
optimizer
On Mon, Mar 8, 2010 at 10:28 AM, Konrad Garus <konrad.garus@gmail.com> wrote:
> 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
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Paritioning vs. caching
2010-03-08 18:28 Paritioning vs. caching Konrad Garus <konrad.garus@gmail.com>
@ 2010-03-08 20:30 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 3+ messages in thread
From: Josh Berkus @ 2010-03-08 20:30 UTC (permalink / raw)
To: Konrad Garus <konrad.garus@gmail.com>; +Cc: pgsql-performance
> 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.
If you're not including the partition criterion in most of your queries,
you're probably partitioning on the wrong value.
--Josh Berkus
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-03-08 20:30 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-03-08 18:28 Paritioning vs. caching Konrad Garus <konrad.garus@gmail.com>
2010-03-08 19:27 ` Anj Adu <fotographs@gmail.com>
2010-03-08 20:30 ` Josh Berkus <josh@agliodbs.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