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 ?