Why is my indexed DISTINCT ON so much slower than my INNER JOIN?
This is the answer:


  There are a lot (thousands) of purchases per customer.


DISTINCT ON is fast for few purchases per customer. See:


Select first row in each GROUP BY group?


This should be much faster:

SELECT c.id AS customer_id, p.id AS purchase_id
FROM   customers c
LEFT   JOIN LATERAL (
   SELECT p.id
   FROM   purchases p
   WHERE  p.customer_id = c.id
   ORDER  BY p.id DESC
   LIMIT  1
   ) p ON true;


Subtle difference: every customer is in the result, even with no purchase at all.

Your index "purchases_customer_id_id" btree (customer_id, id) is good for this. An index on (customer_id, id DESC) would be even slightly better.

See:


Optimize GROUP BY query to retrieve latest row per user


Aside 1:

The 1st plan shows rows=823, the 2nd rows=755. Suggests you have purchases.customer_id that have no match in table customers, which typically shouldn't be. Add a FK constraint from purchases.customer_id to customers.id and make purchases.customer_id NOT NULL to enforce referential integrity.

Aside 2:

Lots of Heap Fetches at the end of each query plan. Are you vacuuming enough. See:


How does PostgreSQL perform ORDER BY with a b-tree index on the field?