2

This question already has an answer here:

Using Postgres 9.4.
I have a table cartests with 5.5M rows. Each row is a car test:

\d log.cartests;
             Table "log.cartests"
   Column    |           Type           | Modifiers 
-------------+--------------------------+-----------
 carid       | integer                  | not null
 timestamp   | timestamp with time zone | not null
 exhaust     | varchar                  | 
 brakes      | varchar

 Indexes:
    "cartests_pkey" PRIMARY KEY, btree (id, "position", "timestamp")
    "cartests_carid_idx" btree (carid)
    "cartests_timestamp_idx" btree ("timestamp")

I need to retrieve the latest log for each car.

SELECT DISTINCT ON (carid) carid, timestamp, exhaust, brakes
FROM log.cartests
ORDER BY carid, timestamp DESC;

This one takes ~ 6 seconds. Running EXPLAIN ANALYZE gives:

Unique  (cost=933787.06..961490.34 rows=472 width=16) 
         (actual time=4951.347..6072.534 rows=476 loops=1)
  ->  Sort  (cost=933787.06..947638.70 rows=5540656 width=16) 
            (actual time=4951.345..5782.466 rows=5540656 loops=1)
        Sort Key: carid, "timestamp"
        Sort Method: external merge  Disk: 162504kB
        ->  Seq Scan on cartests  
            (cost=0.00..123810.56 rows=5540656 width=16)
            (actual time=0.009..620.988 rows=5540656 loops=1)
Planning time: 0.107 ms
Execution time: 6095.598 ms

Why must it sort if I have an index on both carid and timestamp?
Is there a better method to use here?

marked as duplicate by ypercubeᵀᴹ, Philᵀᴹ, RolandoMySQLDBA, Max Vernon, RLF Jan 22 '15 at 20:53

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

0

Why must it sort if I have an index on both carid and timestamp? Is there a better method to use here?

Because you don't have an index on both carid and timestamp! You have an index on carid, and a different index on timestamp. If you want the query to use both, you need an index on (carid, "timestamp") as ypercube mentioned.

Likewise, I'd try switching from the DISTINCT ORDER BY to a GROUP BY, and benchmarking each.

With every tuning question, try writing the query in a few different ways, and compare them against each other - how fast are they, and how much resources do they use?

  • While a multicolumn index is superior for the use case, Postgres can combine two indexes rather efficiently, too. GROUP BY is not very useful to get whole rows. – Erwin Brandstetter Jan 18 '15 at 2:20

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