Message-ID: <0fbb3e14e792b85057441739053a2c608bf74318.camel@cybertec.at>
Subject: Re: Partitioning Tables
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: "Campbell, Lance" <lance@illinois.edu>,
	"pgsql-admin@lists.postgresql.org"
	 <pgsql-admin@lists.postgresql.org>
Date: Sat, 06 Feb 2021 05:28:14 +0100
In-Reply-To: <248F0103-EA30-4536-963B-73BD7477AF08@illinois.edu>
References: <248F0103-EA30-4536-963B-73BD7477AF08@illinois.edu>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.36.5 (3.36.5-2.fc32) 
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

On Fri, 2021-02-05 at 18:27 +0000, Campbell, Lance wrote:
> I have a challenge.  I have two tables, group and group_member.  A group table has a
>  type indicator telling me which of three ways the table can be used.  The group member
>  table is made up of 50 million records that have a foreign key to the group table.
> 
> I really need the queries to be fast for one particular type of group.  This type has
>  less than a million members in it.  So my first thought was to create a separate
>  group_member table just for members of this type of group.  But I have to change a lot of SQL.
> 
> The other idea I thought of is there a way to use table partitions?  If the query goes
>  against a group of type A then it would pull from the small partition but if it is a group
>  of some other type it would query against the other partition. 

If you want to partition "group_member", the partitioning key cannot be in "group".

So you would have to (redundantly) add "group.type" to "group_member".
You could ensure consistency by including that column in the foreign key.

If you want partition pruning to take effect, you will have to include
a condition on "group_member.type" in the WHERE condition.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com