explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nzOV : Optimization for: Optimization for: plan #sJ4P; plan #E0pS

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.008 2,462.934 ↑ 1.0 3 1

Subquery Scan on f (cost=67,061.81..11,584,733.44 rows=3 width=370) (actual time=1,006.824..2,462.934 rows=3 loops=1)

2. 0.009 281.734 ↑ 1.0 3 1

Limit (cost=67,061.81..67,143.11 rows=3 width=370) (actual time=281.261..281.734 rows=3 loops=1)

3. 0.728 281.725 ↑ 89.0 3 1

Nested Loop Left Join (cost=67,061.81..74,297.56 rows=267 width=370) (actual time=281.259..281.725 rows=3 loops=1)

  • Join Filter: (((stock.m_attributesetinstance_id)::text = (p.em_opxpatr_valor_atributos_id)::text) AND ((stock.m_product_id)::text = (p.m_product_id)::text))
4. 0.005 27.914 ↑ 89.0 3 1

Nested Loop (cost=41.78..7,206.07 rows=267 width=240) (actual time=27.896..27.914 rows=3 loops=1)

5. 0.004 27.793 ↑ 1.0 1 1

Nested Loop (cost=6.04..3,546.77 rows=1 width=31) (actual time=27.793..27.793 rows=1 loops=1)

  • Join Filter: ((prod.m_product_category_id)::text = (pc.m_product_category_id)::text)
6. 0.057 0.057 ↑ 1.0 1 1

Seq Scan on m_product_category pc (cost=0.00..3.74 rows=1 width=33) (actual time=0.057..0.057 rows=1 loops=1)

  • Filter: (em_opxdes_categoria_vehiculo = 'Y'::bpchar)
  • Rows Removed by Filter: 39
7. 0.003 27.732 ↑ 17.0 1 1

Nested Loop (cost=6.04..3,542.82 rows=17 width=64) (actual time=27.731..27.732 rows=1 loops=1)

8. 0.007 27.719 ↑ 17.0 1 1

Nested Loop (cost=5.76..3,495.48 rows=17 width=64) (actual time=27.719..27.719 rows=1 loops=1)

9. 0.001 27.682 ↑ 1.0 1 1

Nested Loop (cost=0.42..3,033.31 rows=1 width=64) (actual time=27.682..27.682 rows=1 loops=1)

10. 0.051 0.051 ↑ 1.0 1 1

Index Only Scan using opxdes_vehiculo_pkey on opxdes_vehiculo veh (cost=0.42..8.44 rows=1 width=31) (actual time=0.051..0.051 rows=1 loops=1)

  • Index Cond: (opxdes_vehiculo_id = 'E3E19AD7BC1642CA8880B33E5DC28993'::text)
  • Heap Fetches: 1
11. 27.630 27.630 ↑ 1.0 1 1

Seq Scan on m_refinventory box (cost=0.00..3,024.86 rows=1 width=66) (actual time=27.629..27.630 rows=1 loops=1)

  • Filter: (((em_opxawo_vehiculo)::text = 'E3E19AD7BC1642CA8880B33E5DC28993'::text) AND (em_awobg_contenedor = 'N'::bpchar))
  • Rows Removed by Filter: 64,569
12. 0.007 0.030 ↑ 118.0 1 1

Bitmap Heap Scan on m_storage_detail sd (cost=5.34..460.99 rows=118 width=66) (actual time=0.030..0.030 rows=1 loops=1)

  • Recheck Cond: ((m_refinventory_id)::text = (box.m_refinventory_id)::text)
  • Filter: (qtyonhand > '0'::numeric)
  • Heap Blocks: exact=1
13. 0.023 0.023 ↑ 118.0 1 1

Bitmap Index Scan on m_storagedet_refinventory (cost=0.00..5.31 rows=118 width=0) (actual time=0.023..0.023 rows=1 loops=1)

  • Index Cond: ((m_refinventory_id)::text = (box.m_refinventory_id)::text)
14. 0.010 0.010 ↑ 1.0 1 1

