explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sJ4P

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 2,099,301.004 2,100,705.866 ↑ 1.5 6,101 1

Nested Loop Left Join (cost=72,140.50..1,007,771.38 rows=9,013 width=370) (actual time=2,037.270..2,100,705.866 rows=6,101 loops=1)

  • Join Filter: (opxesde_atrib_para_lpv(p.em_opxpatr_valor_atributos_id) = awobg_atrib_lpv(ol.c_orderline_id))
  • Rows Removed by Join Filter: 6,536,259
2. 2.615 271.982 ↓ 3.6 952 1

Hash Left Join (cost=67,061.86..74,227.71 rows=267 width=272) (actual time=266.594..271.982 rows=952 loops=1)

  • Hash Cond: (((p.em_opxpatr_valor_atributos_id)::text = (stock.m_attributesetinstance_id)::text) AND ((p.m_product_id)::text = (stock.m_product_id)::text))
3. 1.439 19.487 ↓ 3.6 952 1

Nested Loop (cost=41.78..7,206.22 rows=267 width=240) (actual time=16.701..19.487 rows=952 loops=1)

4. 0.002 16.625 ↑ 1.0 1 1

Nested Loop (cost=6.04..3,546.94 rows=1 width=31) (actual time=16.599..16.625 rows=1 loops=1)

  • Join Filter: ((prod.m_product_category_id)::text = (pc.m_product_category_id)::text)
5. 0.027 0.027 ↑ 1.0 1 1

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

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

Nested Loop (cost=6.04..3,542.99 rows=17 width=64) (actual time=16.581..16.596 rows=1 loops=1)

7. 0.011 16.583 ↑ 17.0 1 1

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

8. 0.005 16.537 ↑ 1.0 1 1

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

9. 0.031 0.031 ↑ 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.030..0.031 rows=1 loops=1)

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

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

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

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

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

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

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

Index Scan using m_product_key on m_product prod (cost=0.28..2.79 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)
14. 1.336 1.423 ↓ 1.0 952 1

Bitmap Heap Scan on opxdes_vehiculo_pieza p (cost=35.74..3,650.02 rows=926 width=240) (actual time=0.098..1.423 rows=952 loops=1)

  • Recheck Cond: ((opxdes_vehiculo_id)::text = 'E3E19AD7BC1642CA8880B33E5DC28993'::text)
  • Heap Blocks: exact=54
15. 0.087 0.087 ↓ 1.0 952 1

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

  • Index Cond: ((opxdes_vehiculo_id)::text = 'E3E19AD7BC1642CA8880B33E5DC28993'::text)
16. 0.000 249.880 ↓ 0.0 0 1

Hash (cost=67,020.07..67,020.07 rows=1 width=98) (actual time=249.880..249.880 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
17. 0.001 249.880 ↓ 0.0 0 1

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

  • Group Key: stock.m_product_id, stock.m_attributesetinstance_id
18. 0.007 249.879 ↓ 0.0 0 1

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

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

Seq Scan on m_storage_detail stock (cost=0.00..67,020.02 rows=1 width=70) (actual time=249.872..249.872 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
20. 1,098.520 1,132.880 ↓ 1.0 6,872 952

Materialize (cost=5,078.65..5,264.30 rows=6,751 width=65) (actual time=0.031..1.190 rows=6,872 loops=952)

21. 9.175 34.360 ↓ 1.0 6,872 1

HashAggregate (cost=5,078.65..5,163.03 rows=6,751 width=65) (actual time=28.990..34.360 rows=6,872 loops=1)

  • Group Key: ol.c_orderline_id
22. 4.644 25.185 ↓ 2.0 13,744 1

Hash Join (cost=2,110.54..5,044.89 rows=6,751 width=37) (actual time=12.285..25.185 rows=13,744 loops=1)

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

Seq Scan on c_orderline ol (cost=0.00..2,781.52 rows=22,752 width=70) (actual time=0.006..8.807 rows=22,752 loops=1)

24. 1.034 11.734 ↓ 2.0 7,022 1

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

  • Buckets: 8,192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 510kB
25. 5.280 10.700 ↓ 2.0 7,022 1

Nested Loop (cost=0.00..2,065.93 rows=3,569 width=33) (actual time=0.022..10.700 rows=7,022 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: 1,238
26. 5.420 5.420 ↑ 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..5.420 rows=4,130 loops=1)

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

28. 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)

Planning time : 4.618 ms
Execution time : 2,100,707.827 ms