explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eU2S

Settings
# exclusive inclusive rows x rows loops node
1. 0.029 458,725.797 ↑ 7,142.9 7 1

Hash Join (cost=123,021.84..124,153.61 rows=50,000 width=429) (actual time=445,372.635..458,725.797 rows=7 loops=1)

  • Hash Cond: (sni.id_modelo_documento_fiscal = mdf.id_modelo_documento_fiscal)
2.          

CTE sub_numero

3. 20.056 398.565 ↓ 1.3 314,125 1

Append (cost=46.06..100,405.52 rows=234,253 width=8) (actual time=1.153..398.565 rows=314,125 loops=1)

4. 76.250 160.235 ↓ 1.3 310,745 1

Hash Join (cost=46.06..23,371.79 rows=230,962 width=8) (actual time=1.152..160.235 rows=310,745 loops=1)

  • Hash Cond: (nfs.id_modelo_documento_fiscal = mdf_1.id_modelo_documento_fiscal)
5. 82.867 82.867 ↓ 1.1 310,747 1

Index Scan using idx_nota_fiscal_saida_emissao on nota_fiscal_saida nfs (cost=0.43..22,548.80 rows=294,963 width=8) (actual time=0.021..82.867 rows=310,747 loops=1)

  • Index Cond: ((emissao >= '2018-10-01'::date) AND (emissao <= '2018-10-31'::date))
6. 0.328 1.118 ↑ 1.0 830 1

