explain.depesz.com

PostgreSQL's explain analyze made readable

Result: goWT : Optimization for: plan #H1kt

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 7.140 187.963 ↓ 1.2 2,847 1

HashAggregate (cost=35,929.83..35,954.34 rows=2,451 width=140) (actual time=186.472..187.963 rows=2,847 loops=1)

  • Group 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)
2. 0.660 180.823 ↓ 1.2 2,943 1

Append (cost=14,227.01..35,764.38 rows=2,451 width=140) (actual time=18.866..180.823 rows=2,943 loops=1)

3. 40.811 115.660 ↓ 1.3 2,903 1

Merge Left Join (cost=14,227.01..35,302.71 rows=2,232 width=140) (actual time=18.866..115.660 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 1) OR (pro.cla1_id = 509) OR (buf.buf_valor > '0'::numeric) OR (pro.pro_nuevo = 1))
  • Rows Removed by Filter: 310
4. 2.804 51.921 ↓ 1.3 3,213 1

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

  • Merge Cond: (pro.pro_id = buf.pro_id)
5. 4.024 46.139 ↓ 1.9 3,213 1

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

6. 4.195 4.195 ↓ 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.010..4.195 rows=3,792 loops=1)

  • Filter: prod1_muestraapp
  • Rows Removed by Filter: 824
7. 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 2) > '0'::numeric) OR ((SubPlan 3) > '0'::numeric)))
  • Rows Removed by Filter: 0
8.          

SubPlan (forIndex Scan)

9. 11.211 11.211 ↑ 1.0 1 3,737

Index Scan using idx_dlistapre_aux_pro_id_lpr_id on dlistapre_aux dp (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))
10. 10.188 10.188 ↑ 1.0 1 3,396

Index Scan using idx_dlistapre_pro_id_lpr_id on dlistapre dp_1 (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))
11. 2.978 2.978 ↑ 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.003..2.978 rows=3,991 loops=1)

12. 4.475 19.903 ↓ 72.0 3,888 1

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

  • Sort Key: saldoinv.pro_id
  • Sort Method: quicksort Memory: 643kB
13. 14.194 15.428 ↓ 72.0 3,888 1

Bitmap Heap Scan on saldoinv (cost=3,738.36..14,093.57 rows=54 width=79) (actual time=2.198..15.428 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
14. 1.234 1.234 ↓ 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.234..1.234 rows=11,009 loops=1)

  • Index Cond: (bod_id = 1)
15.          

SubPlan (forMerge Left Join)

16. 0.874 3.025 ↓ 2.6 6,280 1

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

17. 0.347 0.347 ↓ 2.4 82 1

Seq Scan on cordcom cor (cost=0.00..5.86 rows=34 width=4) (actual time=0.034..0.347 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
18. 1.804 1.804 ↓ 1.1 77 82

Index Only Scan using idx_dordcom_corpro on dordcom dor (cost=0.28..2.80 rows=70 width=8) (actual time=0.005..0.022 rows=77 loops=82)

  • Index Cond: (cor_id = cor.cor_id)
  • Heap Fetches: 1200
19. 6.617 64.503 ↑ 5.5 40 1

Nested Loop Left Join (cost=200.00..437.17 rows=219 width=140) (actual time=19.102..64.503 rows=40 loops=1)

  • Join Filter: (saldoinv_1.pro_id = pro_1.pro_id)
  • Rows Removed by Join Filter: 64826
20. 5.086 5.086 ↑ 5.5 40 1

Foreign Scan (cost=100.00..300.43 rows=219 width=4) (actual time=5.078..5.086 rows=40 loops=1)

  • Relations: (fdw_motostop.producto pro) LEFT JOIN (fdw_motostop.producto1 pro1)
21. 4.565 52.800 ↓ 1,621.0 1,621 40

Materialize (cost=100.00..118.13 rows=1 width=454) (actual time=0.045..1.320 rows=1,621 loops=40)

22. 48.235 48.235 ↓ 1,621.0 1,621 1

Foreign Scan on saldoinv saldoinv_1 (cost=100.00..118.12 rows=1 width=454) (actual time=1.781..48.235 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