explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5PVI

Settings
# exclusive inclusive rows x rows loops node
1. 0.062 7,791.416 ↓ 11.0 11 1

Sort (cost=2,822.87..2,822.87 rows=1 width=167) (actual time=7,791.414..7,791.416 rows=11 loops=1)

  • Sort Key: b.datavalidazionedocumento
  • Sort Method: quicksort Memory: 27kB
2. 0.543 7,791.354 ↓ 11.0 11 1

Nested Loop (cost=0.01..2,822.86 rows=1 width=167) (actual time=130.866..7,791.354 rows=11 loops=1)

  • Join Filter: (now() <= (b.datavalidazionedocumento + ('1 day'::interval * (COALESCE(d.durata_validita, 31))::double precision)))
  • Rows Removed by Join Filter: 20
3. 0.387 7,790.532 ↓ 31.0 31 1

Nested Loop Anti Join (cost=0.01..2,821.57 rows=1 width=155) (actual time=130.813..7,790.532 rows=31 loops=1)

4. 0.227 7,763.185 ↓ 40.0 40 1

Nested Loop (cost=0.01..2,806.23 rows=1 width=155) (actual time=130.759..7,763.185 rows=40 loops=1)

5. 262.557 7,264.358 ↓ 40.0 40 1

Nested Loop (cost=0.01..2,797.66 rows=1 width=147) (actual time=103.440..7,264.358 rows=40 loops=1)

6. 2,573.369 2,573.369 ↓ 3.2 408 1

Index Scan using dmacl_t_anagregistry_identificativoassistito_idx on dmacl_t_anagregistry a (cost=0.00..515.05 rows=127 width=90) (actual time=49.834..2,573.369 rows=408 loops=1)

  • Index Cond: ((identificativoassistito)::text = 'RVRRRT67P05B719D'::text)
7. 3,941.752 4,428.432 ↓ 0.0 0 408

Index Scan using ie_dmacl_t_doc_registry_idpazregistry on dmacl_t_doc_registry b (cost=0.01..17.96 rows=1 width=138) (actual time=10.854..10.854 rows=0 loops=408)

  • Index Cond: (idpazregistry = (pgp_sym_decrypt(a.idpazregistry, '99999'::text))::bigint)
  • Filter: (((tipologiadcoumentomedio)::text = '57832-8'::text) AND ((statodocentry)::text = 'APPROVED'::text) AND ((tipologiadocumentoalto)::text = 'PRS'::text) AND (datavalidazionedocumento >= to_timestamp('23/01/2018'::text, 'dd/mm/yyyy'::text)) AND (datavalidazionedocumento <= to_timestamp('23/01/2020'::text, 'dd/mm/yyyy'::text)) AND (CASE WHEN (""substring""(""substring""((iddocumentooid)::text, '010[aA-zZ]'::text), 1, 3) = '010'::text) THEN 'RP'::text ELSE 'AR'::text END = 'RP'::text) AND (datavalidazionedocumento = (SubPlan 1)))
  • Rows Removed by Filter: 1
8.          

SubPlan (for Index Scan)

9. 0.360 486.680 ↑ 1.0 1 40

Aggregate (cost=9.39..9.40 rows=1 width=8) (actual time=12.167..12.167 rows=1 loops=40)

10. 486.320 486.320 ↑ 1.0 1 40

Index Scan using ie_dmacl_t_doc_registry_iddocumentooid on dmacl_t_doc_registry z (cost=0.00..9.38 rows=1 width=8) (actual time=12.156..12.158 rows=1 loops=40)

  • Index Cond: ((iddocumentooid)::text = (b.iddocumentooid)::text)
11. 498.600 498.600 ↑ 1.0 1 40

Index Scan using ie_dmacl_t_nre_registry_iddocregistry on dmacl_t_nre_registry c (cost=0.00..8.55 rows=1 width=24) (actual time=12.464..12.465 rows=1 loops=40)

  • Index Cond: (iddocregistry = b.id)
12. 26.960 26.960 ↓ 0.0 0 40

Index Only Scan using ie_dmacl_t_doc_registry_iddocumentooid on dmacl_t_doc_registry y (cost=0.00..7.67 rows=1 width=52) (actual time=0.674..0.674 rows=0 loops=40)

  • Index Cond: (iddocumentooid = replace((b.iddocumentooid)::text, '_PRE'::text, '_ERO'::text))
  • Heap Fetches: 3
13. 0.279 0.279 ↑ 1.0 1 31

Seq Scan on dmacl_d_catalogo_tipo_documento d (cost=0.00..1.26 rows=1 width=33) (actual time=0.005..0.009 rows=1 loops=31)

  • Filter: ((codice_tipo_doc)::text = '57832-8'::text)
  • Rows Removed by Filter: 20"Total runtime: 7793.504 ms