explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tier

Settings
# exclusive inclusive rows x rows loops node
1. 2,253.445 26,844.993 ↓ 1.0 310,902 1

Sort (cost=13,409,774.37..13,410,522.74 rows=299,350 width=1,286) (actual time=26,608.636..26,844.993 rows=310,902 loops=1)

  • Sort Key: sd.m_product_id, sd.c_uom_id, sd.m_attributesetinstance_id, sd.m_product_uom_id, sd.m_warehouse_id
  • Sort Method: external merge Disk: 127504kB
2. 3,188.979 24,591.548 ↓ 1.0 310,902 1

Merge Full Join (cost=393,886.45..13,046,945.28 rows=299,350 width=1,286) (actual time=8,550.903..24,591.548 rows=310,902 loops=1)

  • Merge Cond: (((s.m_product_id)::text = (sd.m_product_id)::text) AND ((s.m_attributesetinstance_id)::text = (sd.m_attributesetinstance_id)::text) AND (((COALESCE(s.m_product_uom_id, '-'::character varying))::text) = ((COALESCE(sd.m_product_uom_id, '-'::character varying))::text)) AND (((COALESCE(s.m_warehouse_id, '-'::character varying))::text) = ((COALESCE(sd.m_warehouse_id, '-'::character varying))::text)))
3. 1,442.801 3,483.921 ↑ 1.0 292,779 1

Sort (cost=195,333.28..196,081.66 rows=299,350 width=282) (actual time=3,333.153..3,483.921 rows=292,779 loops=1)

  • Sort Key: s.m_product_id, s.m_attributesetinstance_id, ((COALESCE(s.m_product_uom_id, '-'::character varying))::text), ((COALESCE(s.m_warehouse_id, '-'::character varying))::text)
  • Sort Method: external sort Disk: 37744kB
4. 69.878 2,041.120 ↑ 1.0 292,779 1

Subquery Scan on s (cost=36,429.08..88,291.19 rows=299,350 width=282) (actual time=415.727..2,041.120 rows=292,779 loops=1)

5. 829.855 1,971.242 ↑ 1.0 292,779 1

Finalize GroupAggregate (cost=36,429.08..85,297.69 rows=299,350 width=314) (actual time=415.724..1,971.242 rows=292,779 loops=1)

  • Group Key: sp.m_product_id, sp.c_uom_id, sp.m_attributesetinstance_id, sp.m_product_uom_id, sp.m_warehouse_id
6. 0.000 1,141.387 ↓ 1.2 292,781 1

