explain.depesz.com

PostgreSQL's explain analyze made readable

Result: H1kt

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 3.336 3,668.012 ↑ 11.9 2,881 1

Unique (cost=678,937.70..681,339.05 rows=34,305 width=140) (actual time=3,664.331..3,668.012 rows=2,881 loops=1)

2. 7.075 3,664.676 ↑ 11.3 3,042 1

Sort (cost=678,937.70..679,023.46 rows=34,305 width=140) (actual time=3,664.329..3,664.676 rows=3,042 loops=1)

  • Sort Key: (date_part('year'::text, (('now'::cstring)::date)::timestamp without time zone)), saldoinv.pro_id, (((((((((((((((((((((((((saldoinv.sal_sinicial + saldoinv.sal_deb1) + saldoinv.sal_deb2) + saldoinv.sal_deb3) + saldoinv.sal_deb4) + saldoinv.sal_deb5) + saldoinv.sal_deb6) + saldoinv.sal_deb7) + saldoinv.sal_deb8) + saldoinv.sal_deb9) + saldoinv.sal_deb10) + saldoinv.sal_deb11) + saldoinv.sal_deb12) - saldoinv.sal_cre1) - saldoinv.sal_cre2) - saldoinv.sal_cre3) - saldoinv.sal_cre4) - saldoinv.sal_cre5) - saldoinv.sal_cre6) - saldoinv.sal_cre7) - saldoinv.sal_cre8) - saldoinv.sal_cre9) - saldoinv.sal_cre10) - saldoinv.sal_cre11) - saldoinv.sal_cre12)), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
  • Sort Method: quicksort Memory: 831kB
3. 0.570 3,657.601 ↑ 11.3 3,042 1

Append (cost=14,227.01..674,910.28 rows=34,305 width=140) (actual time=16.869..3,657.601 rows=3,042 loops=1)

4. 40.327 111.207 ↓ 1.3 2,903 1

Merge Left Join (cost=14,227.01..35,302.71 rows=2,232 width=140) (actual time=16.868..111.207 rows=2,903 loops=1)

  • Merge Cond: (pro.pro_id = saldoinv.pro_id)
  • Filter: ((((((((((((((((((((((((((saldoinv.sal_sinicial + saldoinv.sal_deb1) + saldoinv.sal_deb2) + saldoinv.sal_deb3) + saldoinv.sal_deb4) + saldoinv.sal_deb5) + saldoinv.sal_deb6) + saldoinv.sal_deb7) + saldoinv.sal_deb8) + saldoinv.sal_deb9) + saldoinv.sal_deb10) + saldoinv.sal_deb11) + saldoinv.sal_deb12) - saldoinv.sal_cre1) - saldoinv.sal_cre2) - saldoinv.sal_cre3) - saldoinv.sal_cre4) - saldoinv.sal_cre5) - saldoinv.sal_cre6) - saldoinv.sal_cre7) - saldoinv.sal_cre8) - saldoinv.sal_cre9) - saldoinv.sal_cre10) - saldoinv.sal_cre11) - saldoinv.sal_cre12) > '0'::numeric) OR (hashed SubPlan 4) OR (pro.cla1_id = 509) OR (buf.buf_valor > '0'::numeric) OR (pro.pro_nuevo = 1))
  • Rows Removed by Filter: 310
5. 2.698 50.360 ↓ 1.3 3,213 1

Merge Left Join (cost=0.85..20,911.50 rows=2,457 width=16) (actual time=0.060..50.360 rows=3,213 loops=1)

  • Merge Cond: (pro.pro_id = buf.pro_id)
6. 2.735 44.849 ↓ 1.9 3,213 1

Nested Loop (cost=0.56..20,092.73 rows=1,735 width=12) (actual time=0.054..44.849 rows=3,213 loops=1)

7. 4.194 4.194 ↓ 1.0 3,792 1

Index Scan using idx_producto1_producto on producto1 pro1 (cost=0.28..189.54 rows=3,677 width=4) (actual time=0.013..4.194 rows=3,792 loops=1)

  • Filter: prod1_muestraapp
  • Rows Removed by Filter: 824
8. 16.521 37.920 ↑ 1.0 1 3,792

Index Scan using producto_pkey on producto pro (cost=0.28..5.40 rows=1 width=12) (actual time=0.010..0.010 rows=1 loops=3,792)

  • Index Cond: (pro_id = pro1.pro_id)
  • Filter: (pro_activo AND (cla1_id <> 509) AND (((SubPlan 5) > '0'::numeric) OR ((SubPlan 6) > '0'::numeric)))
  • Rows Removed by Filter: 0
