explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Z44g

Settings
# exclusive inclusive rows x rows loops node
1. 0.044 215.435 ↓ 14.0 14 1

Sort (cost=842.40..842.40 rows=1 width=159) (actual time=215.433..215.435 rows=14 loops=1)

  • Sort Key: b.datavalidazionedocumento
  • Sort Method: quicksort Memory: 28kB
2. 0.085 215.391 ↓ 14.0 14 1

Nested Loop Anti Join (cost=0.01..842.39 rows=1 width=159) (actual time=11.261..215.391 rows=14 loops=1)

3. 0.219 214.766 ↓ 18.0 18 1

Nested Loop (cost=0.01..837.75 rows=1 width=159) (actual time=11.222..214.766 rows=18 loops=1)

  • Join Filter: (CASE WHEN (now() > (b.datavalidazionedocumento + ('1 day'::interval * (COALESCE(d.durata_validita, 31))::double precision))) THEN 'S'::text ELSE 'N'::text END = 'N'::text)
  • Rows Removed by Join Filter: 15
4. 0.050 214.382 ↓ 33.0 33 1

Nested Loop (cost=0.01..836.47 rows=1 width=155) (actual time=11.197..214.382 rows=33 loops=1)

5. 208.642 214.101 ↓ 33.0 33 1

Nested Loop (cost=0.01..833.91 rows=1 width=147) (actual time=11.185..214.101 rows=33 loops=1)

6. 0.587 0.587 ↓ 2.7 348 1

Index Scan using dmacl_t_anagregistry_identificativoassistito_idx on dmacl_t_anagregistry a (cost=0.00..144.55 rows=128 width=90) (actual time=0.030..0.587 rows=348 loops=1)

  • Index Cond: ((identificativoassistito)::text = 'CRTNTN37C24G136A'::text)
7. 3.486 4.872 ↓ 0.0 0 348

Index Scan using ie_dmacl_t_doc_registry_idpazregistry on dmacl_t_doc_registry b (cost=0.01..5.38 rows=1 width=138) (actual time=0.014..0.014 rows=0 loops=348)

  • Index Cond: (idpazregistry = (pgp_sym_decrypt(a.idpazregistry, '99999999'::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 (""substring""(""substring""((iddocumentooid)::text, '010[aA-zZ]'::text), 1, 3) = '010'::text) AND (datavalidazionedocumento = (SubPlan 1)))
  • Rows Removed by Filter: 1
8.          

SubPlan (for Index Scan)

9. 0.099 1.386 ↑ 1.0 1 33

Aggregate (cost=2.78..2.79 rows=1 width=8) (actual time=0.042..0.042 rows=1 loops=33)

10. 1.287 1.287 ↑ 1.0 1 33

Index Scan using ie_dmacl_t_doc_registry_iddocumentooid on dmacl_t_doc_registry z (cost=0.00..2.78 rows=1 width=8) (actual time=0.038..0.039 rows=1 loops=33)

  • Index Cond: ((iddocumentooid)::text = (b.iddocumentooid)::text)
11. 0.231 0.231 ↑ 1.0 1 33

Index Scan using ie_dmacl_t_nre_registry_iddocregistry on dmacl_t_nre_registry c (cost=0.00..2.55 rows=1 width=24) (actual time=0.007..0.007 rows=1 loops=33)

  • Index Cond: (iddocregistry = b.id)
12. 0.165 0.165 ↑ 1.0 1 33

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

  • Filter: ((codice_tipo_doc)::text = '57832-8'::text)
  • Rows Removed by Filter: 20
13. 0.540 0.540 ↓ 0.0 0 18

Index Only Scan using ie_dmacl_t_doc_registry_iddocumentooid on dmacl_t_doc_registry y (cost=0.00..2.31 rows=1 width=52) (actual time=0.030..0.030 rows=0 loops=18)

  • Index Cond: (iddocumentooid = replace((b.iddocumentooid)::text, '_PRE'::text, '_ERO'::text))
  • Heap Fetches: 4"Total runtime: 215.594 ms