explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UXDP : original

Settings
# exclusive inclusive rows x rows loops node
1. 89.110 18,460.430 ↑ 13,544,750.3 14,101 1

Merge Right Join (cost=11,171,868,110.17..21,917,394,195.99 rows=190,994,523,429 width=4,372) (actual time=18,326.558..18,460.430 rows=14,101 loops=1)

  • Merge Cond: (((aal.codigo)::bigint) = ((art.codigoarticulo)::bigint))
  • Execution time: 18,471.256 ms(166 filas)
2. 35.492 1,091.224 ↑ 1.0 45,815 1

Sort (cost=6,009.98..6,124.57 rows=45,836 width=16) (actual time=1,084.379..1,091.224 rows=45,815 loops=1)

  • Sort Key: ((aal.codigo)::bigint)
  • Sort Method: external merge Disk: 1,816kB
3. 31.874 1,055.732 ↑ 1.0 45,836 1

Hash Right Join (cost=2,203.56..2,461.31 rows=45,836 width=16) (actual time=1,017.632..1,055.732 rows=45,836 loops=1)

  • Hash Cond: (stockvendible.idarticuloalmacen = aal.idarticulo)
4. 957.864 957.864 ↓ 42.6 42,630 1

Function Scan on devuelvestockalmacenexistenciaspedidos stockvendible (cost=0.25..10.25 rows=1,000 width=16) (actual time=950.551..957.864 rows=42,630 loops=1)

5. 11.630 65.994 ↑ 1.0 45,836 1

Hash (cost=1,406.36..1,406.36 rows=45,836 width=16) (actual time=65.994..65.994 rows=45,836 loops=1)

  • Buckets: 65,536 Batches: 2 Memory Usage: 1,751kB
6. 54.364 54.364 ↑ 1.0 45,836 1

Seq Scan on articuloalmacen aal (cost=0.00..1,406.36 rows=45,836 width=16) (actual time=0.512..54.364 rows=45,836 loops=1)

7. 2.220 17,280.096 ↑ 59,100.9 14,101 1

Materialize (cost=11,171,862,100.20..11,176,029,011.00 rows=833,382,160 width=4,397) (actual time=17,242.082..17,280.096 rows=14,101 loops=1)

8. 125.980 17,277.876 ↑ 59,100.9 14,101 1

Sort (cost=11,171,862,100.20..11,173,945,555.60 rows=833,382,160 width=4,397) (actual time=17,242.079..17,277.876 rows=14,101 loops=1)

  • Sort Key: ((art.codigoarticulo)::bigint)
  • Sort Method: external sort Disk: 11,432kB
9. 51.544 17,151.896 ↑ 59,100.9 14,101 1

Merge Right Join (cost=359,444.13..21,934,621.88 rows=833,382,160 width=4,397) (actual time=16,948.287..17,151.896 rows=14,101 loops=1)

  • Merge Cond: (((art_1.codigoarticulo)::integer) = vd11.artcod)
10. 38.672 5,828.080 ↓ 42.5 42,450 1

Sort (cost=3,749.93..3,752.43 rows=1,000 width=34) (actual time=5,820.772..5,828.080 rows=42,450 loops=1)

  • Sort Key: ((art_1.codigoarticulo)::integer)
  • Sort Method: external merge Disk: 2,056kB
11. 13.495 5,789.408 ↓ 42.5 42,462 1

Merge Left Join (cost=3,648.33..3,700.10 rows=1,000 width=34) (actual time=5,769.609..5,789.408 rows=42,462 loops=1)

  • Merge Cond: ((iva.idindiceventaanual = ivm.indiceventaanual_id) AND (((date_part('month'::text, (('now'::cstring)::date + (((ceil((articleslt.leadtime / '4'::double precision)))::text || ' MONTH'::text))::interval)))::integer) = ((ivm.mes)::integer)))
12. 72.277 5,775.913 ↓ 42.5 42,462 1