Hash (cost=35.25..35.25 rows=830 width=4) (actual time=1.118..1.118 rows=830 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 38kB
7. 0.790 0.790 ↑ 1.0 830 1

Seq Scan on modelo_documento_fiscal mdf_1 (cost=0.00..35.25 rows=830 width=4) (actual time=0.017..0.790 rows=830 loops=1)

  • Filter: (codigo_modelo_documento = ANY ('{55,65}'::bpchar[]))
  • Rows Removed by Filter: 230
8. 0.777 2.223 ↓ 1.2 3,022 1

Hash Join (cost=119.85..6,326.10 rows=2,582 width=8) (actual time=0.652..2.223 rows=3,022 loops=1)

  • Hash Cond: (nfe.id_modelo_documento_fiscal = mdf_2.id_modelo_documento_fiscal)
9. 0.871 1.107 ↓ 1.0 3,399 1

Bitmap Heap Scan on nota_fiscal_entrada nfe (cost=74.22..6,271.79 rows=3,298 width=8) (actual time=0.285..1.107 rows=3,399 loops=1)

  • Recheck Cond: ((entrada >= '2018-10-01'::date) AND (entrada <= '2018-10-31'::date))
  • Heap Blocks: exact=442
10. 0.236 0.236 ↓ 1.0 3,399 1

Bitmap Index Scan on idx_0414_01 (cost=0.00..73.40 rows=3,298 width=0) (actual time=0.236..0.236 rows=3,399 loops=1)

  • Index Cond: ((entrada >= '2018-10-01'::date) AND (entrada <= '2018-10-31'::date))
11. 0.097 0.339 ↑ 1.0 830 1

Hash (cost=35.25..35.25 rows=830 width=4) (actual time=0.339..0.339 rows=830 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 38kB
12. 0.242 0.242 ↑ 1.0 830 1

Seq Scan on modelo_documento_fiscal mdf_2 (cost=0.00..35.25 rows=830 width=4) (actual time=0.010..0.242 rows=830 loops=1)

  • Filter: (codigo_modelo_documento = ANY ('{55,65}'::bpchar[]))
  • Rows Removed by Filter: 230
13. 0.068 208.538 ↑ 1.2 146 1

Finalize GroupAggregate (cost=59,474.77..59,479.12 rows=174 width=8) (actual time=208.455..208.538 rows=146 loops=1)

  • Group Key: nfs_1.id_modelo_documento_fiscal
14. 0.043 208.470 ↓ 1.1 400 1

Sort (cost=59,474.77..59,475.64 rows=348 width=8) (actual time=208.452..208.470 rows=400 loops=1)

  • Sort Key: nfs_1.id_modelo_documento_fiscal
  • Sort Method: quicksort Memory: 43kB
15. 7.264 208.427 ↓ 1.1 400 1

Gather (cost=59,423.53..59,460.07 rows=348 width=8) (actual time=208.252..208.427 rows=400 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
16. 52.457 201.163 ↑ 1.3 133 3

Partial HashAggregate (cost=58,423.53..58,425.27 rows=174 width=8) (actual time=201.143..201.163 rows=133 loops=3)

  • Group Key: nfs_1.id_modelo_documento_fiscal
17. 63.267 148.706 ↓ 1.0 282,449 3

Hash Join (cost=46.06..57,044.26 rows=275,855 width=8) (actual time=1.117..148.706 rows=282,449 loops=3)

  • Hash Cond: (nfs_1.id_modelo_documento_fiscal = mdf_3.id_modelo_documento_fiscal)
18. 84.414 84.414 ↑ 1.2 282,462 3

Parallel Index Scan using idx_nota_fiscal_saida_emissao on nota_fiscal_saida nfs_1 (cost=0.43..56,070.16 rows=352,297 width=8) (actual time=0.067..84.414 rows=282,462 loops=3)

  • Index Cond: (emissao > '2018-10-31'::date)
19. 0.305 1.025 ↑ 1.0 830 3

Hash (cost=35.25..35.25 rows=830 width=4) (actual time=1.025..1.025 rows=830 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 38kB
20. 0.720 0.720 ↑ 1.0 830 3

Seq Scan on modelo_documento_fiscal mdf_3 (cost=0.00..35.25 rows=830 width=4) (actual time=0.042..0.720 rows=830 loops=3)

  • Filter: (codigo_modelo_documento = ANY ('{55,65}'::bpchar[]))
  • Rows Removed by Filter: 230
21. 1.743 7.513 ↑ 2.5 212 1

HashAggregate (cost=8,880.63..8,885.98 rows=535 width=8) (actual time=7.462..7.513 rows=212 loops=1)

  • Group Key: nfe_1.id_modelo_documento_fiscal
22. 2.219 5.770 ↓ 1.1 9,100 1

Hash Join (cost=241.96..8,840.26 rows=8,074 width=8) (actual time=1.121..5.770 rows=9,100 loops=1)

  • Hash Cond: (nfe_1.id_modelo_documento_fiscal = mdf_4.id_modelo_documento_fiscal)
23. 2.598 3.209 ↑ 1.0 10,199 1

Bitmap Heap Scan on nota_fiscal_entrada nfe_1 (cost=196.34..8,767.46 rows=10,312 width=8) (actual time=0.731..3.209 rows=10,199 loops=1)

  • Recheck Cond: (entrada > '2018-10-31'::date)
  • Heap Blocks: exact=988
24. 0.611 0.611 ↑ 1.0 10,211 1

Bitmap Index Scan on idx_0414_01 (cost=0.00..193.76 rows=10,312 width=0) (actual time=0.611..0.611 rows=10,211 loops=1)

  • Index Cond: (entrada > '2018-10-31'::date)
25. 0.098 0.342 ↑ 1.0 830 1

Hash (cost=35.25..35.25 rows=830 width=4) (actual time=0.342..0.342 rows=830 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 38kB
26. 0.244 0.244 ↑ 1.0 830 1

Seq Scan on modelo_documento_fiscal mdf_4 (cost=0.00..35.25 rows=830 width=4) (actual time=0.011..0.244 rows=830 loops=1)

  • Filter: (codigo_modelo_documento = ANY ('{55,65}'::bpchar[]))
  • Rows Removed by Filter: 230
27.          

CTE sub_extremos

28. 85.102 572.879 ↓ 1.8 351 1

HashAggregate (cost=6,441.96..6,443.96 rows=200 width=12) (actual time=572.807..572.879 rows=351 loops=1)

  • Group Key: sub_numero.id_modelo_documento_fiscal
29. 487.777 487.777 ↓ 1.3 314,125 1

CTE Scan on sub_numero (cost=0.00..4,685.06 rows=234,253 width=8) (actual time=1.156..487.777 rows=314,125 loops=1)

30.          

CTE sub_correta

31. 87,656.835 88,230.220 ↓ 4,644.3 928,857,172 1

ProjectSet (cost=5.50..1,009.00 rows=200,000 width=16) (actual time=573.116..88,230.220 rows=928,857,172 loops=1)

32. 0.418 573.385 ↓ 1.8 351 1

HashAggregate (cost=5.50..7.50 rows=200 width=12) (actual time=573.111..573.385 rows=351 loops=1)

  • Group Key: se.id_modelo_documento_fiscal, se.menor_numero, se.maior_numero
33. 572.967 572.967 ↓ 1.8 351 1

CTE Scan on sub_extremos se (cost=0.00..4.00 rows=200 width=12) (actual time=572.809..572.967 rows=351 loops=1)

34.          

CTE sub_inutilizacao

35. 0.154 0.863 ↑ 717.1 403 1

ProjectSet (cost=57.45..1,507.51 rows=289,000 width=16) (actual time=0.652..0.863 rows=403 loops=1)

36. 0.165 0.709 ↓ 1.3 369 1

HashAggregate (cost=57.45..60.34 rows=289 width=12) (actual time=0.649..0.709 rows=369 loops=1)

  • Group Key: idfe.id_modelo_documento_fiscal, idfe.numero_inicial, idfe.numero_final
37. 0.098 0.544 ↓ 1.3 369 1

Hash Join (cost=45.62..55.28 rows=289 width=12) (actual time=0.413..0.544 rows=369 loops=1)

  • Hash Cond: (idfe.id_modelo_documento_fiscal = mdf_5.id_modelo_documento_fiscal)
38. 0.052 0.052 ↑ 1.0 369 1

Seq Scan on inutilizacao_dfe idfe (cost=0.00..8.69 rows=369 width=12) (actual time=0.014..0.052 rows=369 loops=1)

39. 0.110 0.394 ↑ 1.0 830 1

Hash (cost=35.25..35.25 rows=830 width=4) (actual time=0.394..0.394 rows=830 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 38kB
40. 0.284 0.284 ↑ 1.0 830 1

Seq Scan on modelo_documento_fiscal mdf_5 (cost=0.00..35.25 rows=830 width=4) (actual time=0.009..0.284 rows=830 loops=1)

  • Filter: (codigo_modelo_documento = ANY ('{55,65}'::bpchar[]))
  • Rows Removed by Filter: 230
41.          

CTE sub_numeracao_inutilizada

42. 115,618.539 458,725.405 ↑ 7,142.9 7 1

Hash Join (cost=7,947.50..13,610.00 rows=50,000 width=8) (actual time=445,372.256..458,725.405 rows=7 loops=1)

  • Hash Cond: ((sc.numero = si.numero) AND (sc.id_modelo_documento_fiscal = si.id_modelo_documento_fiscal))
43. 343,105.493 343,105.493 ↓ 4,644.3 928,857,172 1

CTE Scan on sub_correta sc (cost=0.00..4,000.00 rows=200,000 width=8) (actual time=573.117..343,105.493 rows=928,857,172 loops=1)

44. 0.069 1.373 ↑ 72.1 401 1

Hash (cost=7,514.00..7,514.00 rows=28,900 width=8) (actual time=1.373..1.373 rows=401 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 272kB
45. 0.311 1.304 ↑ 72.1 401 1

HashAggregate (cost=7,225.00..7,514.00 rows=28,900 width=8) (actual time=1.116..1.304 rows=401 loops=1)

  • Group Key: si.numero, si.id_modelo_documento_fiscal
46. 0.993 0.993 ↑ 717.1 403 1

CTE Scan on sub_inutilizacao si (cost=0.00..5,780.00 rows=289,000 width=8) (actual time=0.654..0.993 rows=403 loops=1)

47. 458,725.412 458,725.412 ↑ 7,142.9 7 1

CTE Scan on sub_numeracao_inutilizada sni (cost=0.00..1,000.00 rows=50,000 width=8) (actual time=445,372.260..458,725.412 rows=7 loops=1)

48. 0.143 0.356 ↑ 1.0 1,060 1

Hash (cost=32.60..32.60 rows=1,060 width=9) (actual time=0.356..0.356 rows=1,060 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 62kB
49. 0.213 0.213 ↑ 1.0 1,060 1

Seq Scan on modelo_documento_fiscal mdf (cost=0.00..32.60 rows=1,060 width=9) (actual time=0.024..0.213 rows=1,060 loops=1)

Planning time : 5.297 ms
Execution time : 468,491.044 ms