Whenever I arrive at a point where the answer to “why did it take so long” is “the underlying query took this much time”, the next step for me is see what query was generated exactly and what the plan is (EXPLAIN), with actual timings of the execution if possible (EXPLAIN ANALYZE).

I wonder if the newly created index will help when you want to fetch multiple courier_ids each with their most recent location. With a query like

SELECT cl.*
FROM couriers c
CROSS JOIN LATERAL (
  SELECT id, courier_id, location, time, accuracy, inserted_at, updated_at
  FROM courier_locatins cl
  WHERE cl.courier_id = c.id
  ORDER BY time DESC 
  LIMIT 1
) AS cl
WHERE c.id IN (...)

it might be even possible to use the old two indexes (perhaps with the condition that the index on column “time” be created in reversed order; CREATE INDEX courier_location_time_btree ON courier_locations USING btree (time DESC). The multicolumn index would likely benefit from descending order as well. Thinking about it further, a BRIN index might be better still).

There is a lot of guessing in this comment because I don’t have the data and I lack the intuition to know better how the query planner would work. There are people in #postgresql on Freenode who could tell just from looking at your case and after getting a few answers from you.

Looks like you have a misunderstanding of EXPLAIN ANALYSE’s output. The first step in the query plan for DB view is the bitmap index scan, then bitmap heapscan, then the sort; not the other way around.

The order is “inside out”.

Elixir is cool, but this could have skipped all of that and just focused on the SQL and Postgres.

We can define indexes on multiple columns and it’s important that the most limiting index is the leftmost one. As we usually scope by couriers, we’ll make courier_id the left most.

Also worth mentioning that range indexes like date/time should always be the last column in a compound index if you can afford it, so the range is densely stored.