CODEWITHSUNDEEP
sqlpostgresqlindexing
xaroulis gekas
xaroulis gekas

Reputation: 165

Ideal order for index creation in Postgres

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?

Upvotes: 1

Views: 196

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

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.

Upvotes: 2

Related Questions