Re: Partitioned Table Index Column Order

On Thu, 24 Jun 2021 at 11:56, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> David Rowley <dgrowleyml(at)gmail(dot)com> writes:
> > On Thu, 24 Jun 2021 at 10:55, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
> >> 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:
>
> Sure, but is that any different from the behavior with unpartitioned
> tables? You have to make the index column order agree with the
> ORDER BY you want to use, in either case.

The reason I mentioned it is that the performance of the ordered
partitioned scans pretty good. If the application does ORDER BY a,b
just as often as it does ORDER BY b,a and you just get to pick 1
index, then it's better to have the index with the partitioned key
first. At least one of the queries can get away without doing a Sort
that way. If you have the partition key last in the index then both
queries need to sort... You could fix that by adding a 2nd index, but
that's not always practical, so it seems worth a mention, at least to
me.

David