Why is my indexed DISTINCT ON so much slower than my INNER JOIN? I have two tables, customers and purchases. There are a lot (thousands) of purchases per customer. I usually only need the most recent purchase for each customer, which is why I have the latest_purchase_id column and update it with a trigger whenever I add a purchase (see https://dba.stackexchange.com/a/243988/186435).

I'd rather not use a trigger, so I tried using a DISTINCT ON query with an index, but it's much slower and I'm not sure why.

Table customers:

       Column        |  Type    |                       Modifiers                        | Storage  | Stats target | Description
---------------------+----------+--------------------------------------------------------+----------+--------------+-------------
 id                  | integer  | not null default nextval('customers_id_seq'::regclass) | plain    |              |
 latest_purchase_id  | integer  |                                                        | plain    |              |
Indexes:
    "customers_pkey" PRIMARY KEY, btree (id)
    "customers_latest_purchase_id" btree (latest_purchase_id)
Foreign-key constraints:
    "customers_latest_purchase_fk" FOREIGN KEY (latest_purchase_id) REFERENCES purchases(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "purchases" CONSTRAINT "purchases_customer_fk" FOREIGN KEY (customer_id) REFERENCES customers(id) DEFERRABLE INITIALLY DEFERRED
Has OIDs: no


Table purchases:

     Column   |  Type     |                        Modifiers                       | Storage  | Stats target | Description
--------------+-----------+--------------------------------------------------------+----------+--------------+-------------
 id           | integer   | not null default nextval('purchases_id_seq'::regclass) | plain    |              |
 customer_id  | integer   |                                                        | plain    |              |
Indexes:
    "purchases_pkey" PRIMARY KEY, btree (id)
    "purchases_customer_id_id" btree (customer_id, id)
    "purchases_customer_id" btree (customer_id)
Foreign-key constraints:
    "purchases_customer_fk" FOREIGN KEY (customer_id) REFERENCES customers(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "customers" CONSTRAINT "customers_latest_purchase_id" FOREIGN KEY (latest_purchase_id) REFERENCES purchases(id) DEFERRABLE INITIALLY DEFERRED
Has OIDs: no


DISTINCT ON query:

EXPLAIN ANALYZE SELECT DISTINCT ON (customer_id) id, customer_id FROM purchases ORDER BY customer_id DESC, id DESC;
 Result  (cost=0.43..162516.37 rows=381 width=8) (actual time=0.050..1478.196 rows=823 loops=1)
   -&gt;  Unique  (cost=0.43..162516.37 rows=381 width=8) (actual time=0.047..1477.754 rows=823 loops=1)
         -&gt;  Index Only Scan Backward using purchases_customer_id_id on purchases  (cost=0.43..157850.96 rows=1866163 width=8) (actual time=0.045..1066.759 rows=1866132 loops=1)
               Heap Fetches: 1363529
 Planning Time: 0.096 ms
 Execution Time: 1478.408 ms


INNER JOIN query based on latest_purchase:

EXPLAIN ANALYZE SELECT c.id, p.id FROM customers c JOIN purchases p ON c.latest_purchase = p.id;
 Nested Loop  (cost=0.43..43877.27 rows=7594 width=8) (actual time=0.508..112.665 rows=755 loops=1)
   -&gt;  Seq Scan on customers d  (cost=0.00..213.94 rows=7594 width=8) (actual time=0.006..2.861 rows=7594 loops=1)
   -&gt;  Index Only Scan using customers_purchase_pkey on purchases p  (cost=0.43..5.75 rows=1 width=4) (actual time=0.014..0.014 rows=0 loops=7594)
         Index Cond: (id = c.latest_purchase)
         Heap Fetches: 583
 Planning Time: 1.032 ms
 Execution Time: 112.861 ms
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?