explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.016 2,711.720 ↑ 1.0 10 1

Limit (cost=72,088.40..72,628.98 rows=10 width=338) (actual time=560.895..2,711.720 rows=10 loops=1)

2. 0.017 2,711.704 ↑ 900.6 10 1

Merge Left Join (cost=72,088.40..558,934.08 rows=9,006 width=338) (actual time=560.892..2,711.704 rows=10 loops=1)

  • Merge Cond: ((p.m_product_id)::text = (stock.m_product_id)::text)
  • Join Filter: ((stock.m_attributesetinstance_id)::text = (p.em_opxpatr_valor_atributos_id)::text)
3. 1,439.599 2,472.896 ↑ 900.6 10 1

Nested Loop (cost=5,068.37..491,891.34 rows=9,006 width=240) (actual time=322.096..2,472.896 rows=10 loops=1)

  • Join Filter: (opxesde_atrib_para_lpv(p.em_opxpatr_valor_atributos_id) = ff.atriblpv)
  • Rows Removed by Join Filter: 10,859
4. 0.006 24.365 ↑ 133.5 2 1

Nested Loop (cost=6.60..7,322.92 rows=267 width=240) (actual time=24.337..24.365 rows=2 loops=1)

5. 0.171 0.171 ↑ 463.0 2 1

Index Scan using em_dlc_veh_pieza_product_un on opxdes_vehiculo_pieza p (cost=0.56..3,764.57 rows=926 width=240) (actual time=0.170..0.171 rows=2 loops=1)

  • Index Cond: ((opxdes_vehiculo_id)::text = 'E3E19AD7BC1642CA8880B33E5DC28993'::text)
6. 0.013 24.188 ↑ 1.0 1 2

Materialize (cost=6.04..3,546.78 rows=1 width=31) (actual time=12.083..12.094 rows=1 loops=2)

7. 0.007 24.175 ↑ 1.0 1 1

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

  • Join Filter: ((prod.m_product_category_id)::text = (pc.m_product_category_id)::text)
8. 0.070 0.070 ↑ 1.0 1 1

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

  • Filter: (em_opxdes_categoria_vehiculo = 'Y'::bpchar)
  • Rows Removed by Filter: 58
9. 0.003 24.098 ↑ 17.0 1 1

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

10. 0.007 24.081 ↑ 17.0 1 1

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

11. 0.003 24.034 ↑ 1.0 1 1

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

12. 0.052 0.052 ↑ 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.052 rows=1 loops=1)

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

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

  • Filter: (((em_opxawo_vehiculo)::text = 'E3E19AD7BC1642CA8880B33E5DC28993'::text) AND (em_awobg_contenedor = 'N'::bpchar))
  • Rows Removed by Filter: 64,590
14. 0.011 0.040 ↑ 118.0 1 1

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

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

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

  • Index Cond: ((m_refinventory_id)::text = (box.m_refinventory_id)::text)
16. 0.014 0.014 ↑ 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.014..0.014 rows=1 loops=1)

  • Index Cond: ((m_product_id)::text = (sd.m_product_id)::text)
17. 2.900 1,008.932 ↑ 1.2 5,434 2

Materialize (cost=5,061.77..6,918.30 rows=6,751 width=32) (actual time=21.416..504.466 rows=5,434 loops=2)

18. 1.418 1,006.032 ↓ 1.1 7,505 1

Subquery Scan on ff (cost=5,061.77..6,884.54 rows=6,751 width=32) (actual time=42.829..1,006.032 rows=7,505 loops=1)

19. 966.761 1,004.614 ↓ 1.1 7,505 1

HashAggregate (cost=5,061.77..6,817.03 rows=6,751 width=97) (actual time=42.828..1,004.614 rows=7,505 loops=1)

  • Group Key: ol.c_orderline_id
20. 7.300 37.853 ↓ 2.2 15,010 1

Hash Join (cost=2,110.54..5,044.89 rows=6,751 width=33) (actual time=18.993..37.853 rows=15,010 loops=1)

  • Hash Cond: ((ol.c_order_id)::text = (o.c_order_id)::text)
21. 12.240 12.240 ↑ 1.0 22,752 1

Seq Scan on c_orderline ol (cost=0.00..2,781.52 rows=22,752 width=66) (actual time=0.008..12.240 rows=22,752 loops=1)

22. 1.746 18.313 ↓ 2.2 7,686 1

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

  • Buckets: 8,192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 552kB
23. 8.204 16.567 ↓ 2.2 7,686 1

Nested Loop (cost=0.00..2,065.93 rows=3,569 width=33) (actual time=0.027..16.567 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
24. 8.363 8.363 ↑ 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.008..8.363 rows=4,130 loops=1)

  • Filter: (((docstatus)::text = 'CO'::text) AND (issotrx = 'Y'::bpchar))
  • Rows Removed by Filter: 7,899
25. 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)

26. 0.008 0.008 ↑ 1.0 2 1

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

27. 0.001 238.791 ↓ 0.0 0 1

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

28. 0.001 238.790 ↓ 0.0 0 1

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

  • Group Key: stock.m_product_id, stock.m_attributesetinstance_id
29. 0.003 238.789 ↓ 0.0 0 1

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

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

Seq Scan on m_storage_detail stock (cost=0.00..67,020.02 rows=1 width=70) (actual time=238.786..238.786 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
Planning time : 10.470 ms
Execution time : 2,712.565 ms