explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lMdV

Settings
# exclusive inclusive rows x rows loops node
1. 0.369 629.518 ↑ 1.0 7,377 1

Limit (cost=10,000,135,672.15..10,000,137,650.66 rows=7,377 width=1,267) (actual time=584.432..629.518 rows=7,377 loops=1)

2. 61.979 629.149 ↑ 1.0 7,377 1

WindowAgg (cost=10,000,135,672.15..10,000,137,650.66 rows=7,377 width=1,267) (actual time=584.428..629.149 rows=7,377 loops=1)

3. 32.218 567.170 ↑ 1.0 7,377 1

Hash Left Join (cost=10,000,135,672.15..10,000,136,083.04 rows=7,377 width=1,883) (actual time=489.676..567.170 rows=7,377 loops=1)

  • Hash Cond: (a_6.uf = a_5.uf)
  • Join Filter: ((b_5.dt_doc >= to_date((a_5.dt_inicial)::text, 'yyyymmdd'::text)) AND (b_5.dt_doc <= to_date((a_5.dt_final)::text, 'yyyymmdd'::text)))
  • Rows Removed by Join Filter: 14754
4. 8.000 533.906 ↑ 1.0 7,377 1

Hash Left Join (cost=10,000,135,592.43..10,000,135,900.54 rows=7,377 width=1,881) (actual time=488.607..533.906 rows=7,377 loops=1)

  • Hash Cond: (b_5.cod_mun = a_6.cod_mun)
5. 5.748 522.376 ↑ 1.0 7,377 1

Hash Left Join (cost=10,000,135,213.11..10,000,135,419.78 rows=7,377 width=1,878) (actual time=485.040..522.376 rows=7,377 loops=1)

  • Hash Cond: ((b_5.fk_0000 = a_3.fk_0000) AND (a_4.cod_item = a_3.cod_item))
6. 18.938 511.937 ↑ 1.0 7,377 1

Merge Left Join (cost=10,000,134,679.48..10,000,134,757.06 rows=7,377 width=1,602) (actual time=480.335..511.937 rows=7,377 loops=1)

  • Merge Cond: (b_5.fk_0000 = a_1.fk_0000)
  • Join Filter: (a_1.cod_nat = a_4.cod_nat)
  • Rows Removed by Join Filter: 125409
7. 6.819 488.347 ↑ 1.0 7,377 1

Merge Left Join (cost=10,000,134,657.60..10,000,134,715.51 rows=7,377 width=1,343) (actual time=480.273..488.347 rows=7,377 loops=1)

  • Merge Cond: ((b_5.fk_0000 = a_2.fk_0000) AND (a_4.unid = a_2.unid))
8. 18.144 481.051 ↑ 1.0 7,377 1

Sort (cost=10,000,134,652.68..10,000,134,671.13 rows=7,377 width=1,084) (actual time=480.201..481.051 rows=7,377 loops=1)

  • Sort Key: b_5.fk_0000, a_4.unid
  • Sort Method: quicksort Memory: 15062kB
9. 4.711 462.907 ↑ 1.0 7,377 1

Hash Join (cost=10,000,133,257.55..10,000,134,178.76 rows=7,377 width=1,084) (actual time=453.982..462.907 rows=7,377 loops=1)

  • Hash Cond: (a_4.fk_c100 = b_5.id)
10. 4.333 4.333 ↑ 1.0 7,377 1

Seq Scan on reg_c170 a_4 (cost=0.00..819.77 rows=7,377 width=718) (actual time=0.015..4.333 rows=7,377 loops=1)

  • Filter: ((cod_item IS NOT NULL) AND (unid IS NOT NULL) AND (cod_nat IS NOT NULL))
11. 39.154 453.863 ↓ 8.0 66,909 1

Hash (cost=10,000,133,153.53..10,000,133,153.53 rows=8,322 width=370) (actual time=453.863..453.863 rows=66,909 loops=1)

  • Buckets: 131072 (originally 16384) Batches: 1 (originally 1) Memory Usage: 15925kB
12. 12.788 414.709 ↓ 8.0 66,909 1