Index Scan using m_product_key on m_product prod (cost=0.28..2.78 rows=1 width=66) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: ((m_product_id)::text = (sd.m_product_id)::text)
15. 0.028 0.116 ↑ 308.7 3 1

Bitmap Heap Scan on opxdes_vehiculo_pieza p (cost=35.74..3,650.03 rows=926 width=240) (actual time=0.100..0.116 rows=3 loops=1)

  • Recheck Cond: ((opxdes_vehiculo_id)::text = 'E3E19AD7BC1642CA8880B33E5DC28993'::text)
  • Heap Blocks: exact=3
16. 0.088 0.088 ↓ 1.0 952 1

Bitmap Index Scan on opxdes_vehiculo_pieza_idx (cost=0.00..35.51 rows=926 width=0) (actual time=0.088..0.088 rows=952 loops=1)

  • Index Cond: ((opxdes_vehiculo_id)::text = 'E3E19AD7BC1642CA8880B33E5DC28993'::text)
17. 0.002 253.083 ↓ 0.0 0 3

Materialize (cost=67,020.03..67,020.07 rows=1 width=98) (actual time=84.361..84.361 rows=0 loops=3)

18. 0.002 253.081 ↓ 0.0 0 1

GroupAggregate (cost=67,020.03..67,020.06 rows=1 width=98) (actual time=253.081..253.081 rows=0 loops=1)

  • Group Key: stock.m_product_id, stock.m_attributesetinstance_id
19. 0.009 253.079 ↓ 0.0 0 1

Sort (cost=67,020.03..67,020.04 rows=1 width=70) (actual time=253.079..253.079 rows=0 loops=1)

  • Sort Key: stock.m_product_id, stock.m_attributesetinstance_id
  • Sort Method: quicksort Memory: 25kB
20. 253.070 253.070 ↓ 0.0 0 1

Seq Scan on m_storage_detail stock (cost=0.00..67,020.02 rows=1 width=70) (actual time=253.070..253.070 rows=0 loops=1)

  • Filter: ((qtyonhand > '0'::numeric) AND (qtyorderonhand <> '0'::numeric) AND (reservedqty <> '0'::numeric) AND (allocatedqty <> '0'::numeric))
  • Rows Removed by Filter: 1,099,201
21.          

SubPlan (for Subquery Scan)

22. 0.030 2,181.192 ↑ 1.0 1 3

Aggregate (cost=3,839,196.76..3,839,196.77 rows=1 width=32) (actual time=727.064..727.064 rows=1 loops=3)

23. 15.576 2,181.162 ↑ 6.8 5 3

Subquery Scan on ff (cost=5,078.65..3,839,196.67 rows=34 width=32) (actual time=300.232..727.054 rows=5 loops=3)

  • Filter: ((SubPlan 1) = f.atrib_tpv)
  • Rows Removed by Filter: 7,500
24. 17.982 49.176 ↓ 1.1 7,505 3

HashAggregate (cost=5,078.65..5,163.03 rows=6,751 width=65) (actual time=12.022..16.392 rows=7,505 loops=3)

  • Group Key: ol_1.c_orderline_id
25. 5.721 31.194 ↓ 2.2 15,010 1

Hash Join (cost=2,110.54..5,044.89 rows=6,751 width=37) (actual time=14.992..31.194 rows=15,010 loops=1)

  • Hash Cond: ((ol_1.c_order_id)::text = (o.c_order_id)::text)
26. 11.071 11.071 ↑ 1.0 22,752 1

Seq Scan on c_orderline ol_1 (cost=0.00..2,781.52 rows=22,752 width=70) (actual time=0.007..11.071 rows=22,752 loops=1)

27. 1.279 14.402 ↓ 2.2 7,686 1

Hash (cost=2,065.93..2,065.93 rows=3,569 width=33) (actual time=14.402..14.402 rows=7,686 loops=1)

  • Buckets: 8,192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 552kB
28. 5.926 13.123 ↓ 2.2 7,686 1

