explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QOdF

Settings
# exclusive inclusive rows x rows loops node
1. 0.169 5,934.124 ↓ 33.0 33 1

Sort (cost=2,712.30..2,712.30 rows=1 width=164) (actual time=5,934.120..5,934.124 rows=33 loops=1)

  • Sort Key: dmacl_t_doc_registry.datavalidazionedocumento
  • Sort Method: quicksort Memory: 33kB
2. 1.519 5,933.955 ↓ 33.0 33 1

Nested Loop (cost=0.01..2,712.29 rows=1 width=164) (actual time=81.663..5,933.955 rows=33 loops=1)

  • Join Filter: (((dmacl_t_doc_registry.tipologiadcoumentomedio)::text = (dmacl_d_catalogo_tipo_documento.codice_tipo_doc)::text) AND (CASE WHEN (now() > ((dmacl_t_doc_registry.datavalidazionedocumento)::date + CASE WHEN (dmacl_d_catalogo_tipo_documento.durata_validita IS NULL) THEN 31 ELSE dmacl_d_catalogo_tipo_documento.durata_validita END)) THEN 'S'::text ELSE 'N'::text END = 'N'::text))
  • Rows Removed by Join Filter: 103
3. 58.844 5,930.804 ↓ 136.0 136 1

Nested Loop Anti Join (cost=0.01..2,710.87 rows=1 width=139) (actual time=81.568..5,930.804 rows=136 loops=1)

  • Join Filter: (dmacl_t_doc_registry_3.idpazregistry = (pgp_sym_decrypt(dmacl_t_anagregistry.idpazregistry, '303e9a4b62fd417eb1fd64893e8b8971'::text))::bigint)
  • Rows Removed by Join Filter: 89
4. 178.617 4,564.456 ↓ 136.0 136 1

Nested Loop (cost=0.01..2,692.93 rows=1 width=212) (actual time=48.738..4,564.456 rows=136 loops=1)

5. 1,693.240 1,693.240 ↓ 2.1 273 1

Index Scan using dmacl_t_anagregistry_identificativoassistito_idx on dmacl_t_anagregistry (cost=0.00..515.05 rows=127 width=90) (actual time=39.121..1,693.240 rows=273 loops=1)

  • Index Cond: ((identificativoassistito)::text = 'PRSFST66D02L219J'::text)
6. 2,604.063 2,692.599 ↓ 0.0 0 273

Index Scan using ie_dmacl_t_doc_registry_idpazregistry on dmacl_t_doc_registry (cost=0.01..17.14 rows=1 width=130) (actual time=9.862..9.863 rows=0 loops=273)

  • 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('23012020'::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. 86.904 88.536 ↑ 1.0 1 136

Aggregate (cost=8.56..8.57 rows=1 width=8) (actual time=0.651..0.651 rows=1 loops=136)

9. 1.632 1.632 ↑ 1.0 1 136

Index Scan using ie_dmacl_t_doc_registry_idpazregistry on dmacl_t_doc_registry dmacl_t_doc_registry_2 (cost=0.01..8.56 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=136)

  • Index Cond: (idpazregistry = (pgp_sym_decrypt(dmacl_t_anagregistry.idpazregistry, '303e9a4b62fd417eb1fd64893e8b8971'::text))::bigint)
  • Filter: ((iddocumentooid)::text = (dmacl_t_doc_registry.iddocumentooid)::text)
10. 1,307.504 1,307.504 ↑ 1.0 1 136

Index Scan using ie_dmacl_t_doc_registry_iddocumentooid on dmacl_t_doc_registry dmacl_t_doc_registry_3 (cost=0.00..8.96 rows=1 width=60) (actual time=9.613..9.614 rows=1 loops=136)

  • Index Cond: ((iddocumentooid)::text = replace((dmacl_t_doc_registry.iddocumentooid)::text, '_PRE'::text, '_ERO'::text))
11. 1.632 1.632 ↑ 1.0 1 136

Seq Scan on dmacl_d_catalogo_tipo_documento (cost=0.00..1.37 rows=1 width=33) (actual time=0.007..0.012 rows=1 loops=136)

  • Filter: (((codice_tipo_doc)::text = ANY ('{57833-6,57832-8}'::text[])) AND (CASE WHEN ((codice_tipo_doc)::text = '57833-6'::text) THEN 'F'::text ELSE 'P'::text END = 'P'::text))
  • Rows Removed by Filter: 20"Total runtime: 5936.136 ms