Subquery Scan on b_5 (cost=10,000,000,035.16..10,000,133,153.53 rows=8,322 width=370) (actual time=5.850..414.709 rows=66,909 loops=1)

13. 111.030 401.921 ↓ 8.0 66,909 1

Hash Join (cost=10,000,000,035.16..10,000,133,070.31 rows=8,322 width=382) (actual time=5.849..401.921 rows=66,909 loops=1)

  • Hash Cond: (a_8.fk_0000 = b_7.fk_0000)
  • Join Filter: ((SubPlan 3) AND (SubPlan 4) AND CASE WHEN (b_7.tipo = 'C100'::bpchar) THEN ((CASE WHEN (a_8.dt_e_s <> ''::bpchar) THEN to_date((a_8.dt_e_s)::text, 'ddMMyyyy'::text) ELSE to_date((a_8.dt_doc)::text, 'ddMMyyyy'::text) END >= (SubPlan 1)) AND (CASE WHEN (a_8.dt_e_s <> ''::bpchar) THEN to_date((a_8.dt_e_s)::text, 'ddMMyyyy'::text) ELSE to_date((a_8.dt_doc)::text, 'ddMMyyyy'::text) END <= $1)) ELSE true END)
14.          

Initplan (for Hash Join)

15. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.00..1.01 rows=1 width=4) (never executed)

16. 0.000 0.000 ↓ 0.0 0

Seq Scan on tab_cenario_fiscal_parametros c_1 (cost=0.00..1.01 rows=1 width=4) (never executed)

17. 21.110 151.638 ↓ 1.0 66,909 1

Hash Left Join (cost=29.52..6,116.25 rows=66,574 width=315) (actual time=0.373..151.638 rows=66,909 loops=1)

  • Hash Cond: ((a_8.fk_0000 = a_7.fk_0000) AND (a_8.cod_part = a_7.cod_part))
  • Filter: (CASE WHEN (a_7.cod_mun IS NULL) THEN ''::bpchar ELSE a_7.cod_mun END IS NOT NULL)
18. 130.210 130.210 ↑ 1.0 66,909 1

Seq Scan on reg_c100 a_8 (cost=0.00..4,915.82 rows=66,909 width=164) (actual time=0.039..130.210 rows=66,909 loops=1)

  • Filter: ((cod_part IS NOT NULL) AND (id IS NOT NULL))
19. 0.122 0.318 ↑ 1.0 341 1

Hash (cost=24.41..24.41 rows=341 width=216) (actual time=0.318..0.318 rows=341 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 91kB
20. 0.196 0.196 ↑ 1.0 341 1

Seq Scan on reg_0150 a_7 (cost=0.00..24.41 rows=341 width=216) (actual time=0.010..0.196 rows=341 loops=1)

21. 0.009 5.435 ↑ 4.0 1 1

Hash (cost=10,000,000,004.57..10,000,000,004.57 rows=4 width=114) (actual time=5.435..5.435 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.002 5.426 ↑ 4.0 1 1

Subquery Scan on b_7 (cost=10,000,000,000.00..10,000,000,004.57 rows=4 width=114) (actual time=5.423..5.426 rows=1 loops=1)

23. 5.404 5.424 ↑ 4.0 1 1

Nested Loop (cost=10,000,000,000.00..10,000,000,004.53 rows=4 width=140) (actual time=5.422..5.424 rows=1 loops=1)

  • Join Filter: CASE WHEN (a_9.tipo = '0000'::bpchar) THEN ((to_date(""substring""((b_8.dt_in)::text, 3, 6), 'MMyyyy'::text) >= to_date((a_9.data_inicial)::text, 'yyyyMM'::text)) AND (to_date(""substring""((b_8.dt_in)::text, 3, 6), 'MMyyyy'::text) <= to_date((a_9.data_final)::text, 'yyyyMM'::text))) ELSE true END
24. 0.010 0.010 ↑ 1.0 1 1

Seq Scan on tab_cenario_fiscal_parametros a_9 (cost=0.00..1.01 rows=1 width=60) (actual time=0.010..0.010 rows=1 loops=1)

25. 0.010 0.010 ↑ 9.0 1 1

Seq Scan on reg_0000 b_8 (cost=0.00..1.09 rows=9 width=27) (actual time=0.009..0.010 rows=1 loops=1)

26.          

SubPlan (for Hash Join)

27. 66.909 66.909 ↑ 50.0 2 66,909

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.001..0.001 rows=2 loops=66,909)

28. 0.000 0.000 ↑ 1.0 1 66,909

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=66,909)

