explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mJCu

Settings
# exclusive inclusive rows x rows loops node
1. 24.194 5,320.594 ↓ 1,474.4 30,963 1

Unique (cost=265,665.58..265,666.53 rows=21 width=361) (actual time=5,284.142..5,320.594 rows=30,963 loops=1)

2. 115.961 5,296.400 ↓ 1,476.6 31,009 1

Sort (cost=265,665.58..265,665.63 rows=21 width=361) (actual time=5,284.141..5,296.400 rows=31,009 loops=1)

  • Sort Key: dt.observations, im.movement_date, dt.pos, dt.doc_number, dlt.cant, wl.name, wl._type, av.value_string, ((SubPlan 1)), ((SubPlan 2)), pro.product_client_id, pro.name, wl_source.name, ((((xue.rate * (dlt.cant)::double precision))::text || (xu.uomsymbol)::text)), wl_source._type, d_parent.doc_number, av_sap.value_integer
  • Sort Method: external merge Disk: 6192kB
3. 173.721 5,180.439 ↓ 1,476.6 31,009 1

Nested Loop Left Join (cost=222,375.94..265,665.12 rows=21 width=361) (actual time=3,817.835..5,180.439 rows=31,009 loops=1)

4. 25.578 4,107.457 ↓ 1,476.6 31,009 1

Nested Loop Left Join (cost=222,375.38..265,531.31 rows=21 width=187) (actual time=3,810.229..4,107.457 rows=31,009 loops=1)

5. 26.066 3,957.843 ↓ 1,476.6 31,009 1

Hash Join (cost=222,375.09..265,519.32 rows=21 width=178) (actual time=3,810.202..3,957.843 rows=31,009 loops=1)

  • Hash Cond: ((pro.product_id = xue.product_id) AND (xu.uom_id = xue.target_uom_id))
6. 87.587 3,931.698 ↓ 14.9 31,009 1

Nested Loop (cost=222,369.60..265,492.82 rows=2,080 width=186) (actual time=3,810.103..3,931.698 rows=31,009 loops=1)

  • Join Filter: (pro.presentation_uom_id = xu.uom_id)
  • Rows Removed by Join Filter: 248072
7. 0.049 0.049 ↑ 1.0 9 1

Index Scan using uom_uom_id_idx on uom xu (cost=0.14..12.27 rows=9 width=42) (actual time=0.027..0.049 rows=9 loops=1)

8. 114.797 3,844.062 ↓ 14.9 31,009 9

Materialize (cost=222,369.47..265,204.95 rows=2,080 width=144) (actual time=311.023..427.118 rows=31,009 loops=9)

9. 33.677 3,729.265 ↓ 14.9 31,009 1

Nested Loop Left Join (cost=222,369.47..265,194.55 rows=2,080 width=144) (actual time=2,799.133..3,729.265 rows=31,009 loops=1)

10. 19.489 3,633.570 ↓ 14.9 31,009 1

Hash Join (cost=222,369.18..264,538.05 rows=2,080 width=131) (actual time=2,799.111..3,633.570 rows=31,009 loops=1)

  • Hash Cond: (dlt.product_id = pro.product_id)
11. 11.143 3,613.459 ↓ 14.4 31,009 1

Nested Loop Left Join (cost=222,339.51..264,479.49 rows=2,157 width=88) (actual time=2,798.479..3,613.459 rows=31,009 loops=1)

12. 142.960 3,354.244 ↓ 14.4 31,009 1

Merge Join (cost=222,339.07..263,152.85 rows=2,157 width=77) (actual time=2,798.420..3,354.244 rows=31,009 loops=1)

  • Merge Cond: (im.product_instance_id = dlt.product_instance_id)
  • Join Filter: (dlt.warehouse_location_id = im.warehouse_location_id)
  • Rows Removed by Join Filter: 109643
13. 386.597 386.597 ↑ 1.0 716,184 1

Index Scan using instances_movements_product_instance_id_idx_1 on instances_movements im (cost=0.42..26,183.66 rows=726,737 width=16) (actual time=0.012..386.597 rows=716,184 loops=1)

14. 16.556 2,824.687 ↑ 1.6 140,660 1

Materialize (cost=222,324.31..223,435.53 rows=222,244 width=73) (actual time=2,794.788..2,824.687 rows=140,660 loops=1)

15. 49.649 2,808.131 ↑ 7.5 29,606 1

Sort (cost=222,324.31..222,879.92 rows=222,244 width=73) (actual time=2,794.785..2,808.131 rows=29,606 loops=1)

  • Sort Key: dlt.product_instance_id
  • Sort Method: external merge Disk: 2200kB
16. 86.230 2,758.482 ↑ 7.5 29,606 1

