explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7UAe

Settings
# exclusive inclusive rows x rows loops node
1. 1.736 2,878.595 ↑ 1.0 1 1

Aggregate (cost=28,598.25..28,598.26 rows=1 width=0) (actual time=2,878.595..2,878.595 rows=1 loops=1)

2. 11.707 2,876.859 ↓ 12,061.0 12,061 1

Nested Loop (cost=19.29..28,598.25 rows=1 width=0) (actual time=2,661.169..2,876.859 rows=12,061 loops=1)

3. 4.583 2,853.091 ↓ 12,061.0 12,061 1

Merge Join (cost=19.15..28,597.83 rows=1 width=4) (actual time=2,661.143..2,853.091 rows=12,061 loops=1)

  • Merge Cond: (e_lj.t_loja_id = lj.id)
4. 138.194 2,847.456 ↓ 4,020.3 12,061 1

Nested Loop (cost=19.02..28,584.06 rows=3 width=8) (actual time=2,661.075..2,847.456 rows=12,061 loops=1)

  • Join Filter: ((cv.dt_venda_aj >= c.dt_inicio) AND (cv.dt_venda_aj <= c.dt_final) AND (e_lj.t_estab_adq_id = ro.t_estab_adq_id))
  • Rows Removed by Join Filter: 615,111
5. 0.188 0.478 ↓ 13.0 52 1

Nested Loop (cost=0.14..17.09 rows=4 width=24) (actual time=0.023..0.478 rows=52 loops=1)

  • Join Filter: (e_lj.t_contrato_id = c.id)
  • Rows Removed by Join Filter: 156
6. 0.238 0.238 ↑ 1.0 52 1

Index Scan using fk_t_estab_adq_loja1_idx on t_estab_adq_loja_contrato e_lj (cost=0.14..12.92 rows=52 width=12) (actual time=0.015..0.238 rows=52 loops=1)

7. 0.044 0.052 ↑ 1.0 4 52

Materialize (cost=0.00..1.06 rows=4 width=20) (actual time=0.000..0.001 rows=4 loops=52)

8. 0.008 0.008 ↑ 1.0 4 1

Seq Scan on t_contrato c (cost=0.00..1.04 rows=4 width=20) (actual time=0.002..0.008 rows=4 loops=1)

9. 54.759 2,708.784 ↓ 31.8 12,061 52

Materialize (cost=18.88..28,537.59 rows=379 width=20) (actual time=49.584..52.092 rows=12,061 loops=52)

10. 25.329 2,654.025 ↓ 31.8 12,061 1

Nested Loop (cost=18.88..28,535.70 rows=379 width=20) (actual time=2,578.330..2,654.025 rows=12,061 loops=1)

  • Join Filter: (abm.t_adq_id = adq.id)
  • Rows Removed by Join Filter: 144,732
11. 27.537 2,616.635 ↓ 31.8 12,061 1

Nested Loop (cost=18.88..28,460.63 rows=379 width=24) (actual time=2,578.306..2,616.635 rows=12,061 loops=1)

  • Join Filter: (ro.t_estab_adq_id = est.id)
  • Rows Removed by Join Filter: 156,793
12. 0.106 0.106 ↑ 1.0 14 1

Index Only Scan using t_estab_adq_pkey on t_estab_adq est (cost=0.14..12.35 rows=14 width=4) (actual time=0.004..0.106 rows=14 loops=1)

  • Heap Fetches: 14
13. 17.449 2,588.992 ↓ 31.8 12,061 14

Materialize (cost=18.74..28,369.64 rows=379 width=20) (actual time=0.029..184.928 rows=12,061 loops=14)

14. 95.238 2,571.543 ↓ 31.8 12,061 1

Nested Loop (cost=18.74..28,367.75 rows=379 width=20) (actual time=0.398..2,571.543 rows=12,061 loops=1)

15. 77.160 781.449 ↓ 11.7 282,476 1

Nested Loop (cost=18.32..8,900.83 rows=24,041 width=24) (actual time=0.046..781.449 rows=282,476 loops=1)

16. 27.413 122.279 ↓ 11.7 52,910 1

Nested Loop (cost=17.90..948.47 rows=4,503 width=16) (actual time=0.039..122.279 rows=52,910 loops=1)

17. 0.072 0.501 ↓ 11.6 81 1

Nested Loop (cost=1.23..5.15 rows=7 width=12) (actual time=0.022..0.501 rows=81 loops=1)

18. 0.107 0.147 ↓ 11.8 47 1

