From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Campbell, Lance <lance@illinois.edu>
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: Partitioning Tables
Date: Sat, 06 Feb 2021 05:28:14 +0100
Message-ID: <0fbb3e14e792b85057441739053a2c608bf74318.camel@cybertec.at> (raw)
In-Reply-To: <248F0103-EA30-4536-963B-73BD7477AF08@illinois.edu>
References: <248F0103-EA30-4536-963B-73BD7477AF08@illinois.edu>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, lance@illinois.edu, pgsql-admin@lists.postgresql.org
Subject: Re: Partitioning Tables
In-Reply-To: <0fbb3e14e792b85057441739053a2c608bf74318.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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