Sort (cost=3,636.47..3,638.97 rows=1,000 width=34) (actual time=5,769.603..5,775.913 rows=42,462 loops=1)

  • Sort Key: iva.idindiceventaanual, ((date_part('month'::text, (('now'::cstring)::date + (((ceil((articleslt.leadtime / '4'::double precision)))::text || ' MONTH'::text))::interval)))::integer)
  • Sort Method: external merge Disk: 2,056kB
13. 172.507 5,703.636 ↓ 42.5 42,462 1

Hash Left Join (cost=3,547.04..3,586.64 rows=1,000 width=34) (actual time=5,519.383..5,703.636 rows=42,462 loops=1)

  • Hash Cond: (date_part('year'::text, (('now'::cstring)::date + (((ceil((articleslt.leadtime / '4'::double precision)))::text || ' MONTH'::text))::interval)) = (iva.anio)::double precision)
14. 29.598 5,525.488 ↓ 42.5 42,462 1

HashAggregate (cost=3,545.65..3,555.65 rows=1,000 width=26) (actual time=5,513.679..5,525.488 rows=42,462 loops=1)

  • Group Key: art_1.codigoarticulo, articleslt.leadtime, articleslt.reliabilitylevel, articleslt.securityweeks
15. 22.879 5,495.890 ↓ 42.5 42,462 1

Hash Join (cost=3,262.90..3,535.65 rows=1,000 width=26) (actual time=5,464.772..5,495.890 rows=42,462 loops=1)

  • Hash Cond: (articleslt.article_id = art_1.idarticulo)
16. 5,451.990 5,451.990 ↓ 42.5 42,462 1

Function Scan on internalorderprovidersparametersforarticles articleslt (cost=0.25..10.25 rows=1,000 width=26) (actual time=5,443.663..5,451.990 rows=42,462 loops=1)

17. 9.218 21.021 ↑ 1.0 48,118 1

Hash (cost=2,426.18..2,426.18 rows=48,118 width=16) (actual time=21.021..21.021 rows=48,118 loops=1)

  • Buckets: 65,536 Batches: 2 Memory Usage: 1,815kB
18. 11.803 11.803 ↑ 1.0 48,118 1

Seq Scan on articulo art_1 (cost=0.00..2,426.18 rows=48,118 width=16) (actual time=0.015..11.803 rows=48,118 loops=1)

19. 0.016 5.641 ↑ 1.0 17 1