Nested Loop (cost=0.00..2,065.93 rows=3,569 width=33) (actual time=0.021..13.123 rows=7,686 loops=1)

  • Join Filter: (date_part('month'::text, age(now(), (o.dateordered)::timestamp with time zone)) >= (COALESCE(conf.meses_a_actualziar, '1'::numeric))::double precision)
  • Rows Removed by Join Filter: 574
29. 7.197 7.197 ↑ 1.3 4,130 1

Seq Scan on c_order o (cost=0.00..1,770.43 rows=5,354 width=41) (actual time=0.006..7.197 rows=4,130 loops=1)

  • Filter: (((docstatus)::text = 'CO'::text) AND (issotrx = 'Y'::bpchar))
  • Rows Removed by Filter: 7,899
30. 0.000 0.000 ↑ 1.0 2 4,130

Materialize (cost=0.00..1.03 rows=2 width=32) (actual time=0.000..0.000 rows=2 loops=4,130)

31. 0.006 0.006 ↑ 1.0 2 1

Seq Scan on opxesde_configuracion conf (cost=0.00..1.02 rows=2 width=32) (actual time=0.005..0.006 rows=2 loops=1)

32.          

SubPlan (for Subquery Scan)

33. 22.515 2,116.410 ↑ 1.0 1 22,515

Aggregate (cost=567.90..567.91 rows=1 width=32) (actual time=0.094..0.094 rows=1 loops=22,515)

34. 23.304 2,093.895 ↓ 2.0 2 22,515

Nested Loop Left Join (cost=2.09..567.88 rows=1 width=45) (actual time=0.029..0.093 rows=2 loops=22,515)

35. 14.709 1,913.775 ↓ 2.0 2 22,515

Nested Loop (cost=1.67..560.08 rows=1 width=68) (actual time=0.026..0.085 rows=2 loops=22,515)

36. 22.515 315.210 ↑ 1.0 1 22,515

Nested Loop (cost=0.97..17.01 rows=1 width=66) (actual time=0.014..0.014 rows=1 loops=22,515)

37. 112.575 112.575 ↑ 1.0 1 22,515

Index Scan using c_orderline_key on c_orderline ol (cost=0.41..8.43 rows=1 width=33) (actual time=0.005..0.005 rows=1 loops=22,515)

  • Index Cond: ((c_orderline_id)::text = (ff.c_orderline_id)::text)
38. 180.120 180.120 ↑ 1.0 1 22,515

Index Only Scan using m_attributesetinstance_key on m_attributesetinstance aol (cost=0.56..8.58 rows=1 width=33) (actual time=0.008..0.008 rows=1 loops=22,515)

  • Index Cond: (m_attributesetinstance_id = (ol.m_attributesetinstance_id)::text)
  • Heap Fetches: 15,528
39. 0.000 1,583.856 ↑ 39.0 3 15,528

Nested Loop (cost=0.70..541.90 rows=117 width=101) (actual time=0.016..0.102 rows=3 loops=15,528)

40. 403.728 403.728 ↑ 1.0 13 15,528

Seq Scan on m_attribute attr (cost=0.00..10.40 rows=13 width=60) (actual time=0.001..0.026 rows=13 loops=15,528)

  • Filter: (em_awobg_filtrar_buscar_venta = 'Y'::bpchar)
  • Rows Removed by Filter: 179
41. 1,211.184 1,211.184 ↓ 0.0 0 201,864

Index Scan using m_attributeinstance_attribu_un on m_attributeinstance attrinstance (cost=0.70..40.79 rows=9 width=107) (actual time=0.005..0.006 rows=0 loops=201,864)

  • Index Cond: (((m_attributesetinstance_id)::text = (aol.m_attributesetinstance_id)::text) AND ((m_attribute_id)::text = (attr.m_attribute_id)::text))
42. 156.816 156.816 ↑ 1.0 1 39,204

Index Scan using m_attributevalue_key on m_attributevalue attrvalue (cost=0.42..7.80 rows=1 width=43) (actual time=0.004..0.004 rows=1 loops=39,204)

  • Index Cond: ((attrinstance.m_attributevalue_id)::text = (m_attributevalue_id)::text)
Planning time : 15.480 ms
Execution time : 2,463.489 ms