explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ndLA

Settings
# exclusive inclusive rows x rows loops node
1. 23,694.529 26,232.816 ↓ 45.1 10,471 1

WindowAgg (cost=2,566,203.57..2,566,303.33 rows=232 width=424) (actual time=2,521.880..26,232.816 rows=10,471 loops=1)

2.          

CTE filiais

3. 0.196 2.185 ↑ 1.5 68 1

Nested Loop (cost=4.22..965.28 rows=100 width=26) (actual time=0.365..2.185 rows=68 loops=1)

4. 0.194 1.092 ↑ 1.4 69 1

Nested Loop (cost=3.79..322.58 rows=100 width=14) (actual time=0.318..1.092 rows=69 loops=1)

5. 0.147 0.346 ↑ 1.4 69 1

HashAggregate (cost=3.52..4.52 rows=100 width=4) (actual time=0.279..0.346 rows=69 loops=1)

  • Group Key: ((unnest('{10021,10026,10035,10037,10053,10054,10055,10057,10058,10059,10060,10065,10066,10067,10074,10075,10083,10086,10090,10091,10092,10094,10096,10097,10102,10103,10104,10105,10110,10113,10117,10120,10127,10139,10148,10151,10157,10162,10181,10189,10199,10200,10205,10206,10207,10209,10218,10219,10220,10221,10236,10238,10239,10240,10256,10257,10259,10260,10278,10279,10295,10296,10308,10317,10326,10331,10332,10334,10339}'::text[])))::integer
6. 0.118 0.199 ↑ 1.4 69 1

Result (cost=0.00..2.27 rows=100 width=4) (actual time=0.022..0.199 rows=69 loops=1)

7. 0.078 0.081 ↑ 1.4 69 1

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.013..0.081 rows=69 loops=1)

8. 0.003 0.003 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.003 rows=1 loops=1)

9. 0.552 0.552 ↑ 1.0 1 69

Index Scan using pk_filial on filial f (cost=0.27..3.71 rows=1 width=14) (actual time=0.008..0.008 rows=1 loops=69)

  • Index Cond: ((idfilial)::integer = (((unnest('{10021,10026,10035,10037,10053,10054,10055,10057,10058,10059,10060,10065,10066,10067,10074,10075,10083,10086,10090,10091,10092,10094,10096,10097,10102,10103,10104,10105,10110,10113,10117,10120,10127,10139,10148,10151,10157,10162,10181,10189,10199,10200,10205,10206,10207,10209,10218,10219,10220,10221,10236,10238,10239,10240,10256,10257,10259,10260,10278,10279,10295,10296,10308,10317,10326,10331,10332,10334,10339}'::text[])))::integer))
10. 0.897 0.897 ↑ 1.0 1 69

Index Scan using pk_pessoa on pessoa p (cost=0.43..6.43 rows=1 width=20) (actual time=0.013..0.013 rows=1 loops=69)

  • Index Cond: ((idcnpj_cpf)::bigint = (f.idcnpj_cpf)::bigint)
11.          

CTE tags

12. 0.019 4.825 ↑ 1.0 1 1

Aggregate (cost=30,968.97..30,968.98 rows=1 width=32) (actual time=4.823..4.825 rows=1 loops=1)

13. 0.025 4.806 ↓ 5.0 5 1

Unique (cost=419.82..30,968.96 rows=1 width=35) (actual time=4.150..4.806 rows=5 loops=1)

14. 2.497 4.781 ↑ 5.1 22 1

Foreign Scan on tax_grid g (cost=419.82..30,968.68 rows=112 width=35) (actual time=4.148..4.781 rows=22 loops=1)

  • Filter: ((NOT (alternatives: SubPlan 2 or hashed SubPlan 3)) OR (alternatives: SubPlan 4 or hashed SubPlan 5))
  • Rows Removed by Filter: 127
15.          

SubPlan (forForeign Scan)

16. 0.000 0.000 ↓ 0.0 0

Foreign Scan on tax_grid_usuario u (cost=100.29..104.77 rows=12 width=32) (never executed)

17. 1.126 1.126 ↑ 1.0 69 1

Foreign Scan on tax_grid_usuario u_1 (cost=100.28..106.87 rows=69 width=32) (actual time=1.073..1.126 rows=69 loops=1)

