5

In Postgres 12, I have a table purchase_orders and one for its items. I'm running a query that returns PO's for a given shop and a sum of items ordered on each PO:

SELECT po.id, 
       SUM(grouped_items.total_quantity) AS total_quantity
FROM purchase_orders po
LEFT JOIN (
  SELECT purchase_order_id, 
  SUM(quantity) AS total_quantity
  FROM items
  GROUP BY purchase_order_id
) grouped_items ON po.id = grouped_items.purchase_order_id

WHERE po.shop_id = 195
GROUP BY po.id

This query returns the desired result. The JOIN is in a subquery because there will be other JOINS to other tables, so this produces an already grouped table to join to.

I wrote another query with a correlated SELECT subquery instead of a JOIN. The execution time was practically identical running both methods so it was difficult to see which one was faster. I ran EXPLAIN ANALYZE but can't interpret it very well.

Question: In the example above, will Postgres process the entire items table of the subquery, and only after join with the purchase_orders? Or is it smart enough to filter down the set if items first?

The EXPLAIN report mentions "Seq Scan on Items..." which seemed to contain all rows in items, and then that gets reduced as it moves up the tree. But not sure if that means it actually SUM'ed the entire table in memory.

EXPLAIN:

GroupAggregate  (cost=6948.16..6973.00 rows=1242 width=40) (actual time=165.099..166.321 rows=1242 loops=1)
  Group Key: po.id
  Buffers: shared hit=4148
  ->  Sort  (cost=6948.16..6951.27 rows=1242 width=16) (actual time=165.090..165.406 rows=1242 loops=1)
        Sort Key: po.id
        Sort Method: quicksort  Memory: 107kB
        Buffers: shared hit=4148
        ->  Hash Right Join  (cost=6668.31..6884.34 rows=1242 width=16) (actual time=99.951..120.627 rows=1242 loops=1)
              Hash Cond: (items.purchase_order_id = po.id)
              Buffers: shared hit=4148
              ->  HashAggregate  (cost=5906.04..5993.80 rows=8776 width=16) (actual time=98.328..104.320 rows=14331 loops=1)
                    Group Key: items.purchase_order_id
                    Buffers: shared hit=3749
                    ->  Seq Scan on items  (cost=0.00..5187.03 rows=143803 width=12) (actual time=0.005..38.307 rows=143821 loops=1)
                          Buffers: shared hit=3749
              ->  Hash  (cost=746.74..746.74 rows=1242 width=8) (actual time=1.588..1.588 rows=1242 loops=1)
                    Buckets: 2048  Batches: 1  Memory Usage: 65kB
                    Buffers: shared hit=399
                    ->  Bitmap Heap Scan on purchase_orders po  (cost=33.91..746.74 rows=1242 width=8) (actual time=0.200..1.169 rows=1242 loops=1)
                          Recheck Cond: (shop_id = 195)
                          Heap Blocks: exact=392
                          Buffers: shared hit=399
                          ->  Bitmap Index Scan on index_purchase_orders_on_shop_id  (cost=0.00..33.60 rows=1242 width=0) (actual time=0.153..0.153 rows=1258 loops=1)
                                Index Cond: (shop_id = 195)
                                Buffers: shared hit=7
Planning time: 0.200 ms
Execution time: 166.665 ms

Second method, using correlated subquery:

SELECT po.id,
       (
           SELECT SUM(quantity)
           FROM items
           WHERE purchase_order_id = po.id
           GROUP BY purchase_order_id
       ) AS total_quantity
FROM purchase_orders po
WHERE shop_id = 195
GROUP BY po.id

EXPLAIN:

HashAggregate  (cost=749.84..25716.43 rows=1242 width=16) (actual time=1.667..9.488 rows=1243 loops=1)
  Group Key: po.id
  Buffers: shared hit=5603
  ->  Bitmap Heap Scan on purchase_orders po  (cost=33.91..746.74 rows=1242 width=8) (actual time=0.175..1.072 rows=1243 loops=1)
        Recheck Cond: (shop_id = 195)
        Heap Blocks: exact=390
        Buffers: shared hit=397
        ->  Bitmap Index Scan on index_purchase_orders_on_shop_id  (cost=0.00..33.60 rows=1242 width=0) (actual time=0.130..0.130 rows=1244 loops=1)
              Index Cond: (shop_id = 195)
              Buffers: shared hit=7
  SubPlan 1
    ->  GroupAggregate  (cost=0.42..20.09 rows=16 width=16) (actual time=0.005..0.005 rows=1 loops=1243)
          Group Key: items.purchase_order_id
          Buffers: shared hit=5206
          ->  Index Scan using index_items_on_purchase_order_id on items  (cost=0.42..19.85 rows=16 width=12) (actual time=0.003..0.004 rows=3 loops=1243)
                Index Cond: (purchase_order_id = po.id)
                Buffers: shared hit=5206
Planning time: 0.183 ms
Execution time: 9.831 ms
3

I've been looking into this issue a bit myself lately, and my conclusion is that the planner is not smart enough to optimize this particular thing. The correlated subselect will be executed once for each row even if that is a huge number of rows, while the uncorrelated one will be executed to completion even if only a few rows from it are needed.

It does know that one will be faster than the other (assuming the estimated row counts are reasonably correct) but it lacks the capacity to recognize the two formulations are identical and so choose between execution plans based on estimated performance.

Although in your case, the queries would not be identical because they handle missing rows in "items" differently. The correlated subselect would be identical to the left join, not the inner join.

| improve this answer | |
4

To get an actual performance boost, LEFT JOIN to the aggregating subquery, but repeat the (selective!) predicate of the outer query.

SELECT po.number
     , SUM(grouped_items.total_quantity) AS total_quantity
FROM   purchase_orders po
LEFT   JOIN (
   SELECT purchase_order_id AS id
        , SUM(quantity) AS total_quantity
   FROM   items
   WHERE  purchase_order_id IN (1, 2, 3)  -- repeat selective condition !
   GROUP  BY 1
   ) grouped_items USING (id)
WHERE  po.id IN (1, 2, 3)
GROUP  BY po.number;

Luckily, this is possible in your case: the predicate is applicable in the subquery. A bit more verbose, but typically delivers best performance regardless of what percentage of rows in items is involved. My rule of thumb is "aggregate first and join later". See:

Other cases are not so lucky. Then you have to decide which way to go. Postgres is not smart enough to optimize much, as jjanes explained. If all or most rows from items are involved, aggregating in a subquery is typically (much) faster. If only few rows are involved, correlated subqueries or an equivalent LATERAL subquery is typically (much) faster. See:

For only 3 rows from the outer query (WHERE po.id IN (1, 2, 3)), a correlated subquery can't go wrong. But I assume that's just simplification for the demo.

| improve this answer | |
  • Thanks! By mistake I wrote an incorrect WHERE clause. It's supposed to filter by po.shop_id. Since shop_id is not a column in items, I can't do that selective condition. I've edited the example now. – flavio Jun 29 at 8:20

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.