explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CMgk

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 76,217.914 ↑ 1.0 1 1

Aggregate (cost=10,001,431,613.08..10,001,431,613.09 rows=1 width=8) (actual time=76,217.914..76,217.914 rows=1 loops=1)

2. 0.032 76,217.901 ↑ 19,304.6 20 1

Sort (cost=10,001,425,821.70..10,001,426,786.93 rows=386,092 width=540) (actual time=76,217.901..76,217.901 rows=20 loops=1)

  • Sort Key: b.data_arquivo, b.num_doc, b.cod_part
  • Sort Method: quicksort Memory: 27kB
3. 0.696 76,217.869 ↑ 19,304.6 20 1

Merge Left Join (cost=10,001,293,272.37..10,001,296,296.59 rows=386,092 width=540) (actual time=76,217.833..76,217.869 rows=20 loops=1)

  • Merge Cond: ((b.fk_0000 = a.fk_0000) AND (b.cod_part = a.cod_part))
4. 0.039 76,157.602 ↑ 19,304.6 20 1

Sort (cost=10,001,292,151.90..10,001,293,117.13 rows=386,092 width=79) (actual time=76,157.599..76,157.602 rows=20 loops=1)

  • Sort Key: b.fk_0000, b.cod_part
  • Sort Method: quicksort Memory: 27kB
5. 0.006 76,157.563 ↑ 19,304.6 20 1

Subquery Scan on b (cost=10,000,556,481.81..10,001,256,325.29 rows=386,092 width=79) (actual time=67,670.485..76,157.563 rows=20 loops=1)

6. 6,679.181 76,157.557 ↑ 19,304.6 20 1

Hash Join (cost=10,000,556,481.81..10,001,252,464.37 rows=386,092 width=512) (actual time=67,670.485..76,157.557 rows=20 loops=1)

  • Hash Cond: (a_3.fk_c100 = a_1.id)
7. 448.641 67,899.577 ↑ 444.4 895 1

Merge Anti Join (cost=10,000,480,737.65..10,000,768,758.20 rows=397,747 width=12) (actual time=65,637.358..67,899.577 rows=895 loops=1)

  • Merge Cond: (a_3.fk_c100 = a_2.fk_c100)
  • Join Filter: ((a_2.cfop = a_3.cfop) AND (a_2.cst_icms = a_3.cst_icms) AND (a_2.aliq_icms = a_3.aliq_icms))
  • Rows Removed by Join Filter: 85031
8. 175.175 57,331.200 ↑ 5.0 84,341 1

GroupAggregate (cost=10,000,480,737.22..10,000,494,525.77 rows=424,263 width=249) (actual time=57,127.946..57,331.200 rows=84,341 loops=1)

  • Group Key: a_3.fk_c100, (to_date((a_4.dt_in)::text, 'ddMMyyyy'::text)), a_3.fk_0000, a_3.cfop, a_3.aliq_icms, a_3.cst_icms
9. 393.038 57,156.025 ↑ 1.0 422,038 1

Sort (cost=10,000,480,737.22..10,000,481,797.88 rows=424,263 width=25) (actual time=57,127.935..57,156.025 rows=422,038 loops=1)

  • Sort Key: a_3.fk_c100, (to_date((a_4.dt_in)::text, 'ddMMyyyy'::text)), a_3.fk_0000, a_3.cfop, a_3.aliq_icms, a_3.cst_icms
  • Sort Method: quicksort Memory: 45260kB
10. 1,627.365 56,762.987 ↑ 1.0 422,038 1

Hash Join (cost=10,000,000,021.41..10,000,441,080.08 rows=424,263 width=25) (actual time=3,750.326..56,762.987 rows=422,038 loops=1)

  • Hash Cond: (a_3.fk_0000 = a_4.id)
11. 55,135.515 55,135.515 ↓ 1.0 3,818,398 1

Seq Scan on reg_c170 a_3 (cost=0.00..420,212.67 rows=3,818,367 width=21) (actual time=0.018..55,135.515 rows=3,818,398 loops=1)

12. 0.009 0.107 ↑ 6.0 1 1

Hash (cost=10,000,000,021.34..10,000,000,021.34 rows=6 width=13) (actual time=0.107..0.107 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.072 0.098 ↑ 6.0 1 1

Nested Loop (cost=10,000,000,000.14..10,000,000,021.34 rows=6 width=13) (actual time=0.097..0.098 rows=1 loops=1)

  • Join Filter: (((CASE WHEN (btrim((a_4.dt_in)::text) = ''::text) THEN '000000'::text ELSE (substr((a_4.dt_in)::text, 5, 4) || substr((a_4.dt_in)::text, 3, 2)) END)::integer >= (c.data_inicial)::integer) AND ((CASE WHEN (btrim((a_4.dt_in)::text) = ''::text) THEN '000000'::text ELSE (substr((a_4.dt_in)::text, 5, 4) || substr((a_4.dt_in)::text, 3, 2)) END)::integer <= (c.data_final)::integer))
  • Rows Removed by Join Filter: 51
14. 0.026 0.026 ↑ 1.0 52 1

Index Scan using reg_0000_pkey on reg_0000 a_4 (cost=0.14..16.28 rows=52 width=13) (actual time=0.011..0.026 rows=52 loops=1)

15. 0.000 0.000 ↑ 1.0 1 52

Materialize (cost=0.00..1.03 rows=1 width=20) (actual time=0.000..0.000 rows=1 loops=52)

16. 0.012 0.012 ↑ 1.0 1 1

Seq Scan on data_correcao c (cost=0.00..1.02 rows=1 width=20) (actual time=0.012..0.012 rows=1 loops=1)

  • Filter: ((tipo_sped)::text = 'FISCAL'::text)
  • Rows Removed by Filter: 1
17. 10,119.736 10,119.736 ↓ 1.0 2,327,779 1

Index Scan using reg_c190_fk_c100_idx on reg_c190 a_2 (cost=0.43..234,297.67 rows=2,293,293 width=44) (actual time=0.032..10,119.736 rows=2,327,779 loops=1)

18. 530.662 1,578.799 ↓ 1.0 957,842 1

Hash (cost=50,708.04..50,708.04 rows=956,570 width=85) (actual time=1,578.799..1,578.799 rows=957,842 loops=1)

  • Buckets: 1048576 Batches: 2 Memory Usage: 64307kB
19. 1,048.137 1,048.137 ↓ 1.0 957,842 1

Seq Scan on reg_c100 a_1 (cost=0.00..50,708.04 rows=956,570 width=85) (actual time=0.044..1,048.137 rows=957,842 loops=1)

  • Filter: ((cod_part IS NOT NULL) AND (ind_emit = '0'::bpchar))
  • Rows Removed by Filter: 27601
20. 56.697 59.571 ↑ 1.0 8,267 1

Sort (cost=1,120.47..1,141.17 rows=8,278 width=65) (actual time=58.991..59.571 rows=8,267 loops=1)

  • Sort Key: a.fk_0000, a.cod_part
  • Sort Method: quicksort Memory: 1549kB
21. 2.874 2.874 ↑ 1.0 8,278 1

Seq Scan on reg_0150 a (cost=0.00..581.78 rows=8,278 width=65) (actual time=0.019..2.874 rows=8,278 loops=1)