9.          

SubPlan (forIndex Scan)

10. 11.211 11.211 ↑ 1.0 1 3,737

Index Scan using idx_dlistapre_aux_pro_id_lpr_id on dlistapre_aux dp_2 (cost=0.29..2.51 rows=1 width=3) (actual time=0.003..0.003 rows=1 loops=3,737)

  • Index Cond: ((lpr_id = 1) AND (pro_id = pro.pro_id))
11. 10.188 10.188 ↑ 1.0 1 3,396

Index Scan using idx_dlistapre_pro_id_lpr_id on dlistapre dp_3 (cost=0.29..2.51 rows=1 width=6) (actual time=0.002..0.003 rows=1 loops=3,396)

  • Index Cond: ((lpr_id = 1) AND (pro_id = pro.pro_id))
12. 2.813 2.813 ↑ 1.6 3,991 1

Index Scan using idx_buffer_pro_id on buffer buf (cost=0.28..798.31 rows=6,414 width=8) (actual time=0.004..2.813 rows=3,991 loops=1)

13. 4.862 17.974 ↓ 72.0 3,888 1

Sort (cost=14,095.12..14,095.26 rows=54 width=79) (actual time=16.731..17.974 rows=3,888 loops=1)

  • Sort Key: saldoinv.pro_id
  • Sort Method: quicksort Memory: 643kB
14. 11.840 13.112 ↓ 72.0 3,888 1

Bitmap Heap Scan on saldoinv (cost=3,738.36..14,093.57 rows=54 width=79) (actual time=2.273..13.112 rows=3,888 loops=1)

  • Recheck Cond: (bod_id = 1)
  • Filter: ((sal_periodo)::double precision = date_part('year'::text, (('now'::cstring)::date)::timestamp without time zone))
  • Rows Removed by Filter: 7121
  • Heap Blocks: exact=825
15. 1.272 1.272 ↓ 1.0 11,009 1

Bitmap Index Scan on idx_saldoinv_bod (cost=0.00..3,738.34 rows=10,871 width=0) (actual time=1.272..1.272 rows=11,009 loops=1)

  • Index Cond: (bod_id = 1)
16.          

SubPlan (forMerge Left Join)

17. 0.830 2.546 ↓ 2.6 6,280 1

Nested Loop (cost=0.28..124.89 rows=2,461 width=4) (actual time=0.042..2.546 rows=6,280 loops=1)

18. 0.240 0.240 ↓ 2.4 82 1

Seq Scan on cordcom cor_1 (cost=0.00..5.86 rows=34 width=4) (actual time=0.022..0.240 rows=82 loops=1)

  • Filter: ((cor_estado <> 9) AND (date_part('day'::text, (cor_fecha_disponibilidad - (('now'::cstring)::date)::timestamp without time zone)) <= '21'::double precision))
  • Rows Removed by Filter: 25
19. 1.476 1.476 ↓ 1.1 77 82

Index Only Scan using idx_dordcom_corpro on dordcom dor_1 (cost=0.28..2.80 rows=70 width=8) (actual time=0.004..0.018 rows=77 loops=82)

  • Index Cond: (cor_id = cor_1.cor_id)
  • Heap Fetches: 1200
20. 2.163 3,545.824 ↑ 230.7 139 1

Hash Join (cost=1,507.88..639,264.52 rows=32,073 width=140) (actual time=99.209..3,545.824 rows=139 loops=1)

  • Hash Cond: (pro_1.pro_id = pro1_1.pro_id)
21. 19.611 3,533.243 ↑ 28.9 152 1

Hash Left Join (cost=1,321.11..635,700.36 rows=4,387 width=458) (actual time=58.622..3,533.243 rows=152 loops=1)

  • Hash Cond: (pro_1.pro_id = buf_1.pro_id)
  • Filter: ((((((((((((((((((((((((((saldoinv_1.sal_sinicial + saldoinv_1.sal_deb1) + saldoinv_1.sal_deb2) + saldoinv_1.sal_deb3) + saldoinv_1.sal_deb4) + saldoinv_1.sal_deb5) + saldoinv_1.sal_deb6) + saldoinv_1.sal_deb7) + saldoinv_1.sal_deb8) + saldoinv_1.sal_deb9) + saldoinv_1.sal_deb10) + saldoinv_1.sal_deb11) + saldoinv_1.sal_deb12) - saldoinv_1.sal_cre1) - saldoinv_1.sal_cre2) - saldoinv_1.sal_cre3) - saldoinv_1.sal_cre4) - saldoinv_1.sal_cre5) - saldoinv_1.sal_cre6) - saldoinv_1.sal_cre7) - saldoinv_1.sal_cre8) - saldoinv_1.sal_cre9) - saldoinv_1.sal_cre10) - saldoinv_1.sal_cre11) - saldoinv_1.sal_cre12) > '0'::numeric) OR (hashed SubPlan 1) OR (pro_1.cla1_id = 509) OR (buf_1.buf_valor > '0'::numeric) OR (pro_1.pro_nuevo = 1))
  • Rows Removed by Filter: 3162
