Postgresql select max query takes long time

I have the following structure in a table in my database. Below I list a part of the existing data.

https://drive.google.com/file/d/1DrQd6kb523wUZDaqhvkx6WBQxJ3wNT3n/view?usp=sharing

My data in text format

id    |       read_date        | value  | local_id | sensor_id 
---------+------------------------+--------+----------+-----------
 9732978 | 2020-11-19 14:14:30-03 |   25.2 |        1 |         1
 9732979 | 2020-11-19 14:14:30-03 |     26 |        1 |         8
 9732980 | 2020-11-19 14:14:30-03 |     65 |        1 |         2
 9732981 | 2020-11-19 14:14:30-03 |  150.5 |        1 |         4
 9732982 | 2020-11-19 14:14:30-03 |    6.2 |        1 |         5
 9732983 | 2020-11-19 14:14:30-03 |  926.9 |        1 |         7
 9732984 | 2020-11-19 14:14:30-03 |      1 |        1 |         3
 9732985 | 2020-11-19 14:14:30-03 |      0 |        1 |         3
 9732986 | 2020-11-19 14:14:30-03 |   31.2 |       10 |         1
 9732987 | 2020-11-19 14:14:30-03 |     32 |       10 |         8
 9732988 | 2020-11-19 14:14:30-03 |     65 |       10 |         2
 9732989 | 2020-11-19 14:14:30-03 |  150.5 |       10 |         4
 9732990 | 2020-11-19 14:14:30-03 |    7.5 |       10 |         5
 9732991 | 2020-11-19 14:14:30-03 | 1132.9 |       10 |         7
 9732992 | 2020-11-19 14:14:30-03 |      1 |       10 |         3

I basically have sensors for temperature, humidity, wind speed and wind direction, and I store this information every 30s in the bank. are already have a certain amount of records. I’m having a problem with a select, I don’t know if I’m doing it wrong, but I need to select the last value registered in the bank from the location and sensor I have this as select

select * 
from app_weatherdata 
where ((select max(id) 
       from app_weatherdata 
       where local_id=3 
         and sensor_id=1) =id);

But the return time for this consultation is too high, what is wrong with my consultation?

Regards

Answer

With this index

create index on app_weatherdata(sensor_id, local_id, id desc);

this should be quite fast:

select *
from app_weatherdata 
where local_id = 3
and sensor_id = 1
order by id desc
limit 1

On a test table with 25 million rows and 100 distinct sensors and 100 distinct locations, I get this execution plan:

Limit  (cost=0.56..4.60 rows=1 width=32) (actual time=0.021..0.021 rows=1 loops=1)
  Buffers: shared hit=5
  ->  Index Scan using app_weatherdata_sensor_id_local_id_id_idx on app_weatherdata  (cost=0.56..4627.36 rows=1147 width=32) (actual time=0.020..0.020 rows=1 loops=1)
        Index Cond: ((sensor_id = 1) AND (local_id = 3))
        Buffers: shared hit=5
Planning Time: 0.085 ms
Execution Time: 0.032 ms