optimization of expensive subquery

Question

The following query gets the last report (where latitude and longitude and secs are <> 0) associated with the specified units:

SELECT 
    reports.* 
FROM
    reports 
    INNER JOIN 
        units 
        ON units.id = reports.unit_id 
WHERE 
    reports.unit_id IN (1111, 1112, 1113) 
    AND 
    (
        reports.id = 
        (
            SELECT reports.id 
            FROM reports
            WHERE reports.unit_id = units.id
            AND
            reports.time_secs != 0
            AND 
            reports.latitude != 0.0
            AND
            reports.longitude != 0.0
            ORDER BY time desc
            LIMIT 1
        )
    )

This query took several minutes to run, and I was wondering if there is an optimization I can do to it.

Answer 1

This can be considerably simpler and faster with DISTINCT ON:

SELECT DISTINCT ON (r.unit_id)
       r.* 
FROM   reports r
JOIN   units   u ON u.id = r.unit_id 
WHERE  r.unit_id IN (1111, 1112, 1113) 
AND    r.time_secs <> 0
AND    r.latitude  <> 0.0
AND    r.longitude <> 0.0
ORDER  BY r.unit_id, r.time DESC;

More explanation in these related answers:
How do I efficiently get "the most recent corresponding row"?
Select first row in each GROUP BY group? (on SO)

Minor detail: the standard SQL operator for "is not equal" is <>. Use that rather than != (which is accepted as well in Postgres).

The only possible purpose of joining to the table units in this query is to verify that one or more related rows exist. If reports.unit_id is bound to unit.id by foreign key (as the naming suggests) referential integrity is guaranteed and you can drop the table unit from the query completely. Just add: unit_id IS NOT NULL.

SELECT DISTINCT ON (unit_id) * 
FROM   reports
WHERE  unit_id IN (1111, 1112, 1113) 
AND    time_secs <> 0
AND    latitude  <> 0.0
AND    longitude <> 0.0
AND    unit_id IS NOT NULL
ORDER  BY unit_id, time DESC;

Answer 2

  WITH T1 as (SELECT 
            reports.* , row_number() over (partition by unit_id order by reports.time DESC) as RN
        FROM
            reports 
            INNER JOIN 
                units 
                ON units.id = reports.unit_id
        WHERE 
        reports.unit_id IN (1111, 1112, 1113) AND
        reports.time_secs <> 0 AND 
        reports.latitude <> 0.0 AND
        reports.longitude <> 0.0)
)
    SELECT * FROM T1 WHERE RN = 1