Re: How to create efficient index in this scenario?

On Sat, Jun 8, 2024 at 7:03 PM veem v <veema0000(at)gmail(dot)com> wrote:

> Hi ,
> It's postgres version 15.4. A table is daily range partitioned on a column
> transaction_timestamp. It has a unique identifier which is the ideal for
> primary key (say transaction_id) , however as there is a limitation in
> which we have to include the partition key as part of the primary key, so
> it has to be a composite index. Either it has to be
> (transaction_id,transaction_timestamp) or ( transaction_timestamp,
> transaction_id). But which one should we go for, if both of the columns get
> used in all the queries?
>
> We will always be using transaction_timestamp as mostly a range predicate
> filter/join in the query and the transaction_id will be mostly used as a
> join condition/direct filter in the queries. So we were wondering, which
> column should we be using as a leading column in this index?
>
> There is a blog below (which is for oracle), showing how the index should
> be chosen and it states , "*Stick the columns you do range scans on last
> in the index, filters that get equality predicates should come first.* ",
> and in that case we should have the PK created as in the order
> (transaction_id,transaction_timestamp). It's because making the range
> predicate as a leading column won't help use that as an access predicate
> but as an filter predicate thus will read more blocks and thus more IO.
> Does this hold true in postgres too?
>
> https://ctandrewsayer.wordpress.com/2017/03/24/the-golden-rule-of-indexing/
>

I believe the analogy holds true here in postgres too and the index in this
case should be on (transaction_id, transaction_timestamp).

>
>
> Additionally there is another scenario in which we have the requirement to
> have another timestamp column (say create_timestamp) to be added as part of
> the primary key along with transaction_id and we are going to query this
> table frequently by the column create_timestamp as a range predicate. And
> ofcourse we will also have the range predicate filter on partition key
> "transaction_timestamp". But we may or may not have join/filter on column
> transaction_id, so in this scenario we should go for
> (create_timestamp,transaction_id,transaction_timestamp). because
> "transaction_timestamp" is set as partition key , so putting it last
> doesn't harm us. Will this be the correct order or any other index order is
> appropriate?
>
>
>
In this case , the index should be on (
create_timestamp,transaction_id,transaction_timestamp), considering the
fact that you will always have queries with "create_timestamp" as predicate
and may not have transaction_id in the query predicate.