explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pjVf

Settings
# exclusive inclusive rows x rows loops node
1. 0.072 952.263 ↓ 13.0 13 1

Sort (cost=762.15..762.15 rows=1 width=164) (actual time=952.262..952.263 rows=13 loops=1)

  • Sort Key: dmacl_t_doc_registry.datavalidazionedocumento
  • Sort Method: quicksort Memory: 28kB
2. 1.044 952.191 ↓ 13.0 13 1

Nested Loop (cost=0.01..762.14 rows=1 width=164) (actual time=776.924..952.191 rows=13 loops=1)

3. 71.483 949.698 ↓ 161.0 161 1

Nested Loop Anti Join (cost=0.01..761.55 rows=1 width=139) (actual time=38.493..949.698 rows=161 loops=1)

  • Join Filter: (dmacl_t_doc_registry_3.idpazregistry = (pgp_sym_decrypt(dmacl_t_anagregistry.idpazregistry, '99999'::text))::bigint)
  • Rows Removed by Join Filter: 118
4. 199.286 305.699 ↓ 161.0 161 1

Nested Loop (cost=0.01..756.32 rows=1 width=212) (actual time=12.725..305.699 rows=161 loops=1)

5. 0.813 0.813 ↓ 2.8 330 1

Index Scan using dmacl_t_anagregistry_identificativoassistito_idx on dmacl_t_anagregistry (cost=0.00..135.57 rows=120 width=90) (actual time=0.032..0.813 rows=330 loops=1)

  • Index Cond: ((identificativoassistito)::text = 'VTTCRL50B45I024K'::text)
6. 4.653 105.600 ↓ 0.0 0 330

Index Scan using ie_dmacl_t_doc_registry_idpazregistry on dmacl_t_doc_registry (cost=0.01..5.16 rows=1 width=130) (actual time=0.320..0.320 rows=0 loops=330)

  • Index Cond: (idpazregistry = (pgp_sym_decrypt(dmacl_t_anagregistry.idpazregistry, '99999'::text))::bigint)
  • Filter: (((statodocentry)::text = 'APPROVED'::text) AND ((tipologiadocumentoalto)::text = 'PRS'::text) AND (datavalidazionedocumento >= to_date('23012018'::text, 'ddmmyyyy'::text)) AND (datavalidazionedocumento <= to_date('04022020'::text, 'ddmmyyyy'::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 ((SubPlan 1) = datavalidazionedocumento))
  • Rows Removed by Filter: 1
7.          

SubPlan (for Index Scan)

8. 99.981 100.947 ↑ 1.0 1 161

Aggregate (cost=2.56..2.57 rows=1 width=8) (actual time=0.627..0.627 rows=1 loops=161)

9. 0.966 0.966 ↑ 1.0 1 161

Index Scan using ie_dmacl_t_doc_registry_idpazregistry on dmacl_t_doc_registry dmacl_t_doc_registry_2 (cost=0.01..2.56 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=161)

  • Index Cond: (idpazregistry = (pgp_sym_decrypt(dmacl_t_anagregistry.idpazregistry, '99999'::text))::bigint)
  • Filter: ((iddocumentooid)::text = (dmacl_t_doc_registry.iddocumentooid)::text)
10. 572.516 572.516 ↑ 1.0 1 161

Index Scan using ie_dmacl_t_doc_registry_iddocumentooid on dmacl_t_doc_registry dmacl_t_doc_registry_3 (cost=0.00..2.60 rows=1 width=60) (actual time=3.533..3.556 rows=1 loops=161)

  • Index Cond: ((iddocumentooid)::text = replace((dmacl_t_doc_registry.iddocumentooid)::text, '_PRE'::text, '_ERO'::text))
11. 1.449 1.449 ↓ 0.0 0 161

Index Scan using ie_dmacl_d_catalogo_tipo_documento_01 on dmacl_d_catalogo_tipo_documento (cost=0.00..0.55 rows=1 width=33) (actual time=0.009..0.009 rows=0 loops=161)

  • Index Cond: (((codice_tipo_doc)::text = (dmacl_t_doc_registry.tipologiadcoumentomedio)::text) AND ((codice_tipo_doc)::text = ANY ('{57833-6,57832-8}'::text[])))
  • Filter: ((CASE WHEN ((codice_tipo_doc)::text = '57833-6'::text) THEN 'F'::text ELSE 'P'::text END = 'P'::text) AND (CASE WHEN (now() > ((dmacl_t_doc_registry.datavalidazionedocumento)::date + CASE WHEN (durata_validita IS NULL) THEN 31 ELSE durata_validita END)) THEN 'S'::text ELSE 'N'::text END = 'N'::text))
  • Rows Removed by Filter: 1"Total runtime: 952.574 ms