All geek questions in one place

Multiple indexes versus single index in multiple columns in postgresql

I could not get the final answers by reading some of the existing posts on this topic.

I have certain data in 100 places over the past 10 years. The table has about 800 million rows. First of all, I need to generate annual statistics for each location. Sometimes I need to generate monthly change statistics and hourly change statistics. I am wondering if I should generate two indexes - one for the location, the other - for the year or generate one index for both location and year. My primary key is currently a serial number (maybe I can use the location and timestamp as the primary key).

Thank.

+4
source share
2 answers

No matter how many indexes you create in relation to, only one of them will be used in a particular query (which depends on the query, statistics, etc.). Therefore, in your case, you will not get the cumulative advantage of creating two indexes on the same column. To get maximum performance from the index, I would suggest using a composite index (location, timestamp).

Please note that queries like this ... WHERE timestamp BETWEEN smth AND smthwill not use the index above, while queries like ... WHERE location = 'smth'or ... WHERE location = 'smth' AND timestamp BETWEEN smth AND smthwill. This is because the first attribute in the index is crucial for searching and sorting.

Do not forget to run

ANALYZE;

.

+5

(location, timestamp) , 2 . , .

+1

Source: https://habr.com/ru/post/1653461/

More articles:


All Articles