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.

Given the table:

    Column    |            Type             
 id           | integer                     
 latitude     | numeric(9,6)                
 longitude    | numeric(9,6)                
 speed        | integer                     
 equipment_id | integer                     
 created_at   | timestamp without time zone
Indexes:
    "geoposition_records_pkey" PRIMARY KEY, btree (id)

The table has 20 million records that is not, relatively speaking, a large number. But it makes sequential scans slow.

How can I get the last record (max(created_at)) of each equipment_id?

I've tried both the following queries, with several variants that I've read through many answers of this topic:

select max(created_at),equipment_id from geoposition_records group by equipment_id;

select distinct on (equipment_id) equipment_id,created_at 
  from geoposition_records order by equipment_id, created_at desc;

I have also tried creating btree indexes for equipment_id,created_at but Postgres finds that using a seqscan is faster. Forcing enable_seqscan = off is of no use either since reading the index is as slow as the seq scan, probably worse.

The query must run periodically returning always the last.

Using Postgres 9.3.

Explain/analyze (with 1.7 million records):

set enable_seqscan=true;
explain analyze select max(created_at),equipment_id from geoposition_records group by equipment_id;
"HashAggregate  (cost=47803.77..47804.34 rows=57 width=12) (actual time=1935.536..1935.556 rows=58 loops=1)"
"  ->  Seq Scan on geoposition_records  (cost=0.00..39544.51 rows=1651851 width=12) (actual time=0.029..494.296 rows=1651851 loops=1)"
"Total runtime: 1935.632 ms"

set enable_seqscan=false;
explain analyze select max(created_at),equipment_id from geoposition_records group by equipment_id;
"GroupAggregate  (cost=0.00..2995933.57 rows=57 width=12) (actual time=222.034..11305.073 rows=58 loops=1)"
"  ->  Index Scan using geoposition_records_equipment_id_created_at_idx on geoposition_records  (cost=0.00..2987673.75 rows=1651851 width=12) (actual time=0.062..10248.703 rows=1651851 loops=1)"
"Total runtime: 11305.161 ms"
share|improve this question
1  
could you please define "performant?" –  swasheck Oct 21 '13 at 15:33
1  
The maximum value would be stored at the end of the index by default; did you try an index on (equipment_id, created_at desc) ? –  Colin 't Hart Oct 21 '13 at 15:33
    
@swasheck performant would be, get the result in less than half a second maybe? –  Feyd Oct 21 '13 at 15:38
    
@Colin yes, I've tried an index equipment_id, created_at desc, still postgres insists on using a seqscan –  Feyd Oct 21 '13 at 15:39
2  
I don't see any NOT NULL constraints in your table definition. What is the percentage of NULL values for each column? In particular, how many in equipment_id? –  Erwin Brandstetter Oct 23 '13 at 0:14

2 Answers 2

A plain multicolumn b-tree index should work after all:

CREATE INDEX foo_idx ON geoposition_records (equipment_id, created_at DESC);

Function

If you can't talk sense into the query planner, a function looping through the equipment table should do the trick. Looking up one equipment_id at a time uses the index. For a small number (57 judging from your EXPLAIN ANALYZE output), that's fast.
It's safe to assume you have an equipment table?

CREATE OR REPLACE FUNCTION f_latest_equip()
  RETURNS TABLE (equipment_id int, latest timestamp) AS
$func$
BEGIN
FOR equipment_id IN
   SELECT e.equipment_id FROM equipment e ORDER BY 1
LOOP
   SELECT max(g.created_at)
   FROM   geoposition_records g
   WHERE  g.equipment_id = f_latest_equip.equipment_id
                        -- prepend function name to disambiguate
   INTO   latest;

   RETURN NEXT;
END LOOP;
END  
$func$  LANGUAGE plpgsql STABLE;

Makes for a nice call, too:

SELECT * FROM f_latest_equip();

Correlated subqueries

Come to think of it, using this equipment table, you could to the dirty work with lowly correlated subqueries to great effect:

SELECT equipment_id
     ,(SELECT max(created_at)
       FROM   geoposition_records
       WHERE  equipment_id = eq.equipment_id) AS latest
FROM   equipment eq;

Performance is very good. Comparing max(), DISTINCT ON, the function and the correlated subqueries in this:

-> SQLfiddle demo

share|improve this answer
    
@Feyd: I would be very interested how these alternatives perform in your case. –  Erwin Brandstetter Oct 23 '13 at 1:48
    
Do correlated inline subqueries perform better than a LEFT JOIN with GROUP BY, in Postgres? –  ypercube Oct 23 '13 at 5:25
    
@ypercube: My experience is otherwise. But in this case the trick works to make use of the index. I am not entirely certain, why. Seems like a weakness of the query planner ... –  Erwin Brandstetter Oct 23 '13 at 6:00
    
Unrelated to this question/answer, can you comment on this comment by joop in SO question? I thought you had an answer or comment about that issue/efficiency but I can't find it. –  ypercube Oct 25 '13 at 11:19
1  
@ErwinBrandstetter this is something I've tried after the answer from Colin, but I can't stop thinking that this is a workaround that uses kind of a database side n+1 queries (not sure if that falls in the antipattern since there is no connection overhead)... I'm wondering now why group by exists at all, if it can't handle a few million records properly... It just doesn't make sense, there ?should? be something we're missing. Finally, the question has changed slightly and we are assuming the presence of an equipment table... I'd like to know if there is actually another way –  Feyd Oct 25 '13 at 13:55

Attempt 1

If

  1. I have a separate equipment table, and
  2. I have an index on geoposition_records(equipment_id, created_at desc)

then the following works for me:

select id as equipment_id, (select max(created_at)
                            from geoposition_records
                            where equipment_id = equipment.id
                           ) as max_created_at
from equipment;

I wasn't able to force PG to do a fast query to determine both the list of equipment_ids and the related max(created_at). But I'm going to try again tomorrow!

Attempt 2

I found this link: http://zogovic.com/post/44856908222/optimizing-postgresql-query-for-distinct-values Combining this technique with my query from attempt 1, I get:

WITH RECURSIVE equipment(id) AS (
    SELECT MIN(equipment_id) FROM geoposition_records
  UNION
    SELECT (
      SELECT equipment_id
      FROM geoposition_records
      WHERE equipment_id > equipment.id
      ORDER BY equipment_id
      LIMIT 1
    )
    FROM equipment WHERE id IS NOT NULL
)
SELECT id AS equipment_id, (SELECT MAX(created_at)
                            FROM geoposition_records
                            WHERE equipment_id = equipment.id
                           ) AS max_created_at
FROM equipment;

and this works FAST! But you need

  1. this ultra-contorted query form, and
  2. an index on geoposition_records(equipment_id, created_at desc).
share|improve this answer
    
It indeed works... but are you telling me that, I can not possibly select distinct (column) without having to read through the whole table? if I have a hash index on equipment_id, postgres can't look at that index to know because of the so called MCC? –  Feyd Oct 22 '13 at 14:54
    
Apparently Postgresql can't (yet) use an index to do a distinct. So for now I'd use the parent table if it exists, or create it if it doesn't. –  Colin 't Hart Oct 22 '13 at 17:20
1  
I found a technique to do this and updated my answer. –  Colin 't Hart Oct 22 '13 at 17:51

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.