explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LQS4

Settings
# exclusive inclusive rows x rows loops node
1. 0.125 11,679.739 ↓ 144.0 144 1

Unique (cost=111,055.20..111,055.23 rows=1 width=389) (actual time=11,679.601..11,679.739 rows=144 loops=1)

2. 0.748 11,679.614 ↓ 154.0 154 1

Sort (cost=111,055.20..111,055.20 rows=1 width=389) (actual time=11,679.599..11,679.614 rows=154 loops=1)

  • Sort Key: d.observations, d.updated, d.pos, d.doc_number, pi.pieces, av.value_string, p.product_client_id, p.name, ((((xue.rate * (dl.cant)::double precision))::text || (xu.uomsymbol)::text)), d_parent.doc_number, ((SubPlan 1))
  • Sort Method: quicksort Memory: 65kB
3. 2.752 11,678.866 ↓ 154.0 154 1

Nested Loop Left Join (cost=611.53..111,055.19 rows=1 width=389) (actual time=141.351..11,678.866 rows=154 loops=1)

4. 0.457 11,661.330 ↓ 154.0 154 1

Nested Loop (cost=611.24..111,049.97 rows=1 width=161) (actual time=141.097..11,661.330 rows=154 loops=1)

  • Join Filter: ((p.product_id = xue.product_id) AND (xu.uom_id = xue.target_uom_id))
5. 0.443 11,660.103 ↓ 154.0 154 1

Nested Loop (cost=611.09..111,049.79 rows=1 width=169) (actual time=141.081..11,660.103 rows=154 loops=1)

6. 1,126.579 11,659.198 ↓ 154.0 154 1

Nested Loop Left Join (cost=610.96..111,049.61 rows=1 width=127) (actual time=141.056..11,659.198 rows=154 loops=1)

  • Filter: (COALESCE(ihp.product_instance_id, dl.product_instance_id) = pi.id)
  • Rows Removed by Filter: 6806558
7. 534.114 4,889.339 ↓ 15,503.5 2,821,640 1

Nested Loop (cost=610.67..110,980.88 rows=182 width=139) (actual time=13.636..4,889.339 rows=2,821,640 loops=1)

8. 18.083 38.401 ↓ 184.0 184 1

Nested Loop (cost=478.47..603.92 rows=1 width=87) (actual time=12.532..38.401 rows=184 loops=1)

  • Join Filter: (pi.product_id = p.product_id)
  • Rows Removed by Join Filter: 152904
9. 0.350 0.350 ↓ 1.0 832 1

Index Scan using products_presentation_uom_id_idx on products p (cost=0.28..74.02 rows=830 width=43) (actual time=0.044..0.350 rows=832 loops=1)

10. 7.872 19.968 ↓ 184.0 184 832

Materialize (cost=478.20..517.45 rows=1 width=44) (actual time=0.010..0.024 rows=184 loops=832)

11. 0.147 12.096 ↓ 184.0 184 1

Nested Loop (cost=478.20..517.45 rows=1 width=44) (actual time=7.868..12.096 rows=184 loops=1)

12. 0.054 10.477 ↓ 184.0 184 1

Nested Loop (cost=477.78..513.71 rows=1 width=32) (actual time=7.834..10.477 rows=184 loops=1)

13. 0.064 0.064 ↑ 1.0 1 1

Index Scan using warehouse_locations_name_idx on warehouse_locations wl (cost=0.29..8.31 rows=1 width=21) (actual time=0.049..0.064 rows=1 loops=1)

  • Index Cond: ((name)::text = 'PICK-4.20'::text)
  • Filter: ((_type)::text = 'picking'::text)
14. 2.634 10.359 ↓ 184.0 184 1

Bitmap Heap Scan on attributes_values av (cost=477.49..505.39 rows=1 width=19) (actual time=7.778..10.359 rows=184 loops=1)

  • Recheck Cond: ((value_integer = wl.warehouse_location_id) AND (attributes_type_param_id = 47))
  • Filter: ((entity_class)::text = 'ProductInstance'::text)
  • Rows Removed by Filter: 89
  • Heap Blocks: exact=206
