MIME-Version: 1.0
From: veem v <veema0000@gmail.com>
Date: Sat, 8 Jun 2024 19:03:07 +0530
Message-ID: <CAB+=1TX+Av1Fx+Q4YOmUGioUoa8TQ8kGa1h06zPSEona2az39A@mail.gmail.com>
Subject: How to create efficient index in this scenario?
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000008b83be061a60f550"
Archived-At: <https://www.postgresql.org/message-id/CAB%2B%3D1TX%2BAv1Fx%2BQ4YOmUGioUoa8TQ8kGa1h06zPSEona2az39A%40mail.gmail.com>
Precedence: bulk

--0000000000008b83be061a60f550
Content-Type: text/plain; charset="UTF-8"

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/

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?

Thanks and Regards
Veem

--0000000000008b83be061a60f550
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi ,=C2=A0<div>It&#39;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) , howeve=
r 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, tr=
ansaction_id). But which one should we go for, if both of the columns get u=
sed in all the queries?<br><br>We will always be using transaction_timestam=
p 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 =C2=A0as a leading colu=
mn in this index?<br><br>There is a blog below (which is for oracle), showi=
ng how the index should be chosen and it states , =C2=A0&quot;<i>Stick the =
columns you do range scans on last in the index, filters that get equality =
predicates should come first.</i> &quot;, and in that case we should have t=
he PK created as in the order (transaction_id,transaction_timestamp). It=
9;s because making the range predicate as a leading column won&#39;t help u=
se that as an access predicate but as an filter predicate thus will read mo=
re blocks and thus more IO. Does this hold true in postgres too?<br><br><a =
href=3D"https://ctandrewsayer.wordpress.com/2017/03/24/the-golden-rule-of-i=
ndexing/">https://ctandrewsayer.wordpress.com/2017/03/24/the-golden-rule-of=
-indexing/</a><br><br>Additionally there is another scenario in which we ha=
ve 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 r=
ange predicate. And ofcourse we will also have the range predicate filter o=
n partition key &quot;transaction_timestamp&quot;. But we may or may not ha=
ve join/filter on column transaction_id, so in this scenario we should go f=
or =C2=A0(create_timestamp,transaction_id,transaction_timestamp). because &=
quot;transaction_timestamp&quot; is set as partition key , so putting it la=
st doesn&#39;t harm us. Will this be the correct order or any other index o=
rder is appropriate?<br></div><div><br></div><div>Thanks and Regards</div><=
div>Veem</div></div>

--0000000000008b83be061a60f550--