explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jYKp

Settings
# exclusive inclusive rows x rows loops node
1. 1.114 209,469.594 ↓ 70.0 70 1

Sort (cost=167.00..167.01 rows=1 width=1,286) (actual time=209,469.580..209,469.594 rows=70 loops=1)

  • Sort Key: n.data_emissao, n.modelo_doc, n.numero_nota
  • Sort Method: quicksort Memory: 100kB
2. 1.695 209,468.480 ↓ 70.0 70 1

Nested Loop Left Join (cost=32.46..166.99 rows=1 width=1,286) (actual time=3,350.288..209,468.480 rows=70 loops=1)

3. 0.400 13.065 ↓ 70.0 70 1

Nested Loop Left Join (cost=32.19..79.39 rows=1 width=1,279) (actual time=0.263..13.065 rows=70 loops=1)

4. 0.388 10.565 ↓ 70.0 70 1

Nested Loop Left Join (cost=6.00..45.27 rows=1 width=1,261) (actual time=0.211..10.565 rows=70 loops=1)

5. 0.321 9.337 ↓ 70.0 70 1

Nested Loop Left Join (cost=1.83..33.96 rows=1 width=1,253) (actual time=0.202..9.337 rows=70 loops=1)

6. 0.368 6.776 ↓ 70.0 70 1

Nested Loop Left Join (cost=1.41..25.51 rows=1 width=1,191) (actual time=0.163..6.776 rows=70 loops=1)

7. 0.256 4.168 ↓ 70.0 70 1

Nested Loop Left Join (cost=0.98..17.06 rows=1 width=1,151) (actual time=0.108..4.168 rows=70 loops=1)

8. 0.605 3.492 ↓ 70.0 70 1

Nested Loop Left Join (cost=0.84..16.89 rows=1 width=1,147) (actual time=0.105..3.492 rows=70 loops=1)

9. 0.647 0.647 ↓ 70.0 70 1

Index Scan using nota_fiscal_ix2 on nota_fiscal n (cost=0.42..8.44 rows=1 width=1,143) (actual time=0.070..0.647 rows=70 loops=1)

  • Index Cond: ((empresa = '32777'::bigint) AND (data_emissao >= '2019-03-04'::date) AND (data_emissao <= '2019-03-05'::date))
10. 2.240 2.240 ↑ 1.0 1 70

Index Scan using nota_fiscal_situacao_nota_fiscal_ix on nota_fiscal_situacao nfs (cost=0.42..8.44 rows=1 width=12) (actual time=0.031..0.032 rows=1 loops=70)

  • Index Cond: (n.grid = nota_fiscal)
11. 0.420 0.420 ↑ 1.0 1 70

Index Only Scan using nota_fiscal_tipo_situacao_pkey on nota_fiscal_tipo_situacao nfts (cost=0.14..0.15 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=70)

  • Index Cond: (codigo = nfs.situacao)
  • Heap Fetches: 59
12. 2.240 2.240 ↑ 1.0 1 70

Index Scan using nota_fiscal_pessoa_pkey on nota_fiscal_pessoa nfp (cost=0.43..8.45 rows=1 width=48) (actual time=0.031..0.032 rows=1 loops=70)

  • Index Cond: (CASE WHEN (n.tipo_emitente = 'T'::text) THEN n.emitente ELSE n.destinatario END = grid)
13. 2.240 2.240 ↑ 1.0 1 70

Index Scan using nfe_nota_fiscal_ix on nfe nf (cost=0.42..8.44 rows=1 width=70) (actual time=0.031..0.032 rows=1 loops=70)

  • Index Cond: (nota_fiscal = n.grid)
14. 0.140 0.840 ↓ 0.0 0 70

Bitmap Heap Scan on cfe cf (cost=4.17..11.28 rows=3 width=24) (actual time=0.012..0.012 rows=0 loops=70)

  • Recheck Cond: (nota_fiscal = n.grid)
15. 0.700 0.700 ↓ 0.0 0 70

Bitmap Index Scan on cfe_nota_fiscal_ix (cost=0.00..4.17 rows=3 width=0) (actual time=0.010..0.010 rows=0 loops=70)

  • Index Cond: (nota_fiscal = n.grid)
16. 0.840 2.100 ↑ 2.0 1 70

Bitmap Heap Scan on pessoa p (cost=26.19..34.10 rows=2 width=42) (actual time=0.029..0.030 rows=1 loops=70)

  • Recheck Cond: ((grid = n.pessoa) OR (grid = nfp.pessoa))
  • Heap Blocks: exact=69
17. 0.140 1.260 ↓ 0.0 0 70

BitmapOr (cost=26.19..26.19 rows=2 width=0) (actual time=0.018..0.018 rows=0 loops=70)

18. 0.980 0.980 ↑ 1.0 1 70

Bitmap Index Scan on pessoa_pkey (cost=0.00..4.30 rows=1 width=0) (actual time=0.014..0.014 rows=1 loops=70)

  • Index Cond: (grid = n.pessoa)
19. 0.140 0.140 ↑ 1.0 1 70

Bitmap Index Scan on pessoa_pkey (cost=0.00..0.30 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=70)

  • Index Cond: (grid = nfp.pessoa)
20. 1.190 1.190 ↓ 0.0 0 70

Index Scan using nota_fiscal_info_complementer_ix1 on nota_fiscal_info_complementar nfic (cost=0.27..8.29 rows=1 width=15) (actual time=0.017..0.017 rows=0 loops=70)

  • Index Cond: (nota_fiscal = n.grid)
21.          

SubPlan (forNested Loop Left Join)

22. 0.140 1.890 ↑ 1.0 1 70