15. 0.033 7.725 ↓ 0.0 0 1

BitmapAnd (cost=477.49..477.49 rows=7 width=0) (actual time=7.725..7.725 rows=0 loops=1)

16. 0.098 0.098 ↑ 9.0 283 1

Bitmap Index Scan on attributes_values_value_integer_idx (cost=0.00..55.48 rows=2,540 width=0) (actual time=0.098..0.098 rows=283 loops=1)

  • Index Cond: (value_integer = wl.warehouse_location_id)
17. 7.594 7.594 ↓ 2.2 48,047 1

Bitmap Index Scan on attributes_values_only_attributes_type_param_id_idx (cost=0.00..421.11 rows=21,423 width=0) (actual time=7.594..7.594 rows=48,047 loops=1)

  • Index Cond: (attributes_type_param_id = 47)
18. 1.472 1.472 ↑ 1.0 1 184

Index Scan using product_instances_id_idx on product_instances pi (cost=0.42..3.73 rows=1 width=12) (actual time=0.007..0.008 rows=1 loops=184)

  • Index Cond: (id = av.entity_id)
19. 965.264 4,316.824 ↑ 4.0 15,335 184

Nested Loop (cost=132.20..109,756.51 rows=62,044 width=52) (actual time=0.673..23.461 rows=15,335 loops=184)

20. 583.096 676.200 ↓ 6.3 3,635 184

Bitmap Heap Scan on document d (cost=131.76..1,484.17 rows=578 width=48) (actual time=0.663..3.675 rows=3,635 loops=184)

  • Recheck Cond: ((type)::text = 'PICKEO_ORDER'::text)
  • Filter: ((status)::text = 'Confirmada'::text)
  • Rows Removed by Filter: 63
  • Heap Blocks: exact=200928
21. 93.104 93.104 ↓ 1.1 3,954 184

Bitmap Index Scan on document_document_type_var_idx (cost=0.00..131.62 rows=3,627 width=0) (actual time=0.506..0.506 rows=3,954 loops=184)

  • Index Cond: ((type)::text = 'PICKEO_ORDER'::text)
22. 2,675.360 2,675.360 ↑ 485.5 4 668,840

Index Scan using document_line_document_id_idx on document_line dl (cost=0.43..167.90 rows=1,942 width=12) (actual time=0.003..0.004 rows=4 loops=668,840)

  • Index Cond: (document_id = d.document_id)
23. 5,643.280 5,643.280 ↑ 1.5 2 2,821,640

Index Scan using instance_has_parents_product_instance_parent_id_idx on instance_has_parents ihp (cost=0.29..0.34 rows=3 width=8) (actual time=0.001..0.002 rows=2 loops=2,821,640)

  • Index Cond: (product_instance_parent_id = dl.product_instance_id)
24. 0.462 0.462 ↑ 1.0 1 154

Index Scan using uom_uom_id_idx on uom xu (cost=0.14..0.17 rows=1 width=42) (actual time=0.003..0.003 rows=1 loops=154)

  • Index Cond: (uom_id = p.presentation_uom_id)
25. 0.770 0.770 ↑ 1.0 1 154

Index Scan using uom_equivalence_product_id_idx on uom_equivalence xue (cost=0.14..0.17 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=154)

  • Index Cond: ((product_id = pi.product_id) AND (source_uom_id = 1))
26. 1.232 1.232 ↑ 1.0 1 154

Index Scan using document_id_idx on document d_parent (cost=0.29..4.93 rows=1 width=13) (actual time=0.007..0.008 rows=1 loops=154)

  • Index Cond: (document_id = d.document_parent_id)
27.          

SubPlan (forNested Loop Left Join)

28. 13.552 13.552 ↑ 1.0 1 154

Result (cost=0.00..0.26 rows=1 width=32) (actual time=0.088..0.088 rows=1 loops=154)

Planning time : 8.641 ms
Execution time : 11,679.982 ms