Gather Merge (cost=36,429.08..71,133.17 rows=251,616 width=282) (actual time=415.708..1,141.387 rows=292,781 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 992.076 2,403.492 ↑ 1.3 97,594 3

Partial GroupAggregate (cost=35,429.06..41,090.42 rows=125,808 width=282) (actual time=385.555..801.164 rows=97,594 loops=3)

  • Group Key: sp.m_product_id, sp.c_uom_id, sp.m_attributesetinstance_id, sp.m_product_uom_id, sp.m_warehouse_id
8. 1,293.324 1,411.416 ↑ 1.3 97,595 3

Sort (cost=35,429.06..35,743.58 rows=125,808 width=224) (actual time=385.530..470.472 rows=97,595 loops=3)

  • Sort Key: sp.m_product_id, sp.c_uom_id, sp.m_attributesetinstance_id, sp.m_product_uom_id, sp.m_warehouse_id
  • Sort Method: external merge Disk: 8472kB
9. 118.092 118.092 ↑ 1.3 97,595 3

Parallel Seq Scan on m_storage_pending sp (cost=0.00..11,441.08 rows=125,808 width=224) (actual time=0.027..39.364 rows=97,595 loops=3)

10. 92.370 5,482.568 ↑ 1.0 268,230 1

Materialize (cost=198,553.16..199,894.52 rows=268,272 width=364) (actual time=5,217.084..5,482.568 rows=268,230 loops=1)

11. 2,078.068 5,390.198 ↑ 1.0 268,230 1

Sort (cost=198,553.16..199,223.84 rows=268,272 width=364) (actual time=5,217.043..5,390.198 rows=268,230 loops=1)

  • Sort Key: sd.m_product_id, sd.m_attributesetinstance_id, ((COALESCE(sd.m_product_uom_id, '-'::character varying))::text), ((COALESCE(sd.m_warehouse_id, '-'::character varying))::text)
  • Sort Method: external sort Disk: 43288kB
12. 123.819 3,312.130 ↑ 1.0 268,230 1

Subquery Scan on sd (cost=37,511.29..84,497.97 rows=268,272 width=364) (actual time=657.395..3,312.130 rows=268,230 loops=1)

13. 1,276.243 3,188.311 ↑ 1.0 268,230 1

Finalize GroupAggregate (cost=37,511.29..81,815.25 rows=268,272 width=364) (actual time=657.392..3,188.311 rows=268,230 loops=1)

  • Group Key: sdd.m_product_id, sdd.c_uom_id, sdd.m_attributesetinstance_id, sdd.m_product_uom_id, l.m_warehouse_id, sdd.m_locator_id
14. 0.000 1,912.068 ↓ 1.2 268,240 1

Gather Merge (cost=37,511.29..68,625.21 rows=223,560 width=364) (actual time=657.369..1,912.068 rows=268,240 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
15. 1,527.819 3,978.498 ↑ 1.3 89,413 3

Partial GroupAggregate (cost=36,511.26..41,820.81 rows=111,780 width=364) (actual time=650.202..1,326.166 rows=89,413 loops=3)

  • Group Key: sdd.m_product_id, sdd.c_uom_id, sdd.m_attributesetinstance_id, sdd.m_product_uom_id, l.m_warehouse_id, sdd.m_locator_id
16. 2,099.403 2,450.679 ↑ 1.3 89,417 3

Sort (cost=36,511.26..36,790.71 rows=111,780 width=306) (actual time=650.177..816.893 rows=89,417 loops=3)

  • Sort Key: sdd.m_product_id, sdd.c_uom_id, sdd.m_attributesetinstance_id, sdd.m_product_uom_id, l.m_warehouse_id, sdd.m_locator_id
  • Sort Method: external merge Disk: 10720kB
17. 192.951 351.276 ↑ 1.3 89,417 3

Hash Left Join (cost=1.18..11,090.84 rows=111,780 width=306) (actual time=0.203..117.092 rows=89,417 loops=3)

  • Hash Cond: ((sdd.m_locator_id)::text = (l.m_locator_id)::text)
18. 158.118 158.118 ↑ 1.3 89,417 3

Parallel Seq Scan on m_storage_detail sdd (cost=0.00..10,640.80 rows=111,780 width=224) (actual time=0.050..52.706 rows=89,417 loops=3)

19. 0.078 0.207 ↑ 1.0 8 3

Hash (cost=1.08..1.08 rows=8 width=164) (actual time=0.069..0.069 rows=8 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.129 0.129 ↑ 1.0 8 3

Seq Scan on m_locator l (cost=0.00..1.08 rows=8 width=164) (actual time=0.038..0.043 rows=8 loops=3)

21.          

SubPlan (for Merge Full Join)

22. 4,663.530 4,663.530 ↑ 1.0 1 310,902

Index Scan using m_product_key on m_product (cost=0.43..8.45 rows=1 width=33) (actual time=0.015..0.015 rows=1 loops=310,902)

  • Index Cond: ((m_product_id)::text = (COALESCE(sd.m_product_id, s.m_product_id))::text)
23. 2,176.314 2,176.314 ↑ 1.0 1 310,902

Index Scan using m_product_key on m_product m_product_1 (cost=0.43..8.45 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=310,902)

  • Index Cond: ((m_product_id)::text = (COALESCE(sd.m_product_id, s.m_product_id))::text)
24. 1,865.412 1,865.412 ↑ 1.0 1 310,902

Index Scan using m_product_key on m_product m_product_2 (cost=0.43..8.45 rows=1 width=18) (actual time=0.006..0.006 rows=1 loops=310,902)

  • Index Cond: ((m_product_id)::text = (COALESCE(sd.m_product_id, s.m_product_id))::text)
25. 1,865.412 1,865.412 ↑ 1.0 1 310,902

Index Scan using m_product_key on m_product m_product_3 (cost=0.43..8.45 rows=1 width=17) (actual time=0.006..0.006 rows=1 loops=310,902)

  • Index Cond: ((m_product_id)::text = (COALESCE(sd.m_product_id, s.m_product_id))::text)
26. 1,865.412 1,865.412 ↑ 1.0 1 310,902

Index Scan using m_product_key on m_product m_product_4 (cost=0.43..8.45 rows=1 width=2) (actual time=0.006..0.006 rows=1 loops=310,902)

  • Index Cond: ((m_product_id)::text = (COALESCE(sd.m_product_id, s.m_product_id))::text)
Planning time : 1.045 ms
Execution time : 26,993.265 ms