explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VulV

Settings
# exclusive inclusive rows x rows loops node
1. 0.784 286,361.512 ↓ 305.0 305 1

Sort (cost=66,887.15..66,887.16 rows=1 width=289) (actual time=286,361.433..286,361.512 rows=305 loops=1)

  • Sort Key: av.value_date, p.product_client_id, ((sum(((pi.pieces)::double precision * ue.rate)) - (sum(COALESCE((sum(ihdl.cant)), '0'::numeric)))::double precision)) DESC
  • Sort Method: quicksort Memory: 67kB
2. 238.276 286,360.728 ↓ 305.0 305 1

HashAggregate (cost=66,887.11..66,887.14 rows=1 width=289) (actual time=286,359.428..286,360.728 rows=305 loops=1)

  • Group Key: av.value_date, wld.nave, p.product_client_id, u.uomsymbol
3. 105.619 286,122.452 ↓ 10,948.0 10,948 1

Nested Loop (cost=40,463.36..66,887.08 rows=1 width=289) (actual time=702.641..286,122.452 rows=10,948 loops=1)

  • Join Filter: ((p.product_id = ue.product_id) AND (u.uom_id = ue.target_uom_id))
4. 154.423 285,841.665 ↓ 10,948.0 10,948 1

Nested Loop (cost=40,463.21..66,886.90 rows=1 width=297) (actual time=702.622..285,841.665 rows=10,948 loops=1)

  • Join Filter: (im_1.product_instance_id = av.entity_id)
5. 144,244.723 285,106.945 ↓ 10,949.0 10,949 1

Nested Loop (cost=40,462.78..66,885.81 rows=1 width=305) (actual time=702.572..285,106.945 rows=10,949 loops=1)

  • Join Filter: (im.warehouse_location_id = wld.id)
  • Rows Removed by Join Filter: 301710644
6. 164.116 3,693.150 ↓ 10,949.0 10,949 1

Nested Loop Left Join (cost=40,462.78..66,359.17 rows=1 width=111) (actual time=694.086..3,693.150 rows=10,949 loops=1)

  • Join Filter: (ihdl.product_instance_id = pi.id)
  • Rows Removed by Join Filter: 208012
7. 103.421 2,696.910 ↓ 10,949.0 10,949 1

Nested Loop (cost=3,320.79..29,168.70 rows=1 width=79) (actual time=347.399..2,696.910 rows=10,949 loops=1)

8. 151.782 2,188.376 ↓ 15.0 10,949 1

Merge Join (cost=3,320.37..28,691.76 rows=728 width=79) (actual time=347.358..2,188.376 rows=10,949 loops=1)

  • Merge Cond: (im_1.product_instance_id = pi.id)
9. 751.170 1,905.059 ↓ 1.2 146,722 1

GroupAggregate (cost=0.42..23,838.46 rows=121,665 width=12) (actual time=0.076..1,905.059 rows=146,722 loops=1)

  • Group Key: im_1.product_instance_id
10. 1,153.889 1,153.889 ↑ 1.0 567,315 1

Index Scan using instances_movements_product_instance_id_idx_1 on instances_movements im_1 (cost=0.42..19,738.48 rows=576,667 width=12) (actual time=0.056..1,153.889 rows=567,315 loops=1)

11. 90.328 131.535 ↓ 10.4 10,950 1

Sort (cost=3,319.94..3,322.58 rows=1,054 width=67) (actual time=62.374..131.535 rows=10,950 loops=1)

  • Sort Key: pi.id
  • Sort Method: quicksort Memory: 1240kB
12. 22.983 41.207 ↓ 10.4 10,950 1

Hash Join (cost=613.24..3,267.02 rows=1,054 width=67) (actual time=7.452..41.207 rows=10,950 loops=1)

  • Hash Cond: (pi.product_id = p.product_id)
13. 11.234 17.238 ↑ 1.0 10,950 1

Bitmap Heap Scan on product_instances pi (cost=587.76..3,188.95 rows=11,215 width=12) (actual time=6.361..17.238 rows=10,950 loops=1)

  • Recheck Cond: ((status)::text = ANY ('{Almacenada,"Lista para pickeo"}'::text[]))
  • Heap Blocks: exact=467
14. 6.004 6.004 ↑ 1.0 10,998 1

Bitmap Index Scan on product_instances_status_idx (cost=0.00..584.96 rows=11,215 width=0) (actual time=6.004..6.004 rows=10,998 loops=1)

  • Index Cond: ((status)::text = ANY ('{Almacenada,"Lista para pickeo"}'::text[]))
15. 0.115 0.986 ↑ 1.0 82 1

