explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dCgX

Settings
# exclusive inclusive rows x rows loops node
1. 470.838 12,623.499 ↓ 58.0 58 1

WindowAgg (cost=17.07..15,957.17 rows=1 width=639) (actual time=12,048.711..12,623.499 rows=58 loops=1)

2. 5.297 12,037.965 ↓ 58.0 58 1

Nested Loop Left Join (cost=17.07..15,821.87 rows=1 width=362) (actual time=123.206..12,037.965 rows=58 loops=1)

  • Join Filter: ((aslgestite.codice)::text = (verifiche_1.codasl)::text)
  • Rows Removed by Join Filter: 5220
3. 4.886 7,299.230 ↓ 58.0 58 1

Nested Loop Left Join (cost=15.99..15,712.05 rows=1 width=270) (actual time=118.487..7,299.230 rows=58 loops=1)

  • Join Filter: ((aslgestite.codice)::text = (verifiche.codasl)::text)
  • Rows Removed by Join Filter: 5046
4. 0.358 2.352 ↓ 58.0 58 1

Nested Loop (cost=15.99..46.20 rows=1 width=42) (actual time=0.268..2.352 rows=58 loops=1)

5. 0.302 1.008 ↓ 58.0 58 1

Hash Left Join (cost=15.85..36.03 rows=1 width=12) (actual time=0.235..1.008 rows=58 loops=1)

  • Hash Cond: ((richieste_provider.id = stati_providers.id_richiesta) AND (richieste_provider.codxrich = stati_providers.codxrich))
  • Filter: ((CASE stati_providers.stato WHEN 'P'::text THEN 'A'::character varying ELSE stati_providers.stato END)::text = 'A'::text)
  • Rows Removed by Filter: 32
6. 0.507 0.507 ↓ 2.0 90 1

Seq Scan on richieste_provider (cost=0.00..19.29 rows=44 width=14) (actual time=0.016..0.507 rows=90 loops=1)

  • Filter: ((rev = 1) AND (abilitazione = 1))
  • Rows Removed by Filter: 129
7. 0.098 0.199 ↓ 1.0 395 1