Hash (cost=1.17..1.17 rows=17 width=10) (actual time=5.641..5.641 rows=17 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
20. 5.625 5.625 ↑ 1.0 17 1

Seq Scan on indiceventaanual iva (cost=0.00..1.17 rows=17 width=10) (actual time=5.622..5.625 rows=17 loops=1)

21. 0.000 0.000 ↓ 0.0 0

Sort (cost=11.87..12.38 rows=204 width=18) (never executed)

  • Sort Key: ivm.indiceventaanual_id, ((ivm.mes)::integer)
22. 0.000 0.000 ↓ 0.0 0

Seq Scan on indiceventamensual ivm (cost=0.00..4.04 rows=204 width=18) (never executed)

23. 37.305 11,272.272 ↑ 11,820.2 14,101 1

Materialize (cost=355,694.20..5,679,909.83 rows=166,676,432 width=4,371) (actual time=11,127.480..11,272.272 rows=14,101 loops=1)

24. 8.820 11,234.967 ↑ 11,820.2 14,101 1

Merge Left Join (cost=355,694.20..5,263,218.75 rows=166,676,432 width=4,371) (actual time=11,127.467..11,234.967 rows=14,101 loops=1)

  • Merge Cond: ((art.codigoarticulo)::integer = ((ventasultimasemana.codigoarticulo)::integer))
25. 10.899 11,193.471 ↑ 695.3 14,101 1

Nested Loop Left Join (cost=62,339.97..680,397.53 rows=9,804,496 width=4,331) (actual time=11,094.784..11,193.471 rows=14,101 loops=1)

  • Join Filter: (art.idarticulo = ventasultimasemanapr.articulo_id)
26. 8.355 11,182.572 ↑ 695.3 14,101 1

Merge Left Join (cost=62,331.50..533,321.56 rows=9,804,496 width=4,323) (actual time=11,094.702..11,182.572 rows=14,101 loops=1)

  • Merge Cond: (vd11.artcod = ((pvaux.codigoarticulo)::integer))
27. 41.416 657.572 ↑ 695.3 14,101 1

Merge Join (cost=62,298.03..263,664.46 rows=9,804,496 width=4,315) (actual time=578.054..657.572 rows=14,101 loops=1)

  • Merge Cond: ((art.codigoarticulo)::integer = vd11.artcod)
28. 13.017 441.183 ↑ 2.9 48,083 1

Merge Left Join (cost=7,666.17..11,864.47 rows=139,061 width=31) (actual time=417.495..441.183 rows=48,083 loops=1)

  • Merge Cond: (((art.codigoarticulo)::integer) = ((artcat.codigo)::integer))
29. 44.341 90.800 ↑ 1.0 48,083 1

Sort (cost=6,831.10..6,951.39 rows=48,118 width=23) (actual time=82.343..90.800 rows=48,083 loops=1)

  • Sort Key: ((art.codigoarticulo)::integer)
  • Sort Method: external merge Disk: 1,976kB
30. 31.107 46.459 ↑ 1.0 48,118 1

Hash Join (cost=1.09..3,088.89 rows=48,118 width=23) (actual time=5.153..46.459 rows=48,118 loops=1)

  • Hash Cond: (art.tipoarticulologistica_id = tipartlog.idtipoarticulologistica)
31. 10.240 10.240 ↑ 1.0 48,118 1

Seq Scan on articulo art (cost=0.00..2,426.18 rows=48,118 width=27) (actual time=0.018..10.240 rows=48,118 loops=1)

32. 0.007 5.112 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=12) (actual time=5.112..5.112 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
33. 5.105 5.105 ↑ 1.0 4 1

Seq Scan on tipoarticulologistica tipartlog (cost=0.00..1.04 rows=4 width=12) (actual time=5.103..5.105 rows=4 loops=1)

34. 8.616 337.366 ↓ 18.9 10,915 1

Sort (cost=835.07..836.52 rows=578 width=16) (actual time=335.147..337.366 rows=10,915 loops=1)

  • Sort Key: ((artcat.codigo)::integer)
  • Sort Method: quicksort Memory: 1,237kB
35. 6.990 328.750 ↓ 18.9 10,915 1

Nested Loop (cost=0.71..808.56 rows=578 width=16) (actual time=29.517..328.750 rows=10,915 loops=1)

36. 1.849 59.800 ↓ 18.9 10,915 1

Nested Loop (cost=0.42..619.44 rows=578 width=16) (actual time=23.844..59.800 rows=10,915 loops=1)

37. 0.041 11.113 ↑ 1.0 1 1

Nested Loop (cost=0.00..2.95 rows=1 width=8) (actual time=11.098..11.113 rows=1 loops=1)

  • Join Filter: (sur.annocomercial_id = anncom.idannocomercial)
  • Rows Removed by Join Filter: 14
38. 7.937 7.937 ↓ 15.0 15 1

Seq Scan on surtido sur (cost=0.00..1.58 rows=1 width=16) (actual time=7.885..7.937 rows=15 loops=1)

  • Filter: ((tiposurtido)::text ~~* 'IXP'::text)
  • Rows Removed by Filter: 31
39. 3.135 3.135 ↑ 2.0 1 15

Seq Scan on annocomercial anncom (cost=0.00..1.35 rows=2 width=8) (actual time=0.208..0.209 rows=1 loops=15)

  • Filter: ((('now'::cstring)::date >= fechainicio) AND (('now'::cstring)::date <= fechafin))
  • Rows Removed by Filter: 13
40. 46.838 46.838 ↓ 2.2 10,915 1

Index Scan using indicefk_00000078 on articuloventa artven (cost=0.42..566.15 rows=5,034 width=16) (actual time=12.741..46.838 rows=10,915 loops=1)

  • Index Cond: (surtido_id = sur.idsurtido)
41. 261.960 261.960 ↑ 1.0 1 10,915

Index Scan using articulocatalogo_pkey on articulocatalogo artcat (cost=0.29..0.32 rows=1 width=16) (actual time=0.024..0.024 rows=1 loops=10,915)

  • Index Cond: (idarticulocatalogo = artven.articulocatalogo_id)
42. 2.165 174.973 ↑ 1.0 14,101 1

Materialize (cost=54,631.86..54,702.37 rows=14,101 width=4,284) (actual time=160.538..174.973 rows=14,101 loops=1)

43. 74.454 172.808 ↑ 1.0 14,101 1

Sort (cost=54,631.86..54,667.12 rows=14,101 width=4,284) (actual time=160.533..172.808 rows=14,101 loops=1)

  • Sort Key: vd11.artcod
  • Sort Method: external merge Disk: 10,552kB
44. 27.460 98.354 ↑ 1.0 14,101 1

Nested Loop (cost=0.00..1,699.06 rows=14,101 width=4,284) (actual time=9.399..98.354 rows=14,101 loops=1)

45. 2.332 2.332 ↑ 1.0 1 1

Seq Scan on divisalogistica div (cost=0.00..1.04 rows=1 width=8) (actual time=2.331..2.332 rows=1 loops=1)

  • Filter: ((codigodivisalogistica)::text ~~* 'USD'::text)
  • Rows Removed by Filter: 2
46. 68.562 68.562 ↑ 1.0 14,101 1

Seq Scan on vd11 (cost=0.00..1,557.01 rows=14,101 width=4,276) (actual time=7.058..68.562 rows=14,101 loops=1)

47. 0.012 10,516.645 ↓ 0.0 0 1

Sort (cost=33.46..33.96 rows=200 width=40) (actual time=10,516.645..10,516.645 rows=0 loops=1)

  • Sort Key: ((pvaux.codigoarticulo)::integer)
  • Sort Method: quicksort Memory: 25kB
48. 0.001 10,516.633 ↓ 0.0 0 1

Subquery Scan on pvaux (cost=21.82..25.82 rows=200 width=40) (actual time=10,516.633..10,516.633 rows=0 loops=1)

49. 0.000 10,516.632 ↓ 0.0 0 1

HashAggregate (cost=21.82..23.82 rows=200 width=40) (actual time=10,516.632..10,516.632 rows=0 loops=1)

  • Group Key: pvintercambios.codigoarticulo
50.          

Initplan (for HashAggregate)

51. 0.015 0.015 ↑ 9.0 1 1

Seq Scan on tienda t (cost=0.00..2.18 rows=9 width=516) (actual time=0.013..0.015 rows=1 loops=1)

  • Filter: actual
  • Rows Removed by Filter: 8
52. 0.007 0.007 ↑ 9.0 1 1

Seq Scan on tienda t_1 (cost=0.00..2.18 rows=9 width=516) (actual time=0.006..0.007 rows=1 loops=1)

  • Filter: actual
  • Rows Removed by Filter: 8
53. 0.007 0.007 ↑ 9.0 1 1

Seq Scan on tienda t_2 (cost=0.00..2.18 rows=9 width=516) (actual time=0.006..0.007 rows=1 loops=1)

  • Filter: actual
  • Rows Removed by Filter: 8
54. 10,516.629 10,516.629 ↓ 0.0 0 1

Function Scan on pendienteporvenir pvintercambios (cost=0.28..10.28 rows=1,000 width=40) (actual time=10,516.629..10,516.629 rows=0 loops=1)

55. 0.000 0.000 ↓ 0.0 0 14,101

Materialize (cost=8.47..8.53 rows=1 width=24) (actual time=0.000..0.000 rows=0 loops=14,101)

56. 0.000 0.055 ↓ 0.0 0 1

Subquery Scan on ventasultimasemanapr (cost=8.47..8.52 rows=1 width=24) (actual time=0.055..0.055 rows=0 loops=1)

57. 0.002 0.055 ↓ 0.0 0 1

GroupAggregate (cost=8.47..8.51 rows=1 width=24) (actual time=0.055..0.055 rows=0 loops=1)

  • Group Key: venartdia.articulo_id
58. 0.012 0.053 ↓ 0.0 0 1

Sort (cost=8.47..8.48 rows=1 width=72) (actual time=0.053..0.053 rows=0 loops=1)

  • Sort Key: venartdia.articulo_id
  • Sort Method: quicksort Memory: 25kB
59. 0.041 0.041 ↓ 0.0 0 1

Index Scan using ventaarticulodia_fechaventa_date on ventaarticulodia venartdia (cost=0.45..8.46 rows=1 width=72) (actual time=0.041..0.041 rows=0 loops=1)

  • Index Cond: (((fechaventa)::date >= (('now'::cstring)::date - 7)) AND ((fechaventa)::date <= (('now'::cstring)::date - 1)))
60. 0.004 32.676 ↓ 0.0 0 1

Sort (cost=293,354.23..293,362.73 rows=3,400 width=556) (actual time=32.676..32.676 rows=0 loops=1)

  • Sort Key: ((ventasultimasemana.codigoarticulo)::integer)
  • Sort Method: quicksort Memory: 25kB
61. 0.000 32.672 ↓ 0.0 0 1

Subquery Scan on ventasultimasemana (cost=292,391.64..293,154.79 rows=3,400 width=556) (actual time=32.672..32.672 rows=0 loops=1)

62. 0.002 32.672 ↓ 0.0 0 1

GroupAggregate (cost=292,391.64..293,120.79 rows=3,400 width=556) (actual time=32.672..32.672 rows=0 loops=1)

  • Group Key: (COALESCE(elefac.codigoelemento, abo.codigoarticulo))
63. 0.003 32.670 ↓ 0.0 0 1

Sort (cost=292,391.64..292,506.08 rows=45,777 width=596) (actual time=32.670..32.670 rows=0 loops=1)

  • Sort Key: (COALESCE(elefac.codigoelemento, abo.codigoarticulo))
  • Sort Method: quicksort Memory: 25kB
64. 0.014 32.667 ↓ 0.0 0 1

Hash Full Join (cost=261,252.89..264,438.96 rows=45,777 width=596) (actual time=32.667..32.667 rows=0 loops=1)

  • Hash Cond: ((elefac.codigoelemento)::text = (abo.codigoarticulo)::text)
65. 0.002 15.344 ↓ 0.0 0 1

GroupAggregate (cost=257,906.78..260,424.51 rows=45,777 width=52) (actual time=15.344..15.344 rows=0 loops=1)

  • Group Key: ((fac_1.fechacreacion)::date), elefac.codigoelemento
66. 0.009 15.342 ↓ 0.0 0 1

Sort (cost=257,906.78..258,021.22 rows=45,777 width=36) (actual time=15.342..15.342 rows=0 loops=1)

  • Sort Key: ((fac_1.fechacreacion)::date), elefac.codigoelemento
  • Sort Method: quicksort Memory: 25kB
67. 0.001 15.333 ↓ 0.0 0 1

Nested Loop (cost=402.19..253,110.10 rows=45,777 width=36) (actual time=15.333..15.333 rows=0 loops=1)

68. 0.000 15.332 ↓ 0.0 0 1

Nested Loop (cost=401.75..225,009.90 rows=46,695 width=24) (actual time=15.332..15.332 rows=0 loops=1)

69. 0.003 15.332 ↓ 0.0 0 1

Bitmap Heap Scan on factura fac_1 (cost=401.32..51,133.40 rows=18,817 width=16) (actual time=15.332..15.332 rows=0 loops=1)

  • Recheck Cond: (((fechacreacion)::date >= (('now'::cstring)::date - 7)) AND ((fechacreacion)::date <= (('now'::cstring)::date - 1)))
70. 15.329 15.329 ↓ 0.0 0 1

Bitmap Index Scan on factura_fechacreacion_date (cost=0.00..396.62 rows=18,817 width=0) (actual time=15.329..15.329 rows=0 loops=1)

  • Index Cond: (((fechacreacion)::date >= (('now'::cstring)::date - 7)) AND ((fechacreacion)::date <= (('now'::cstring)::date - 1)))
71. 0.000 0.000 ↓ 0.0 0

Index Scan using indicefk_00001289 on lineafactura linfac (cost=0.43..8.92 rows=32 width=24) (never executed)

  • Index Cond: (factura_id = fac_1.iddocumento)
  • Filter: ((reserva IS NULL) OR ((reserva)::text !~~* 'RA'::text))
72. 0.000 0.000 ↓ 0.0 0

Index Scan using elementofactura_pkey on elementofactura elefac (cost=0.43..0.59 rows=1 width=32) (never executed)

  • Index Cond: (idelemento = linfac.elementofactura_id)
  • Filter: ((tipoelemento)::text ~~* 'Articulo'::text)
73. 0.000 17.309 ↓ 0.0 0 1

Hash (cost=3,345.90..3,345.90 rows=17 width=48) (actual time=17.309..17.309 rows=0 loops=1)

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

Subquery Scan on abo (cost=3,344.79..3,345.90 rows=17 width=48) (actual time=17.309..17.309 rows=0 loops=1)

75. 0.001 17.308 ↓ 0.0 0 1

GroupAggregate (cost=3,344.79..3,345.73 rows=17 width=52) (actual time=17.308..17.308 rows=0 loops=1)

  • Group Key: ((abo_1.fechacreacion)::date), eleabo.codigoelemento
76. 0.043 17.307 ↓ 0.0 0 1

Sort (cost=3,344.79..3,344.83 rows=17 width=36) (actual time=17.307..17.307 rows=0 loops=1)

  • Sort Key: ((abo_1.fechacreacion)::date), eleabo.codigoelemento
  • Sort Method: quicksort Memory: 25kB
77. 0.001 17.264 ↓ 0.0 0 1

Nested Loop (cost=28.94..3,344.44 rows=17 width=36) (actual time=17.264..17.264 rows=0 loops=1)

78. 0.000 17.263 ↓ 0.0 0 1

Nested Loop (cost=28.52..3,335.12 rows=17 width=24) (actual time=17.263..17.263 rows=0 loops=1)

79. 0.003 17.263 ↓ 0.0 0 1

Bitmap Heap Scan on abono abo_1 (cost=28.10..3,233.33 rows=12 width=16) (actual time=17.263..17.263 rows=0 loops=1)

  • Recheck Cond: (((fechacreacion)::date >= (('now'::cstring)::date - 7)) AND ((fechacreacion)::date <= (('now'::cstring)::date - 1)))
  • Filter: (((estado)::text ~~* 'Cerrado'::text) AND (upper((tipo)::text) = ANY ('{ABONODEVOLUCION,ABONOANULACION}'::text[])))
80. 17.260 17.260 ↓ 0.0 0 1

Bitmap Index Scan on abono_fechacreacion_date (cost=0.00..28.10 rows=1,166 width=0) (actual time=17.260..17.260 rows=0 loops=1)

  • Index Cond: (((fechacreacion)::date >= (('now'::cstring)::date - 7)) AND ((fechacreacion)::date <= (('now'::cstring)::date - 1)))
81. 0.000 0.000 ↓ 0.0 0

Index Scan using indicefk_00001214 on lineaabono linabo (cost=0.42..8.46 rows=2 width=32) (never executed)

  • Index Cond: (abono_id = abo_1.iddocumento)
82. 0.000 0.000 ↓ 0.0 0

Index Scan using elementoabono_pkey on elementoabono eleabo (cost=0.42..0.54 rows=1 width=32) (never executed)

  • Index Cond: (idelemento = linabo.elementoabono_id)
  • Filter: ((tipoelemento)::text ~~* 'Articulo'::text)
Planning time : 42.373 ms