explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EquW

Settings
# exclusive inclusive rows x rows loops node
1. 0.828 3,163.835 ↓ 305.0 305 1

Sort (cost=66,364.60..66,364.60 rows=1 width=289) (actual time=3,163.699..3,163.835 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. 73.334 3,163.007 ↓ 305.0 305 1

HashAggregate (cost=66,364.55..66,364.59 rows=1 width=289) (actual time=3,161.630..3,163.007 rows=305 loops=1)

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

Nested Loop (cost=40,463.65..66,364.52 rows=1 width=289) (actual time=654.834..3,089.673 rows=10,948 loops=1)

  • Join Filter: ((p.product_id = ue.product_id) AND (u.uom_id = ue.target_uom_id))
4. 31.623 2,957.082 ↓ 10,948.0 10,948 1

Nested Loop (cost=40,463.50..66,364.34 rows=1 width=297) (actual time=654.816..2,957.082 rows=10,948 loops=1)

  • Join Filter: (im_1.product_instance_id = av.entity_id)
5. 35.662 2,706.479 ↓ 10,949.0 10,949 1

Nested Loop (cost=40,463.07..66,363.25 rows=1 width=305) (actual time=654.769..2,706.479 rows=10,949 loops=1)

6. 101.110 2,495.633 ↓ 10,949.0 10,949 1

Nested Loop Left Join (cost=40,462.78..66,359.17 rows=1 width=111) (actual time=652.507..2,495.633 rows=10,949 loops=1)

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

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

8. 71.898 1,565.784 ↓ 15.0 10,949 1

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

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

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

  • Group Key: im_1.product_instance_id
10. 901.373 901.373 ↑ 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.030..901.373 rows=567,315 loops=1)

11. 35.878 75.487 ↓ 10.4 10,950 1

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

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

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

  • Hash Cond: (pi.product_id = p.product_id)
13. 11.214 15.829 ↑ 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=4.932..15.829 rows=10,950 loops=1)

  • Recheck Cond: ((status)::text = ANY ('{Almacenada,"Lista para pickeo"}'::text[]))
  • Heap Blocks: exact=467
14. 4.615 4.615 ↑ 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=4.615..4.615 rows=10,998 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
16. 0.251 0.543 ↑ 1.0 82 1

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

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

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

18. 0.017 0.026 ↑ 1.0 9 1

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

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

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

20. 142.337 142.337 ↑ 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.012..0.013 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. 328.793 656.940 ↑ 72.9 19 10,949

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

  • Group Key: ihdl.product_instance_id
22. 5.096 328.147 ↑ 43.3 32 1

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

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

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

24. 121.865 213.791 ↑ 1.6 10,734 1

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

  • Hash Cond: (ihdl.product_instance_id = pi_1.id)
25. 75.292 75.292 ↑ 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..75.292 rows=263,511 loops=1)

26. 5.703 16.634 ↑ 1.0 10,950 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 513kB
27. 7.566 10.931 ↑ 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.567..10.931 rows=10,950 loops=1)

  • Recheck Cond: ((status)::text = ANY ('{Almacenada,"Lista para pickeo"}'::text[]))
  • Heap Blocks: exact=467
28. 3.365 3.365 ↑ 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.365..3.365 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.540 10.113 ↓ 1.0 4,867 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 236kB
31. 5.884 7.573 ↓ 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.115..7.573 rows=4,867 loops=1)

  • Recheck Cond: ((type)::text = 'OUTPUT_ORDER'::text)
  • Heap Blocks: exact=880
32. 1.689 1.689 ↓ 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.689..1.689 rows=4,868 loops=1)

  • Index Cond: ((type)::text = 'OUTPUT_ORDER'::text)
33. 175.184 175.184 ↑ 138.0 1 10,949

Index Scan using mv_warehouse_locations_with_warehouse_id_idx on mv_warehouse_locations_with_warehouse wld (cost=0.29..2.71 rows=138 width=202) (actual time=0.015..0.016 rows=1 loops=10,949)

  • Index Cond: (id = im.warehouse_location_id)
34. 218.980 218.980 ↑ 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.016..0.020 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. 87.584 87.584 ↑ 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.007..0.008 rows=1 loops=10,948)

  • Index Cond: ((product_id = pi.product_id) AND (source_uom_id = 1))
Planning time : 24.971 ms
Execution time : 3,164.754 ms