Postgresql - Query data from a range and bring a boundary of lines but spaced

Asked

Viewed 1,141 times

0

Good morning Guys, all right?

I have a performance problem in a system and need to solve by customizing an SQL.

Let us imagine the following scenario: - A table with millions of records, each record has an ID (PK) and a TIMESTAMP.

  • Currently, I consult the data by the TIMESTAMP interval, for example, last week’s records.

  • We consider that in this one-week period has 1,000,000 (one million) records. Currently resgato these values to create a graph.

Let us agree that this is costly and somewhat stupid since from one record to the other there is minimal change of values and I need to assemble a line graph where the value of X is the TIMESTAMP.

DOUBT: You have to determine to the bank to consult me this interval but bring me up to 1,000 records only? But these records would have to come spaced out in order to understand the whole period, it would be a kind of sampling. Trying to process this in the system programming is sendable.

I leave you my thanks so you can help me, Gustavo Ferreira.

postgresql query optimizing select-limit

  • Tried to put a LIMIT 0.1000 for example?

  • No. I don’t want the first 1000 records, I want 1000 distributed among the 1,000,000.

2 answers

1


You can use Window Functions to take for example a line every 100 lines or you can use some kind of aggregation in the bank, such as transforming the TIMESTAMP at a date and group the values per day or per hour using an average aggregation (AVG)

  • I like it, I’ll look it up in both ways.

0

I decided as follows:

SELECT TO_CHAR(t.date, 'YYYY-MM-DD HH24:00:00') AS data, AVG(t.h24::NUMERIC) AS value FROM telemetria t WHERE t.date >= (CURRENT_TIMESTAMP - INTERVAL '168 HOUR') GROUP BY data ORDER BY data DESC

In this case I am searching data from 7 days ago. The data are grouped by date and time because there are many records with the same time.

When I need a Mairo period that 7 days I group per day, taking the daily average of the value I need.

Thanks for the contribution Renato.

Browser other questions tagged postgresql query optimizing select-limit

You are not signed in. Login or sign up in order to post.