Ideal order for index creation in Postgres

Question

I have a table called timestamppsql.The column i want to create index on is called timestamp1 and id13.I am gonna test a bounch of queries but most of them look like this:

 select date_trunc('day',timestamp1) as day,avg(id13) from timestamppsql where timestamp1 >='2010-01-01 00:05:00' and timestamp1<='2015-01-01 00:05:00' group by day order by day desc,

and something like this

select id13 from timestamppsql where timestamp1 >='2010-01-01 00:05:00' and timestamp1<='2011-01-01 00:05:00',  
select avg(id13)::numeric(10,2) from timestamppsql where timestamp1>='2015-01-01 00:05:00' and timestamp1<='2015-01-01 10:30:00'

I will create an index :

create index project_index on timestamppsql(timestamp1,id13);

The question is which is the correct order for the index?timestamp1 first or id13? I know that the first index in order(from left to right) should be the one we use more,have the least duplicate values and the have most restrictive columns.Whats your opinion on that?

@jarlh I agree with you!i just want to make a conversation in case i miss something — xaroulis gekas
– xaroulis gekas, Commented Mar 17, 2021 at 12:36

Gordon Linoff · Accepted Answer · 2021-03-17 12:33:55Z

2

The WHERE clause is the dominant clause for index creation. You want the index to support filtering first. So, you want timestamp1 before id13` for these queries.

Note that in general Postgres visits the data pages even when an index is used, so id13 may not be important to use in an index. Postgres has improved optimization in more recent versions so in a table with no modifications, a covering index can be used. All that is to say that timestamp1 is really important as the first column in the index. id13 is much less important.

answered Mar 17, 2021 at 12:33

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

xaroulis gekas Over a year ago

.Thank you!i appreciate your help!

xaroulis gekas Over a year ago

.I have one more thing to ask you.I am using a table with about one million rows.I never delete something from my table or update it.I just import the data in the beginning and then i test queries.Should i worry about the statistics being up-to-date?

Laurenz Albe Over a year ago

No, you need to worry about the table being vacuumed often enough so that you get fast index-only scans. Upgrade to v13 and tune autovacuum_vacuum_insert_scale_factor.

Gordon Linoff Over a year ago

@LaurenzAlbe . . . Does this mean that the advice that I give about covering indexes in all other databases will now work in Postgres? Yay!

Laurenz Albe Over a year ago

@GordonLinoff Yes, since PostgreSQL 9.3, I think. The one catch is that you have to (auto-)VACUUM regularly, so that most blocks are marked "all-visible" in the visibility map, and a visibility check on the table is not needed for most rows.

Collectives™ on Stack Overflow

Ideal order for index creation in Postgres

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related