explain.depesz.com

PostgreSQL's explain analyze made readable

Result: usTx : original 2

Settings
# exclusive inclusive rows x rows loops node
1. 89.214 14,581.700 ↑ 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=14,446.919..14,581.700 rows=14,101 loops=1)

  • Merge Cond: (((aal.codigo)::bigint) = ((art.codigoarticulo)::bigint))
  • Execution time: 14,593.231 ms(166 filas)
2. 35.624 642.869 ↑ 1.0 45,815 1

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

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

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

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

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

5. 9.170 18.600 ↑ 1.0 45,836 1

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

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

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

7. 2.262 13,849.617 ↑ 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=13,811.049..13,849.617 rows=14,101 loops=1)

8. 127.500 13,847.355 ↑ 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=13,811.045..13,847.355 rows=14,101 loops=1)

  • Sort Key: ((art.codigoarticulo)::bigint)
  • Sort Method: external sort Disk: 11,432kB
9. 51.587 13,719.855 ↑ 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=13,514.855..13,719.855 rows=14,101 loops=1)

  • Merge Cond: (((art_1.codigoarticulo)::integer) = vd11.artcod)
10. 36.157 3,104.576 ↓ 42.5 42,450 1

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

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

Merge Left Join (cost=3,648.33..3,700.10 rows=1,000 width=34) (actual time=3,049.103..3,068.419 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. 67.657 3,055.468 ↓ 42.5 42,462 1

Sort (cost=3,636.47..3,638.97 rows=1,000 width=34) (actual time=3,049.097..3,055.468 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.349 2,987.811 ↓ 42.5 42,462 1

Hash Left Join (cost=3,547.04..3,586.64 rows=1,000 width=34) (actual time=2,803.815..2,987.811 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.707 2,815.434 ↓ 42.5 42,462 1

HashAggregate (cost=3,545.65..3,555.65 rows=1,000 width=26) (actual time=2,803.724..2,815.434 rows=42,462 loops=1)

  • Group Key: art_1.codigoarticulo, articleslt.leadtime, articleslt.reliabilitylevel, articleslt.securityweeks
15. 22.268 2,785.727 ↓ 42.5 42,462 1

Hash Join (cost=3,262.90..3,535.65 rows=1,000 width=26) (actual time=2,754.949..2,785.727 rows=42,462 loops=1)

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

Function Scan on internalorderprovidersparametersforarticles articleslt (cost=0.25..10.25 rows=1,000 width=26) (actual time=2,733.591..2,742.184 rows=42,462 loops=1)

17. 9.396 21.275 ↑ 1.0 48,118 1

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

  • Buckets: 65,536 Batches: 2 Memory Usage: 1,815kB
18. 11.879 11.879 ↑ 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.879 rows=48,118 loops=1)

19. 0.009 0.028 ↑ 1.0 17 1

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

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

Seq Scan on indiceventaanual iva (cost=0.00..1.17 rows=17 width=10) (actual time=0.016..0.019 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.192 10,563.692 ↑ 11,820.2 14,101 1

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

24. 8.940 10,526.500 ↑ 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=10,417.736..10,526.500 rows=14,101 loops=1)

  • Merge Cond: ((art.codigoarticulo)::integer = ((ventasultimasemana.codigoarticulo)::integer))
25. 10.928 10,517.502 ↑ 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=10,417.671..10,517.502 rows=14,101 loops=1)

  • Join Filter: (art.idarticulo = ventasultimasemanapr.articulo_id)
26. 8.482 10,506.574 ↑ 695.3 14,101 1

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

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

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

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

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

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

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

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

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

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

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

32. 0.003 0.013 ↑ 1.0 4 1

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

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

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

34. 6.348 40.238 ↓ 18.9 10,915 1

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

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

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

36. 1.636 5.513 ↓ 18.9 10,915 1

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

37. 0.042 0.340 ↑ 1.0 1 1

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

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

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

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

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

  • Filter: ((('now'::cstring)::date >= fechainicio) AND (('now'::cstring)::date <= fechafin))
  • Rows Removed by Filter: 13
40. 3.537 3.537 ↓ 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=0.016..3.537 rows=10,915 loops=1)

  • Index Cond: (surtido_id = sur.idsurtido)
41. 21.830 21.830 ↑ 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.002..0.002 rows=1 loops=10,915)

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

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

43. 67.672 99.111 ↑ 1.0 14,101 1

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

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

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

45. 0.019 0.019 ↑ 1.0 1 1

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

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

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

47. 0.010 10,218.423 ↓ 0.0 0 1

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

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

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

49. 0.000 10,218.413 ↓ 0.0 0 1

HashAggregate (cost=21.82..23.82 rows=200 width=40) (actual time=10,218.412..10,218.413 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.005 0.005 ↑ 9.0 1 1

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

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

Function Scan on pendienteporvenir pvintercambios (cost=0.28..10.28 rows=1,000 width=40) (actual time=10,218.410..10,218.410 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.001 0.037 ↓ 0.0 0 1

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

57. 0.001 0.036 ↓ 0.0 0 1

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

  • Group Key: venartdia.articulo_id
58. 0.008 0.035 ↓ 0.0 0 1

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

  • Sort Key: venartdia.articulo_id
  • Sort Method: quicksort Memory: 25kB
59. 0.027 0.027 ↓ 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.027..0.027 rows=0 loops=1)

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

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

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

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

62. 0.001 0.055 ↓ 0.0 0 1

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

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

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

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

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

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

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

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

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

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

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

68. 0.000 0.011 ↓ 0.0 0 1

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

69. 0.000 0.011 ↓ 0.0 0 1

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

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

Bitmap Index Scan on factura_fechacreacion_date (cost=0.00..396.62 rows=18,817 width=0) (actual time=0.011..0.011 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 0.023 ↓ 0.0 0 1

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

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

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

75. 0.001 0.023 ↓ 0.0 0 1

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

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

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

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

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

78. 0.000 0.015 ↓ 0.0 0 1

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

79. 0.002 0.015 ↓ 0.0 0 1

Bitmap Heap Scan on abono abo_1 (cost=28.10..3,233.33 rows=12 width=16) (actual time=0.015..0.015 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. 0.013 0.013 ↓ 0.0 0 1

Bitmap Index Scan on abono_fechacreacion_date (cost=0.00..28.10 rows=1,166 width=0) (actual time=0.013..0.013 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 : 7.872 ms