1. 15

  2. 4

    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
      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.

    1. 1

      Hey, thanks for your comment! Haven’t investigated this case yet, as we mostly display single couriers or if we don’t we make multiple requests (at the moment either way). Doing the index on descending is pretty nice, I feel like I should try that out.

      The different index types as well - true I didn’t investigate them here at all. I usually only do when my current solution won’t help anymore 😅 I should read up about them again!

    2. 4

      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”.

      1. 5

        To add to that, explain.depesz.com is really helpful.

        1. 1

          Thanks, in fact I wondered about that because it seemed weird. 🤦‍♂

          Too far inbetween EXPLAIN ANALYZE’s for me… perhaps luckily? :D

        2. 3

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

          1. 1

            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.

            1. 1

              Interesting! Thanks - do you have a link with more explanation that I could read and link to?