Here's the query:

SELECT DISTINCT ON(points.track_id)
  tracks_competitiontrackpoint points
  JOIN tracks_competitiontrack tracks ON points.track_id=tracks.track_ptr_id
  JOIN competitions_contestparticipant participants ON tracks.participant_id=participants.id
  tracks.task_id = 24 AND points.timestamp <= 1409663400 AND tracks.online = true
  points.track_id, points.timestamp desc, points.id;

Query plan:

Unique  (cost=103411.24..104217.95 rows=527 width=38) (actual time=2507.814..2558.081 rows=113 loops=1)
  ->  Sort  (cost=103411.24..103814.60 rows=161342 width=38) (actual time=2507.805..2541.076 rows=82022 loops=1)
        Sort Key: points.track_id, points."timestamp", points.id
        Sort Method: external merge  Disk: 3984kB
        ->  Hash Join  (cost=69.66..85041.85 rows=161342 width=38) (actual time=2253.401..2408.140 rows=82022 loops=1)
              Hash Cond: (points.track_id = tracks.track_ptr_id)
              ->  Seq Scan on tracks_competitiontrackpoint points  (cost=0.00..73678.24 rows=2581475 width=34) (actual time=0.711..1398.816 rows=2575197 loops=1)
                    Filter: ("timestamp" <= 1409663400)
              ->  Hash  (cost=69.10..69.10 rows=45 width=8) (actual time=1.831..1.831 rows=122 loops=1)
                    Buckets: 1024  Batches: 1  Memory Usage: 5kB
                    ->  Hash Join  (cost=12.60..69.10 rows=45 width=8) (actual time=0.522..1.738 rows=122 loops=1)
                          Hash Cond: (participants.id = tracks.participant_id)
                          ->  Seq Scan on competitions_contestparticipant participants  (cost=0.00..48.74 rows=974 width=8) (actual time=0.008..0.679 rows=976 loops=1)
                          ->  Hash  (cost=12.04..12.04 rows=45 width=8) (actual time=0.383..0.383 rows=122 loops=1)
                                Buckets: 1024  Batches: 1  Memory Usage: 5kB
                                ->  Bitmap Heap Scan on tracks_competitiontrack tracks  (cost=5.30..12.04 rows=45 width=8) (actual time=0.194..0.297 rows=122 loops=1)
                                      Filter: (online AND (task_id = 24))
                                      ->  Bitmap Index Scan on tracks_competitiontrack_online  (cost=0.00..5.29 rows=139 width=0) (actual time=0.144..0.144 rows=159 loops=1)
                                            Index Cond: (online = true)
  Total runtime: 2559.586 ms

Or highlighted results: http://explain.depesz.com/s/ENi

I use DISCTINCT ON to query the latest result for each tracks_competitiontrackpoint.track_id ordered by tracks_competitiontrackpoint.timestamp. Index is enabled on tracks_competitiontrackpoint.track_id, tracks_competitiontrackpoint.timestamp, tracks_competitiontrack.participant_id, tracks_competitiontrack.task_id - pretty much everything that. Still there'a sequential scan for two tables, one of which is taking a lot of time. Could you please explain why?

  • Which version of Postgres? And do you have an index on (track_id, timestamp desc, id)? Since you want to order by (and do the distinct on) on that, it might be considered. – ypercubeᵀᴹ Sep 3 '14 at 12:31
  • An index on tracks (task_id, online, track_id, participant_id) would also be useful, either with this query or a rewrite. – ypercubeᵀᴹ Sep 3 '14 at 12:34
  • Did them both, not working, unfortunately. (explain.depesz.com/s/mUI). Also, Postgres 9.3 – rocknrollnerd Sep 3 '14 at 12:55
  • The problem is rather about DISTINCT ON then joins, 'case a query with no joins at all also has poor performance: explain.depesz.com/s/EOt – rocknrollnerd Sep 3 '14 at 12:57
  • Table definitions with just the relevant columns to see constraints (FK, PK, NOT NULL) and data types would be helpful. (What you get with \d tbl in psql or original CREATE TABLE scripts). Cardinalities (How many rows in each table?). How many rows typically for each points.track_id and how many related rows in the joined tables each? Do WHERE conditions vary? If so, how? – Erwin Brandstetter Sep 3 '14 at 13:40

Since you are on 9.3, can you try this rewriting:

    ( SELECT track_ptr_id, participant_id
      FROM tracks_competitiontrack 
      WHERE task_id = 24
        AND online = true
    ) AS tracks 
    competitions_contestparticipant AS participants 
      ON tracks.participant_id = participants.id
    ( SELECT p.*
      FROM tracks_competitiontrackpoint AS p
      WHERE p.track_id = tracks.track_ptr_id
        AND p.timestamp <= 1409663400
      ORDER BY p.track_id, p.timestamp desc, p.id
      LIMIT 1
    ) AS points ON TRUE
    points.track_id ;

I think that with indexes on tracks (task_id, online, track_ptr_id, participant_id) and on pooints (track_id, timestamp desc, id), it would preform better, assuming that the condition (WHERE task_id = 24 AND online = true) restricts to a small number of rows.

  • The idea with LATERAL seems promising (once again), but there are a couple of implicit assumptions: tracks.track_ptr_id is unique in the subquery tracks? There is 0 or 1 participants.id for each tracks.participant_id? Also: p.track_id is redundant in ORDER BY of subquery points. – Erwin Brandstetter Sep 3 '14 at 18:19
  • @ErwinBrandstetter I guess I made some assumptions (only (tasks_id, track_id) needs to be unique though, not track_id alone. I thought this was plausible for a table named tracks.) Please post an answer (or advise that this is utterly wrong and I'll just remove it.) – ypercubeᵀᴹ Sep 3 '14 at 18:25
  • But you are right, my query may not be equivalent - if my assumptions are wrong. I should have that warning in the answer. – ypercubeᵀᴹ Sep 3 '14 at 18:27
  • I think your answer is good. My idea would be very similar, the upvote is mine. The OP didn't provide clarification yet. I would declare assumptions to clarify. track_ptr_id is unique in the subquery tracks effectively means uniqueness on (task_id, track_id, online). Point is, track_ptr_id must be unique in the derived table to make this work. The LEFT JOIN is also a deviation from the original query. – Erwin Brandstetter Sep 3 '14 at 18:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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