I want to query the last data from the database table below for the receive time of each iot_id.There are about 130 million records in the table iot_data.
iot data table:
create table public.iot_data(
iot_id integer not null receive_time timestamp (6) with time zone not null code integer not null primary
key (machine_id, receive_time)
);
iot master table:
create table public.iot_info (
iot_id serial not null
, model integer not null
, primary key (iot_id)
);
At the beginning, I used the following SQL. My Java service retrieval timed out.
WITH LatestMachineData AS (
SELECT
iot_id
, receive_time
, area
, ROW_NUMBER() OVER (PARTITION BY iot_id ORDER BY receive_time DESC) as rn
FROM
machine_data
)
SELECT
*
FROM
LatestMachineData
WHERE
rn = 1;
Then I asked the AI and got the following SQL.But service still timed out.
SELECT
md.*
FROM
iot_data md
inner join iot_info mi
on mi.iot_id = md.iot_id
INNER JOIN (
SELECT
iot_id
, MAX(receive_time) AS latest_receive_time
FROM
iot_data
GROUP BY
iot_id
) latest_data
ON mi.iot_id = latest_data.iot_id
AND md.receive_time = latest_data.latest_receive_time;
finally I try the following SQL, and it works very well.
select
*
from
iot_data md
where
(md.iot_id,md.receive_time) in (
SELECT
a.iot_id
, a.receive_time as receive_time
FROM
(
SELECT
mi.iot_id
, (
SELECT
receive_time
FROM
iot_data md
WHERE
mi.iot_id = md.iot_id
ORDER BY
receive_time DESC
LIMIT
1
) as receive_time
FROM
iot_info as mi
WHERE
mi.delete_flag = false
) a
where
a.receive_time is not null
)
The question is: why is ORDER BY LIMIT so faster then others ?
why is ORDER BY LIMIT so faster then others ?
You KoTora is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.