jilowebsites.blogg.se - Postgresql left inner join

If only few rows are involved, correlated subqueries or an equivalent LATERAL subquery is typically (much) faster. If all or most rows from items are involved, aggregating in a subquery is typically (much) faster. Postgres is not smart enough to optimize much, as jjanes explained.

Postgres CTE optimization with nested json_build_object.

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

WHERE purchase_order_id IN (1, 2, 3) - repeat selective condition ! , SUM(grouped_items.total_quantity) AS total_quantity To get an actual performance boost, LEFT JOIN to the aggregating subquery, but repeat the (selective!) predicate of the outer query. Second method, using correlated subquery: SELECT po.id,ĮXPLAIN: HashAggregate (cost=76.43 rows=1242 width=16) (actual time=1.667.9.488 rows=1243 loops=1) Hash Cond: (items.purchase_order_id = po.id) But not sure if that means it actually SUM'ed the entire table in memory.ĮXPLAIN: GroupAggregate (cost=69.00 rows=1242 width=40) (actual time=165.099.166.321 rows=1242 loops=1) 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. 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?

I ran EXPLAIN ANALYZE but can't interpret it very well. The execution time was practically identical running both methods so it was difficult to see which one was faster. I wrote another query with a correlated SELECT subquery instead of a JOIN. 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. ) grouped_items ON po.id = grouped_items.purchase_order_id

SUM(grouped_items.total_quantity) AS total_quantity 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, In Postgres 12, I have a table purchase_orders and one for its items.