Hash (cost=9.94..9.94 rows=394 width=6) (actual time=0.199..0.199 rows=395 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
8. 0.101 0.101 ↓ 1.0 395 1

Seq Scan on stati_providers (cost=0.00..9.94 rows=394 width=6) (actual time=0.007..0.101 rows=395 loops=1)

9. 0.986 0.986 ↑ 1.0 1 58

Index Scan using pk_aslgestite_codice on aslgestite (cost=0.14..10.16 rows=1 width=48) (actual time=0.017..0.017 rows=1 loops=58)

  • Index Cond: ((codice)::text = (richieste_provider.codasl)::text)
  • Filter: (((flg_accrprov)::text = 'S'::text) AND ((COALESCE(NULLIF('0'::text, ''::text), '0'::text) = '0'::text) OR (SubPlan 5)))
10.          

SubPlan (for Index Scan)

11. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_uf_form on uf_form (cost=0.14..8.18 rows=1 width=0) (never executed)

  • Index Cond: ((richieste_provider.codasl)::text = (codasl)::text)
  • Filter: ((dt_conf_organigramma IS NULL) OR ((dt_conf_pianoform IS NULL) AND (aslgestite.data_accr < to_date(NULLIF('01012013'::text, ''::text), 'DDMMYYYY'::text))) OR (dt_conf_risorse IS NULL) OR ((dt_conf_convfsc IS NULL) AND ((richieste_provider.profilo)::text = ANY ('{B2,C2,C3}'::text[])) AND ("substring"((richieste_provider.metodologie_form)::text, 4, 1) = '1'::text)))
12. 4,599.052 7,291.992 ↓ 44.0 88 58

Nested Loop Left Join (cost=0.00..15,665.80 rows=2 width=796) (actual time=1.092..125.724 rows=88 loops=58)

  • Join Filter: (((verifiche.tipo)::text = (verifiche_ana_feedback.tipo_verifica)::text) AND ((verifiche.stato_fb)::text = (verifiche_ana_feedback.codice)::text))
  • Rows Removed by Join Filter: 177
13. 10.556 2,637.434 ↓ 44.0 88 58

Nested Loop Left Join (cost=0.00..15,640.33 rows=2 width=57) (actual time=0.179..45.473 rows=88 loops=58)

  • Join Filter: (((verifiche.tipo)::text = (verifiche_ana_stati.tipo_verifica)::text) AND ((verifiche.stato)::text = (verifiche_ana_stati.codice)::text))
  • Rows Removed by Join Filter: 87
14. 75.980 2,626.878 ↓ 44.0 88 58

Seq Scan on verifiche (cost=0.00..15,639.20 rows=2 width=50) (actual time=0.177..45.291 rows=88 loops=58)

  • Filter: ((dt_obsol IS NULL) AND ((tipo)::text = 'V'::text) AND (CASE WHEN (dt_prov_fb IS NULL) THEN 'S'::text ELSE CASE WHEN (NOT (SubPlan 8)) THEN 'S'::text ELSE 'N'::text END END = 'S'::text))
  • Rows Removed by Filter: 434
15.          

SubPlan (for Seq Scan)

16. 2,550.898 2,550.898 ↑ 2.0 1 24,766

Seq Scan on verifiche v_1 (cost=0.00..61.82 rows=2 width=0) (actual time=0.103..0.103 rows=1 loops=24,766)

  • Filter: ((dt_obsol IS NULL) AND (dt_st > verifiche.dt_st) AND ((verifiche.codasl)::text = (codasl)::text) AND ((tipo)::text = 'V'::text))
  • Rows Removed by Filter: 304
17. 0.000 0.000 ↑ 1.0 2 5,104

Materialize (cost=0.00..1.06 rows=2 width=25) (actual time=0.000..0.000 rows=2 loops=5,104)

18. 0.005 0.005 ↑ 1.0 2 1

Seq Scan on verifiche_ana_stati (cost=0.00..1.05 rows=2 width=25) (actual time=0.005..0.005 rows=2 loops=1)

  • Filter: ((tipo_verifica)::text = 'V'::text)
  • Rows Removed by Filter: 2
19. 0.000 0.000 ↑ 1.0 3 5,104

Materialize (cost=0.00..1.06 rows=3 width=32) (actual time=0.000..0.000 rows=3 loops=5,104)

20. 0.004 0.004 ↑ 1.0 3 1

Seq Scan on verifiche_ana_feedback (cost=0.00..1.05 rows=3 width=32) (actual time=0.003..0.004 rows=3 loops=1)

  • Filter: ((tipo_verifica)::text = 'V'::text)
  • Rows Removed by Filter: 1
21.          

SubPlan (for Nested Loop Left Join)

22. 25.230 55.506 ↓ 0.0 0 5,046

Bitmap Heap Scan on verifiche_nc (cost=4.30..11.65 rows=1 width=0) (actual time=0.011..0.011 rows=0 loops=5,046)

  • Recheck Cond: (verifiche.seriale = seriale)
  • Filter: ((conformita)::text = 'N'::text)
  • Rows Removed by Filter: 3
  • Heap Blocks: exact=5220
23. 30.276 30.276 ↑ 1.0 3 5,046

Bitmap Index Scan on pk_verifiche_nc (cost=0.00..4.30 rows=3 width=0) (actual time=0.006..0.006 rows=3 loops=5,046)

  • Index Cond: (verifiche.seriale = seriale)
24. 0.000 0.000 ↓ 0.0 0

Seq Scan on verifiche_nc verifiche_nc_1 (cost=0.00..26.43 rows=304 width=8) (never executed)

  • Filter: ((conformita)::text = 'N'::text)
25. 4,716.086 4,733.438 ↑ 1.0 91 58

Hash Left Join (cost=1.08..107.77 rows=91 width=808) (actual time=0.987..81.611 rows=91 loops=58)

  • Hash Cond: (((verifiche_1.tipo)::text = (verifiche_ana_stati_1.tipo_verifica)::text) AND ((verifiche_1.stato)::text = (verifiche_ana_stati_1.codice)::text))
26. 17.342 17.342 ↑ 1.0 91 58

Seq Scan on verifiche verifiche_1 (cost=0.00..59.30 rows=91 width=50) (actual time=0.006..0.299 rows=91 loops=58)

  • Filter: ((dt_obsol IS NULL) AND ((tipo)::text = 'R'::text))
  • Rows Removed by Filter: 431
27. 0.003 0.010 ↑ 1.0 2 1

Hash (cost=1.05..1.05 rows=2 width=25) (actual time=0.010..0.010 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
28. 0.007 0.007 ↑ 1.0 2 1

Seq Scan on verifiche_ana_stati verifiche_ana_stati_1 (cost=0.00..1.05 rows=2 width=25) (actual time=0.006..0.007 rows=2 loops=1)

  • Filter: ((tipo_verifica)::text = 'R'::text)
  • Rows Removed by Filter: 2
29.          

SubPlan (for WindowAgg)

30. 98.781 110.295 ↓ 0.0 0 57

Aggregate (cost=59.32..59.33 rows=1 width=8) (actual time=1.935..1.935 rows=0 loops=57)

  • Filter: (max(verifiche_2.dt_prov_fb) < func_calc_dtconf((aslgestite.codice)::text))
  • Rows Removed by Filter: 1
31. 11.514 11.514 ↓ 1.4 7 57

Seq Scan on verifiche verifiche_2 (cost=0.00..59.30 rows=5 width=8) (actual time=0.063..0.202 rows=7 loops=57)

  • Filter: ((dt_prov_fb IS NOT NULL) AND (dt_obsol IS NULL) AND ((codasl)::text = (aslgestite.codice)::text))
  • Rows Removed by Filter: 515
32. 0.000 0.000 ↓ 0.0 0

Seq Scan on verifiche verifiche_3 (cost=0.00..59.30 rows=1 width=0) (never executed)

  • Filter: ((dt_prov_fb IS NULL) AND (dt_obsol IS NULL) AND ((codasl)::text = (aslgestite.codice)::text))
33. 0.167 0.167 ↑ 2.0 4 1

Seq Scan on verifiche verifiche_4 (cost=0.00..58.04 rows=8 width=32) (actual time=0.015..0.167 rows=4 loops=1)

  • Filter: ((dt_prov_fb IS NULL) AND (dt_obsol IS NULL))
  • Rows Removed by Filter: 518
34. 4.234 4.234 ↑ 4.0 1 58

Seq Scan on verifiche v (cost=0.00..61.82 rows=4 width=0) (actual time=0.073..0.073 rows=1 loops=58)

  • Filter: ((dt_obsol IS NULL) AND (seriale <> COALESCE(verifiche.seriale, '0'::bigint)) AND ((codasl)::text = (verifiche.codasl)::text) AND ((tipo)::text = 'V'::text))
  • Rows Removed by Filter: 144
Planning time : 9.108 ms
Execution time : 12,624.267 ms