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