22. 564.628 3,512.173 ↓ 5.2 3,314 1

Nested Loop Left Join (cost=200.00..633,972.99 rows=632 width=466) (actual time=48.161..3,512.173 rows=3,314 loops=1)

  • Join Filter: (saldoinv_1.pro_id = pro_1.pro_id)
  • Rows Removed by Join Filter: 5370436
23. 668.106 2,606.203 ↓ 5.2 3,314 1

Foreign Scan on producto pro_1 (cost=100.00..633,845.38 rows=632 width=12) (actual time=1.047..2,606.203 rows=3,314 loops=1)

  • Filter: (((SubPlan 2) > '0'::numeric) OR ((SubPlan 3) > '0'::numeric))
  • Rows Removed by Filter: 551
24.          

SubPlan (forForeign Scan)

25. 1,708.330 1,708.330 ↑ 1.0 1 3,865

Foreign Scan on dlistapre dp (cost=100.00..139.27 rows=1 width=18) (actual time=0.442..0.442 rows=1 loops=3,865)

26. 229.767 229.767 ↑ 1.0 1 551

Foreign Scan on dlistapre dp_1 (cost=100.00..139.27 rows=1 width=18) (actual time=0.417..0.417 rows=1 loops=551)

27. 295.790 341.342 ↓ 1,621.0 1,621 3,314

Materialize (cost=100.00..118.13 rows=1 width=454) (actual time=0.001..0.103 rows=1,621 loops=3,314)

28. 45.552 45.552 ↓ 1,621.0 1,621 1

Foreign Scan on saldoinv saldoinv_1 (cost=100.00..118.12 rows=1 width=454) (actual time=1.620..45.552 rows=1,621 loops=1)

  • Filter: ((sal_periodo)::double precision = date_part('year'::text, (('now'::cstring)::date)::timestamp without time zone))
  • Rows Removed by Filter: 1664
29. 0.002 0.401 ↓ 0.0 0 1

Hash (cost=163.40..163.40 rows=1,780 width=22) (actual time=0.401..0.401 rows=0 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 16kB
30. 0.399 0.399 ↓ 0.0 0 1

Foreign Scan on buffer buf_1 (cost=100.00..163.40 rows=1,780 width=22) (actual time=0.399..0.399 rows=0 loops=1)

31.          

SubPlan (forHash Left Join)

32. 0.057 1.058 ↑ 1,034.7 12 1

Hash Join (cost=318.80..904.42 rows=12,416 width=4) (actual time=1.052..1.058 rows=12 loops=1)

  • Hash Cond: (cor.cor_id = dor.cor_id)
33. 0.447 0.447 ↑ 970.0 1 1

Foreign Scan on cordcom cor (cost=100.00..248.64 rows=970 width=4) (actual time=0.443..0.447 rows=1 loops=1)

  • Filter: (date_part('day'::text, (cor_fecha_disponibilidad - (('now'::cstring)::date)::timestamp without time zone)) <= '21'::double precision)
  • Rows Removed by Filter: 1
34. 0.015 0.554 ↑ 64.0 40 1

Hash (cost=186.80..186.80 rows=2,560 width=8) (actual time=0.553..0.554 rows=40 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 34kB
35. 0.539 0.539 ↑ 64.0 40 1

Foreign Scan on dordcom dor (cost=100.00..186.80 rows=2,560 width=8) (actual time=0.533..0.539 rows=40 loops=1)

36. 0.849 10.418 ↓ 2.5 3,682 1

Hash (cost=168.49..168.49 rows=1,462 width=4) (actual time=10.418..10.418 rows=3,682 loops=1)

  • Buckets: 4096 (originally 2048) Batches: 1 (originally 1) Memory Usage: 162kB
37. 9.569 9.569 ↓ 2.5 3,682 1

Foreign Scan on producto1 pro1_1 (cost=100.00..168.49 rows=1,462 width=4) (actual time=0.879..9.569 rows=3,682 loops=1)