2

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)
   ->  Unique  (cost=0.43..162516.37 rows=381 width=8) (actual time=0.047..1477.754 rows=823 loops=1)
         ->  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)
   ->  Seq Scan on customers d  (cost=0.00..213.94 rows=7594 width=8) (actual time=0.006..2.861 rows=7594 loops=1)
   ->  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
2

This is the answer:

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

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

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:

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:

| improve this answer | |
  • Ah, that seems to be working much better. It's really terrible without the right index, but that's to be expected. – Jonathan Richards May 18 at 22:35
  • @JonathanRichards: Cool. I added some more. – Erwin Brandstetter May 18 at 22:59
  • Thanks for the asides. The code is obfuscated, so the constraints are working as intended. The heap fetches are just because it's an insert-only table. Vacuuming manually to build the visibility map helps a bit, but doesn't make a big difference. I'm not bothering with a fancier solution since it's going to be fixed in postgres 13. – Jonathan Richards Jun 1 at 18:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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