1

In my iot use case, I have a table which stores sensor readings. I am using the timescaledb extension.

I have the following table definition:

CREATE TABLE readings (
  id          BIGSERIAL      PRIMARY KEY,
  time        TIMESTAMPTZ       NOT NULL,
  value       DOUBLE PRECISION  NOT NULL,
  device      VARCHAR(255)      NOT NULL,
  sensor      VARCHAR(255)      NOT NULL,
  resolution  VARCHAR(255)      NOT NULL,
  UNIQUE(time, device, sensor, resolution)
);

I want last reported time for every (device,sensor) pair. Here is the query I am running:

select MAX(time), device, sensor from readings group by device, sensor

This query takes a significant time to complete. Is there any way to improve the performance? Or any better query to achieve the same result?

I tried doing this and it did not help:

create index if not exists readings_idx on readings (time, device, sensor, resolution);
2
  • Please follow the suggestions here: dba.stackexchange.com/tags/postgresql-performance/info. (After adding the index index suggested by Jasen). Also, how many distinct device-sensors are there compared to the number of time points?
    – jjanes
    Commented Oct 28, 2018 at 15:51
  • Not sure how timescale factors into this. In standard Postgres an index with leading (device,sensor) would help - combined with the right query. If you have additional tables holding unique devices / sensors / combinations of both, you have more options: stackoverflow.com/a/25536748/939860 But start by disclosing your version of Postgres. Commented Oct 30, 2018 at 12:29

1 Answer 1

2

(Timescale cofounder here)

It sounds like you are trying to perform a "last point" query, e.g., find the latest record submitted by each (device, sensor). The challenge with the query as above is that the database doesn't know when it has found the last device/sensor, so needs to scan back to the start of time to conclude there are no new devices around.

Much better is the following approach, which performs a LATERAL SCAN from a list of devices against the time-series records:

https://docs.timescale.com/latest/using-timescaledb/reading-data#last-point

Good luck, and you might also find our Slack group at https://slack.timescale.com useful for these types of questions.

1
  • Welcome to the site! Stick around. I went ahead and created a tag for TimeScaleDB. We would love if you would change the community link from StackOverflow to Database Administrators on timescale.com Commented Jan 13, 2019 at 2:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.