29. 66.909 66.909 ↑ 100.0 1 66,909

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.001..0.001 rows=1 loops=66,909)

30. 0.000 0.000 ↑ 1.0 1 66,909

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=66,909)

31. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.00..1.01 rows=1 width=4) (never executed)

32. 0.000 0.000 ↓ 0.0 0

Seq Scan on tab_cenario_fiscal_parametros c (cost=0.00..1.01 rows=1 width=4) (never executed)

33. 0.442 0.477 ↓ 147.5 7,377 1

Sort (cost=4.91..5.04 rows=50 width=273) (actual time=0.065..0.477 rows=7,377 loops=1)

  • Sort Key: a_2.fk_0000, a_2.unid
  • Sort Method: quicksort Memory: 34kB
34. 0.035 0.035 ↑ 2.6 19 1

Seq Scan on reg_0190 a_2 (cost=0.00..3.50 rows=50 width=273) (actual time=0.033..0.035 rows=19 loops=1)

35. 4.638 4.652 ↓ 522.5 125,410 1

Sort (cost=21.89..22.49 rows=240 width=274) (actual time=0.057..4.652 rows=125,410 loops=1)

  • Sort Key: a_1.fk_0000
  • Sort Method: quicksort Memory: 34kB
36. 0.014 0.014 ↑ 13.3 18 1

Seq Scan on reg_0400 a_1 (cost=0.00..12.40 rows=240 width=274) (actual time=0.012..0.014 rows=18 loops=1)

37. 1.804 4.691 ↑ 1.0 4,265 1

Hash (cost=469.65..469.65 rows=4,265 width=341) (actual time=4.691..4.691 rows=4,265 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 1618kB
38. 2.887 2.887 ↑ 1.0 4,265 1

Seq Scan on reg_0200 a_3 (cost=0.00..469.65 rows=4,265 width=341) (actual time=0.010..2.887 rows=4,265 loops=1)

39. 1.805 3.530 ↓ 1.0 5,571 1

Hash (cost=309.70..309.70 rows=5,570 width=270) (actual time=3.530..3.530 rows=5,571 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 1713kB
40. 1.725 1.725 ↓ 1.0 5,571 1

Seq Scan on tab_municipios_ibge a_6 (cost=0.00..309.70 rows=5,570 width=270) (actual time=0.012..1.725 rows=5,571 loops=1)

41. 0.021 1.046 ↓ 7.4 81 1

Hash (cost=79.58..79.58 rows=11 width=26) (actual time=1.046..1.046 rows=81 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
42. 0.006 1.025 ↓ 7.4 81 1

Subquery Scan on a_5 (cost=79.33..79.58 rows=11 width=26) (actual time=1.002..1.025 rows=81 loops=1)

43. 0.018 1.019 ↓ 7.4 81 1

Group (cost=79.33..79.47 rows=11 width=26) (actual time=1.000..1.019 rows=81 loops=1)

  • Group Key: a.uf_orig, a.aliquota, a.dt_inicial, a.dt_final
44. 0.206 1.001 ↓ 7.4 81 1

Sort (cost=79.33..79.36 rows=11 width=26) (actual time=0.999..1.001 rows=81 loops=1)

  • Sort Key: a.uf_orig, a.aliquota, a.dt_inicial, a.dt_final
  • Sort Method: quicksort Memory: 31kB
45. 0.795 0.795 ↓ 7.4 81 1

Seq Scan on tab_aliq_interestaduais a (cost=0.00..79.14 rows=11 width=26) (actual time=0.030..0.795 rows=81 loops=1)

  • Filter: (upper(btrim((uf_orig)::text)) = upper(btrim((uf_dest)::text)))
  • Rows Removed by Filter: 2106
Planning time : 12.374 ms