Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Shouldn't you be using "= 0" and not "!= 0"? AS far as I understood you wanted to find records, where all 3 values are 0 ? –  Sascha Rambeaud Mar 9 at 0:41
    
@SaschaRambeaud I meant all 3 values are not 0, sorry –  JohnMerlino Mar 9 at 0:46
    
Ok, fixed below. –  Sascha Rambeaud Mar 9 at 0:49

2 Answers 2

up vote 3 down vote accepted

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;
share|improve this answer
    
Did you verify that this computes faster? I cannot currently test it with Postgres, but on SQL-Server i frequently found that over(partition by..) is a lot faster than group by. Your code definitely looks nicer, though, but in my defense i can only say i didn't know about DISTINCT ON, seems to be postgreSQL specific. –  Sascha Rambeaud Mar 12 at 10:06
    
@SaschaRambeaud: Description and example seem to indicate "all three columns <> 0". And yes, I verified my assertion about performance (on multiple occasions). Details in the second linked answer. –  Erwin Brandstetter Mar 12 at 17:41
    
Thanks, good to know in case i ever have to work with PostgreSQL :). (I only checked out your first link earlier, sorry) –  Sascha Rambeaud Mar 12 at 18:06
  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
share|improve this answer
    
I am using postgresql. –  JohnMerlino Mar 9 at 0:14
    
Added an example using a window function. CTE, so you can filter for row_number() results. I don't know if you need to inner join to units because there can be unit_id values that have no match in the units table, because otherwise the join seems unnecessary? –  Sascha Rambeaud Mar 9 at 0:39
    
Also instead of select * from the CTE, you'd need to select the desired columns, to avoid getting the row number in results. –  Sascha Rambeaud Mar 9 at 0:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.