How to properly index and query time series data in Postgres?

Question

I'm experimenting with Postgres recently instead of using Bigquery.

My table transactions_all structure

access_id (text)
serial_no (text)
transaction_id (text)
date_local (timestamp)

and an index (BTREE, condition (((date_local)::date), serial_no))

When I'm loading 500.000 rows for one month into this table, performance is okay to query the last 2 days like this

SELECT * 
FROM transactions_all 
WHERE DATE(date_local) BETWEEN CURRENT_DATE - INTERVAL '1 day'  AND CURRENT_DATE 
 AND access_id = 'accessid1' 
 and serial_no = 'IO99267637'

But if I'm selecting the last 21 days like this

SELECT * 
FROM transactions_all 
WHERE DATE(date_local) BETWEEN CURRENT_DATE - INTERVAL '20 day'  AND CURRENT_DATE 
  AND access_id = 'accessid1' 
  and serial_no = 'IO99267637'

then fetching the data takes multiple seconds instead of milliseconds.

Is this a normal behaviour or am I using the wrong index?

How many rows will you get when you use the last 21 days' query. — D-Shih
– D-Shih, Commented Jan 27, 2021 at 7:18
Please edit your question and add the execution plan for the slow query generated using explain (analyze, buffers, format text) (not just a "simple" explain) as formatted text and make sure you preserve the indention of the plan. Paste the text, then put ``` on the line before the plan and on a line after the plan. — user330315
– user330315, Commented Jan 27, 2021 at 7:27
Try date_local::date (the expression used in the index) instead of DATE(date_local) — user330315
– user330315, Commented Jan 27, 2021 at 7:29

Laurenz Albe · Accepted Answer · 2021-01-27 07:41:33Z

8

Your index columns are in the wrong order. In the front you need those expressions that are used with the = operator in the WHERE condition, because index columns after the column that is used with a different operator can no longer be used.

To understand that, imagine a phone book where the names are ordered by (last name, first name). Then consider that it is easy to find all entries with last name "Miller" and a first name less than "J": you just read the "Miller" entries until you hit "J". Now consider that the task is to find all "Joe"s whole last name is less than "M": you have to scan all entries up to "M", and it doesn't help you much that the first names are sorted too.

So use an index like

CREATE INDEX ON transactions_all (serial_no, access_id, date(date_local));

answered Jan 27, 2021 at 7:41

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

1 Comment

Marc H. Over a year ago

Thank you for this great explanation! It put this index onto my table and it increased the performance dramatically.

Collectives™ on Stack Overflow

How to properly index and query time series data in Postgres?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related