On Sun, 9 Jun 2024 at 09:45, Lok P <loknath...@gmail.com> wrote: > > On Sat, Jun 8, 2024 at 7:03 PM veem v <veema0...@gmail.com> wrote: > >> >> 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. >
So in the second scenario, if we keep the create_timestamp as the leading column ,is it not against the advice which the blog provides i.e. to not have the range predicate as the leading column in the index?