Limit (cost=0.43..4.69 rows=1 width=0) (actual time=0.027..0.027 rows=1 loops=70)

23. 1.750 1.750 ↑ 15.0 1 70

Index Only Scan using nota_fiscal_cupom_ix1 on nota_fiscal_cupom nfc (cost=0.43..64.43 rows=15 width=0) (actual time=0.025..0.025 rows=1 loops=70)

  • Index Cond: (nota_fiscal = n.grid)
  • Heap Fetches: 59
24. 0.140 2.730 ↓ 0.0 0 70

Limit (cost=12.07..12.07 rows=1 width=16) (actual time=0.039..0.039 rows=0 loops=70)

25. 0.910 2.590 ↓ 0.0 0 70

Sort (cost=12.07..12.08 rows=2 width=16) (actual time=0.037..0.037 rows=0 loops=70)

  • Sort Key: nfev.data_registro_evento DESC
  • Sort Method: quicksort Memory: 25kB
26. 0.210 1.680 ↓ 0.0 0 70

Bitmap Heap Scan on nfe_evento nfev (cost=4.31..12.06 rows=2 width=16) (actual time=0.024..0.024 rows=0 loops=70)

  • Recheck Cond: (nfe = nf.grid)
  • Filter: (tipo = ANY ('{210240,210200,210210,210220}'::bigint[]))
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=7
27. 1.470 1.470 ↓ 0.0 0 70

Bitmap Index Scan on nfe_evento_nfe (cost=0.00..4.31 rows=2 width=0) (actual time=0.021..0.021 rows=0 loops=70)

  • Index Cond: (nfe = nf.grid)
28. 0.070 1.470 ↓ 0.0 0 70

Limit (cost=0.29..8.31 rows=1 width=4) (actual time=0.021..0.021 rows=0 loops=70)

29. 1.400 1.400 ↓ 0.0 0 70

Index Scan using nfe_ix on nfe_manifestacao nfem (cost=0.29..8.31 rows=1 width=4) (actual time=0.020..0.020 rows=0 loops=70)

  • Index Cond: (nfe = nf.grid)
30. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.15..5.50 rows=1 width=32) (never executed)

31. 0.000 0.000 ↓ 0.0 0

Index Scan using cfe_nota_fiscal_ix on cfe (cost=0.15..16.20 rows=3 width=32) (never executed)

  • Index Cond: (nota_fiscal = n.grid)
32. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.15..5.50 rows=1 width=32) (never executed)

33. 0.000 0.000 ↓ 0.0 0

Index Scan using cfe_nota_fiscal_ix on cfe cfe_1 (cost=0.15..16.20 rows=3 width=32) (never executed)

  • Index Cond: (nota_fiscal = n.grid)
34. 0.000 0.000 ↓ 0.0 0

Limit (cost=1.95..10.16 rows=1 width=32) (never executed)

35. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.95..51.23 rows=6 width=32) (never executed)

36. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..25.11 rows=3 width=8) (never executed)

37. 0.000 0.000 ↓ 0.0 0

Seq Scan on cfe c (cost=0.00..16.62 rows=3 width=16) (never executed)

  • Filter: (nota_fiscal = n.grid)
38. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.42..8.45 rows=1 width=8) (never executed)

39. 0.000 0.000 ↓ 0.0 0

Index Only Scan using nota_fiscal_pkey on nota_fiscal nfx (cost=0.42..8.44 rows=1 width=8) (never executed)

  • Index Cond: (grid = n.grid)
  • Heap Fetches: 0
40. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on cfe_json cj (cost=1.52..8.66 rows=5 width=40) (never executed)

  • Recheck Cond: (cfe = c.grid)
41. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on nfe_jsojn_cfe (cost=0.00..1.52 rows=5 width=0) (never executed)

  • Index Cond: (cfe = c.grid)
42. 1.400 209,440.840 ↓ 0.0 0 70

Limit (cost=8.60..25.08 rows=1 width=0) (actual time=2,992.012..2,992.012 rows=0 loops=70)

43.          

Initplan (forLimit)

44. 0.980 0.980 ↑ 1.0 1 70

Index Scan using pessoa_pkey on pessoa (cost=0.29..8.31 rows=1 width=16) (actual time=0.013..0.014 rows=1 loops=70)

  • Index Cond: (grid = n.empresa)
  • Filter: (tipo ~~ '%%E%%'::text)
45. 61,267.740 209,438.460 ↓ 0.0 0 70

Index Scan using nfe_inutilizacao_ix1 on nfe_inutilizacao ni (cost=0.29..4,104.58 rows=249 width=0) (actual time=2,991.978..2,991.978 rows=0 loops=70)

  • Index Cond: ((emitente_cnpj = regexp_replace($8, '[^0-9]'::text, ''::text, 'g'::text)) AND (modelo_doc = n.modelo_doc) AND (serie = n.serie))
  • Filter: (SubPlan 8)
  • Rows Removed by Filter: 17639
46.          

SubPlan (forIndex Scan)

47. 148,170.720 148,170.720 ↑ 2.2 454 1,234,756

Function Scan on generate_series (cost=0.00..10.00 rows=1,000 width=8) (actual time=0.067..0.120 rows=454 loops=1,234,756)

48. 0.280 5.600 ↓ 0.0 0 70

Limit (cost=0.00..7.96 rows=1 width=1) (actual time=0.080..0.080 rows=0 loops=70)

49. 5.320 5.320 ↓ 0.0 0 70

Seq Scan on nota_fiscal_referenciada (cost=0.00..7.96 rows=1 width=1) (actual time=0.076..0.076 rows=0 loops=70)

  • Filter: (nota_fiscal = n.grid)
  • Rows Removed by Filter: 271