Hash (cost=24.45..24.45 rows=82 width=55) (actual time=0.986..0.986 rows=82 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
16. 0.417 0.871 ↑ 1.0 82 1

Hash Join (cost=1.20..24.45 rows=82 width=55) (actual time=0.133..0.871 rows=82 loops=1)

  • Hash Cond: (p.presentation_uom_id = u.uom_id)
17. 0.419 0.419 ↑ 1.0 831 1

Seq Scan on products p (cost=0.00..19.31 rows=831 width=13) (actual time=0.021..0.419 rows=831 loops=1)

18. 0.019 0.035 ↑ 1.0 9 1

Hash (cost=1.09..1.09 rows=9 width=42) (actual time=0.035..0.035 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.016 0.016 ↑ 1.0 9 1

Seq Scan on uom u (cost=0.00..1.09 rows=9 width=42) (actual time=0.008..0.016 rows=9 loops=1)

20. 405.113 405.113 ↑ 1.0 1 10,949

Index Scan using instances_movements_movement_date_idx on instances_movements im (cost=0.42..0.65 rows=1 width=16) (actual time=0.030..0.037 rows=1 loops=10,949)

  • Index Cond: (movement_date = (max(im_1.movement_date)))
  • Filter: (im_1.product_instance_id = product_instance_id)
  • Rows Removed by Filter: 0
21. 485.764 832.124 ↑ 72.9 19 10,949

HashAggregate (cost=37,141.99..37,159.30 rows=1,385 width=12) (actual time=0.039..0.076 rows=19 loops=10,949)

  • Group Key: ihdl.product_instance_id
22. 4.928 346.360 ↑ 43.3 32 1

Hash Join (cost=4,658.55..37,135.06 rows=1,385 width=12) (actual time=345.245..346.360 rows=32 loops=1)

  • Hash Cond: (dl.document_id = d.document_id)
23. 23.633 331.734 ↑ 1.6 10,734 1

Nested Loop (cost=3,329.57..35,729.34 rows=16,773 width=16) (actual time=69.728..331.734 rows=10,734 loops=1)

24. 124.900 232.963 ↑ 1.6 10,734 1

Hash Join (cost=3,329.14..8,768.26 rows=16,773 width=16) (actual time=69.684..232.963 rows=10,734 loops=1)

  • Hash Cond: (ihdl.product_instance_id = pi_1.id)
25. 91.220 91.220 ↑ 1.0 263,511 1

Seq Scan on instances_has_document_lines ihdl (cost=0.00..4,282.92 rows=263,592 width=16) (actual time=0.017..91.220 rows=263,511 loops=1)

26. 5.736 16.843 ↑ 1.0 10,950 1

Hash (cost=3,188.95..3,188.95 rows=11,215 width=4) (actual time=16.843..16.843 rows=10,950 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 513kB
27. 7.781 11.107 ↑ 1.0 10,950 1

Bitmap Heap Scan on product_instances pi_1 (cost=587.76..3,188.95 rows=11,215 width=4) (actual time=3.529..11.107 rows=10,950 loops=1)

  • Recheck Cond: ((status)::text = ANY ('{Almacenada,"Lista para pickeo"}'::text[]))
  • Heap Blocks: exact=467
28. 3.326 3.326 ↑ 1.0 10,998 1

Bitmap Index Scan on product_instances_status_idx (cost=0.00..584.96 rows=11,215 width=0) (actual time=3.326..3.326 rows=10,998 loops=1)

  • Index Cond: ((status)::text = ANY ('{Almacenada,"Lista para pickeo"}'::text[]))
29. 75.138 75.138 ↑ 1.0 1 10,734

Index Scan using document_line_id_idx on document_line dl (cost=0.43..1.60 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=10,734)

  • Index Cond: (id = ihdl.document_line_id)
30. 2.331 9.698 ↓ 1.0 4,867 1

Hash (cost=1,268.84..1,268.84 rows=4,811 width=4) (actual time=9.698..9.698 rows=4,867 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 236kB
31. 5.671 7.367 ↓ 1.0 4,867 1

Bitmap Heap Scan on document d (cost=117.70..1,268.84 rows=4,811 width=4) (actual time=2.113..7.367 rows=4,867 loops=1)

  • Recheck Cond: ((type)::text = 'OUTPUT_ORDER'::text)
  • Heap Blocks: exact=880
32. 1.696 1.696 ↓ 1.0 4,868 1

Bitmap Index Scan on document_document_type_var_idx (cost=0.00..116.50 rows=4,811 width=0) (actual time=1.696..1.696 rows=4,868 loops=1)

  • Index Cond: ((type)::text = 'OUTPUT_ORDER'::text)
33. 137,169.072 137,169.072 ↓ 9.9 27,557 10,949

Seq Scan on mv_warehouse_locations_with_warehouse wld (cost=0.00..491.84 rows=2,784 width=202) (actual time=0.013..12.528 rows=27,557 loops=10,949)

34. 580.297 580.297 ↑ 1.0 1 10,949

Index Scan using attributes_values_entity_id_idx on attributes_values av (cost=0.43..1.08 rows=1 width=8) (actual time=0.047..0.053 rows=1 loops=10,949)

  • Index Cond: ((entity_id = im.product_instance_id) AND ((entity_class)::text = 'ProductInstance'::text))
  • Filter: ((value_date IS NOT NULL) AND (attributes_type_param_id = 2))
  • Rows Removed by Filter: 3
35. 175.168 175.168 ↑ 1.0 1 10,948

Index Scan using uom_equivalence_product_id_idx on uom_equivalence ue (cost=0.14..0.17 rows=1 width=16) (actual time=0.013..0.016 rows=1 loops=10,948)

  • Index Cond: ((product_id = pi.product_id) AND (source_uom_id = 1))
Planning time : 29.684 ms
Execution time : 286,362.714 ms