Re: Partitioned Table Index Column Order

On Thu, 24 Jun 2021 at 10:55, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
>
> On 2021-Jun-23, Rumpi Gravenstein wrote:
>
> > As a best practice is it better to create the primary key starting or
> > ending with the partition column?
>
> It is not relevant from the partitioning point of view. Other factors
> can be used to decide the column order.

I'm not so sure that's really 100% true. There is at least one
partitioning feature that will work when the partitioning column is
first and won't when it's not.

Ordered partition scans work with RANGE and LIST partitioning:

create table ab (a int, b int, primary key(a,b)) partition by range(a);
create table ab1 partition of ab for values from (0) to (10);
create table ab2 partition of ab for values from (10) to (20);
explain (costs off) select * from ab order by a;
QUERY PLAN
--------------------------------------------------
Append
-> Index Only Scan using ab1_pkey on ab1 ab_1
-> Index Only Scan using ab2_pkey on ab2 ab_2

Reverse the order and you get:

QUERY PLAN
----------------------------------
Sort
Sort Key: ab.a
-> Append
-> Seq Scan on ab1 ab_1
-> Seq Scan on ab2 ab_2

David