AlexK <alkuzo@xxxxxxxxx> 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
