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