Hash Join (cost=4,159.21..192,710.06 rows=222,244 width=73) (actual time=60.963..2,758.482 rows=29,606 loops=1)

  • Hash Cond: (dlt.document_id = dt.document_id)
17. 1,472.506 2,619.695 ↓ 1.2 763,215 1

Hash Join (cost=1,411.14..185,417.39 rows=619,242 width=41) (actual time=8.221..2,619.695 rows=763,215 loops=1)

  • Hash Cond: (dlt.warehouse_source_location_id = wl.warehouse_location_id)
18. 1,139.019 1,139.019 ↓ 1.0 7,719,979 1

Seq Scan on document_line dlt (cost=0.00..148,879.97 rows=7,715,697 width=24) (actual time=0.022..1,139.019 rows=7,719,979 loops=1)

19. 0.672 8.170 ↓ 1.0 2,520 1

Hash (cost=1,379.69..1,379.69 rows=2,516 width=21) (actual time=8.170..8.170 rows=2,520 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 170kB
20. 7.498 7.498 ↓ 1.0 2,520 1

Seq Scan on warehouse_locations wl (cost=0.00..1,379.69 rows=2,516 width=21) (actual time=0.015..7.498 rows=2,520 loops=1)

  • Filter: ((name)::text ~~ 'C.%'::text)
  • Rows Removed by Filter: 26724
21. 9.413 52.557 ↑ 1.0 26,726 1

Hash (cost=2,410.79..2,410.79 rows=26,983 width=40) (actual time=52.557..52.557 rows=26,726 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1773kB
22. 43.144 43.144 ↑ 1.0 26,726 1

Seq Scan on document dt (cost=0.00..2,410.79 rows=26,983 width=40) (actual time=0.033..43.144 rows=26,726 loops=1)

  • Filter: ((type)::text = 'ORDER_OF_MOVE'::text)
  • Rows Removed by Filter: 48160
23. 248.072 248.072 ↑ 1.0 1 31,009

Index Scan using attributes_values_only_entity_id_idx on attributes_values av (cost=0.43..0.61 rows=1 width=15) (actual time=0.004..0.008 rows=1 loops=31,009)

  • Index Cond: (entity_id = dlt.product_instance_id)
  • Filter: (((entity_class)::text = 'ProductInstance'::text) AND (attributes_type_param_id = 3))
  • Rows Removed by Filter: 4
24. 0.338 0.622 ↓ 1.0 833 1

Hash (cost=19.30..19.30 rows=830 width=43) (actual time=0.622..0.622 rows=833 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 69kB
25. 0.284 0.284 ↓ 1.0 833 1

Seq Scan on products pro (cost=0.00..19.30 rows=830 width=43) (actual time=0.018..0.284 rows=833 loops=1)

26. 62.018 62.018 ↑ 1.0 1 31,009

Index Scan using warehouse_locations_warehouse_location_id_idx on warehouse_locations wl_source (cost=0.29..0.31 rows=1 width=21) (actual time=0.002..0.002 rows=1 loops=31,009)

  • Index Cond: (warehouse_location_id = dlt.warehouse_source_location_id)
27. 0.020 0.079 ↓ 1.0 78 1

Hash (cost=4.36..4.36 rows=75 width=16) (actual time=0.079..0.079 rows=78 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
28. 0.059 0.059 ↓ 1.0 78 1

Seq Scan on uom_equivalence xue (cost=0.00..4.36 rows=75 width=16) (actual time=0.021..0.059 rows=78 loops=1)

  • Filter: (source_uom_id = 1)
  • Rows Removed by Filter: 117
29. 124.036 124.036 ↑ 1.0 1 31,009

Index Scan using document_id_idx on document d_parent (cost=0.29..0.56 rows=1 width=13) (actual time=0.003..0.004 rows=1 loops=31,009)

  • Index Cond: (document_id = dt.document_parent_id)
30. 558.162 558.162 ↑ 1.0 1 31,009

Index Scan using attributes_values_entity_id_idx on attributes_values av_sap (cost=0.56..5.83 rows=1 width=8) (actual time=0.010..0.018 rows=1 loops=31,009)

  • Index Cond: ((entity_id = d_parent.document_id) AND ((entity_class)::text = 'OutputOrder'::text))
  • Filter: (attributes_type_param_id = 20)
  • Rows Removed by Filter: 5
31.          

SubPlan (for Nested Loop Left Join)

32. 217.063 217.063 ↑ 1.0 1 31,009

Result (cost=0.00..0.26 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=31,009)

33. 124.036 124.036 ↑ 1.0 1 31,009

Result (cost=0.00..0.26 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=31,009)

Planning time : 10.361 ms
Execution time : 5,326.326 ms