AlexK <alkuzo@gmail.com> wrote:
> My table currently uses up 62 GB of storage, and it has 450 M
> rows. This narrow table has a PK on (ParentID, ChildNumber), and
> it has between 20K and 50K of child rows per parent.
>
> The data is inserted daily, rarely modified, never deleted. The
> performance of modifications is not an issue. The only select
> from it is as follows:
>
> SELECT <column_lis> FROM MyChildTable WHERE ParentID=?
> ORDER BY ChildNumber;
>
> The selects are frequent, and their performance is essential.
>
> Would you advice me to partition this table?
You didn't actually tell us about the most salient facts for
whether partitioning will improve or degrade performance.
If data does not all fit in cache and parents are added over time
with increasing ID values and the vast majority of queries only
reference recent parents, then partitioning by ranges of parentID
will improve your cache hit ratio and thereby improve performance.
Even if all data fits in cache, if children are only added to
recently added parents you could partition by parentID and CLUSTER
partitions when they reach the point where there are few if any new
children or updates. This will reduce the number of pages
referenced per scan, and may allow partitioning to be a win.
Otherwise I would expect partitioning to hurt performance.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