Hash Join (cost=1.09..2.78 rows=4 width=8) (actual time=0.016..0.147 rows=47 loops=1)

  • Hash Cond: (bmb.t_modl_band_id = mdl.id)
19. 0.031 0.031 ↑ 1.0 47 1

Seq Scan on t_band_modl_band bmb (cost=0.00..1.47 rows=47 width=12) (actual time=0.002..0.031 rows=47 loops=1)

20. 0.003 0.009 ↑ 1.0 4 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
21. 0.006 0.006 ↑ 1.0 4 1

Seq Scan on t_modl_band mdl (cost=0.00..1.04 rows=4 width=4) (actual time=0.006..0.006 rows=4 loops=1)

22. 0.282 0.282 ↑ 2.0 2 47

Index Scan using fk_t_adq_has_t_band_modl_ban_1 on t_adq_band_modl abm (cost=0.14..0.55 rows=4 width=12) (actual time=0.004..0.006 rows=2 loops=47)

  • Index Cond: (t_band_modl_band_id = bmb.id)
23. 88.128 94.365 ↑ 1.8 653 81

Bitmap Heap Scan on t_ro_adq ro (cost=16.66..123.00 rows=1,176 width=28) (actual time=0.101..1.165 rows=653 loops=81)

  • Recheck Cond: (t_adq_band_modl_id = abm.id)
  • Heap Blocks: exact=12,721
24. 6.237 6.237 ↑ 1.8 653 81

Bitmap Index Scan on fk_t_ro_adq_t_adq_band_modl1_i (cost=0.00..16.37 rows=1,176 width=0) (actual time=0.077..0.077 rows=653 loops=81)

  • Index Cond: (t_adq_band_modl_id = abm.id)
25. 582.010 582.010 ↑ 2.2 5 52,910

Index Scan using fk_t_cv_adq_t_ro_adq1_idx on t_cv_adq cv (cost=0.42..1.66 rows=11 width=16) (actual time=0.005..0.011 rows=5 loops=52,910)

  • Index Cond: (t_ro_adq_id = ro.id)
26. 1,694.856 1,694.856 ↓ 0.0 0 282,476

Index Scan using fk_t_parcelas_adq_t_cv_adq1_id on t_parcelas_adq parc (cost=0.42..0.80 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=282,476)

  • Index Cond: (t_cv_adq_id = cv.id)
  • Filter: (CASE WHEN ((fl_conc_pagto)::text ~~ 'LIQUIDADO%'::text) THEN dt_pagto WHEN (nova_data IS NOT NULL) THEN nova_data ELSE data_vencimento END = '2020-09-14 00:00:00'::timestamp without time zone)
  • Rows Removed by Filter: 3
27. 12.049 12.061 ↑ 1.0 13 12,061

Materialize (cost=0.00..1.19 rows=13 width=4) (actual time=0.000..0.001 rows=13 loops=12,061)

28. 0.012 0.012 ↑ 1.0 13 1

Seq Scan on t_adq adq (cost=0.00..1.13 rows=13 width=4) (actual time=0.011..0.012 rows=13 loops=1)

29. 0.929 1.052 ↓ 6,033.0 12,066 1

Materialize (cost=0.14..13.75 rows=2 width=4) (actual time=0.063..1.052 rows=12,066 loops=1)

30. 0.029 0.123 ↓ 6.5 13 1

Nested Loop (cost=0.14..13.74 rows=2 width=4) (actual time=0.059..0.123 rows=13 loops=1)

  • Join Filter: (lj.t_emp_id = emp.id)
  • Rows Removed by Join Filter: 13
31. 0.068 0.068 ↑ 1.0 13 1

Index Scan using t_loja_pkey on t_loja lj (cost=0.14..12.33 rows=13 width=8) (actual time=0.041..0.068 rows=13 loops=1)

32. 0.013 0.026 ↑ 1.0 2 13

Materialize (cost=0.00..1.03 rows=2 width=4) (actual time=0.001..0.002 rows=2 loops=13)

33. 0.013 0.013 ↑ 1.0 2 1

Seq Scan on t_emp emp (cost=0.00..1.02 rows=2 width=4) (actual time=0.011..0.013 rows=2 loops=1)

34. 12.061 12.061 ↑ 1.0 1 12,061

Index Only Scan using t_band_pkey on t_band band (cost=0.14..0.41 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=12,061)

  • Index Cond: (id = bmb.t_band_id)
  • Heap Fetches: 12,061
Planning time : 2.938 ms
Execution time : 2,879.070 ms