18. 0.000 0.000 ↓ 0.0 0

Foreign Scan on tax_grid_usuario u_2 (cost=100.30..104.34 rows=1 width=32) (never executed)

19. 1.158 1.158 ↑ 1.0 69 1

Foreign Scan on tax_grid_usuario u_3 (cost=100.28..106.87 rows=69 width=64) (actual time=1.101..1.158 rows=69 loops=1)

20. 200.148 2,538.287 ↓ 45.1 10,471 1

Sort (cost=2,534,269.30..2,534,269.88 rows=232 width=173) (actual time=2,521.212..2,538.287 rows=10,471 loops=1)

  • Sort Key: nx.chnfe
  • Sort Method: quicksort Memory: 3169kB
21. 39.859 2,338.139 ↓ 45.1 10,471 1

Nested Loop Left Join (cost=125,414.52..2,534,260.19 rows=232 width=173) (actual time=1,002.312..2,338.139 rows=10,471 loops=1)

22. 661.875 2,277.338 ↓ 45.1 10,471 1

Bitmap Heap Scan on importacaoxml nx (cost=125,414.52..2,534,255.54 rows=232 width=141) (actual time=997.477..2,277.338 rows=10,471 loops=1)

  • Recheck Cond: ((dataemissao >= '2019-07-07'::date) AND (dataemissao <= '2019-08-07'::date) AND (tipoconsultasefaz = 1))
  • Filter: ((hashed SubPlan 7) AND (((cnpjimportacao)::text = (cnpj_cpf_destinatario)::text) OR ((cnpj_cpf_destinatario)::text = (cnpj_cpf_emissor)::text)) AND CASE WHEN (idempresa = ANY ('{8,19}'::integer[])) THEN (alternatives: SubPlan 8 or hashed SubPlan 9) ELSE (SubPlan 10) END)
  • Rows Removed by Filter: 88067
  • Heap Blocks: exact=24201
23. 5.306 987.219 ↓ 0.0 0 1

BitmapAnd (cost=125,412.27..125,412.27 rows=92,898 width=0) (actual time=987.218..987.219 rows=0 loops=1)

24. 53.837 53.837 ↓ 1.1 153,091 1

Bitmap Index Scan on idx_importacaoxml_emissao_destinatario (cost=0.00..4,278.94 rows=133,251 width=0) (actual time=53.836..53.837 rows=153,091 loops=1)

  • Index Cond: ((dataemissao >= '2019-07-07'::date) AND (dataemissao <= '2019-08-07'::date))
25. 928.076 928.076 ↓ 1.0 1,073,015 1

Bitmap Index Scan on pk_importacaoxml (cost=0.00..121,132.96 rows=1,067,075 width=0) (actual time=928.075..928.076 rows=1,073,015 loops=1)

  • Index Cond: (tipoconsultasefaz = 1)
26.          

SubPlan (forBitmap Heap Scan)

27. 2.345 2.345 ↑ 1.5 68 1

CTE Scan on filiais (cost=0.00..2.00 rows=100 width=18) (actual time=0.372..2.345 rows=68 loops=1)

28. 0.000 0.000 ↓ 0.0 0

Index Only Scan using mat_nota_entrada_idx on mat_nota_entrada ne (cost=0.42..6.44 rows=1 width=0) (never executed)

  • Index Cond: (chave_acesso_nfel = (nx.chnfe)::text)
  • Heap Fetches: 0
29. 0.000 0.000 ↓ 0.0 0

Seq Scan on mat_nota_entrada ne_1 (cost=0.00..1,916.77 rows=99,077 width=32) (never executed)

30. 625.899 625.899 ↑ 1.0 1 13,317

Index Scan using idx_idnfesefaz on nota n (cost=0.56..18.45 rows=1 width=0) (actual time=0.047..0.047 rows=1 loops=13,317)

  • Index Cond: ((idnfesefaz)::text = (nx.chnfe)::text)
  • Filter: ((idoperacao)::integer < 502000)
  • Rows Removed by Filter: 0
31. 20.942 20.942 ↑ 1.0 1 10,471

CTE Scan on tags (cost=0.00..0.02 rows=1 width=32) (actual time=0.001..0.002 rows=1 loops=10,471)

Planning time : 9.527 ms