#	exclusive	inclusive	rows x	rows	loops	node
1.	3,800.096	3,800.096	↓ 1.0	205,443	1	→ CTE Scan on contas_final (cost=3,024,688.35..3,034,427.93 rows=204,611 width=305) (actual time=324.054..3,800.096 rows=205,443 loops=1) Filter: (contasareceber_faturamentos_codigo IS NOT NULL) Rows Removed by Filter: 196
2.						CTE contas
3.	129.276	2,187.840	↑ 1.0	205,639	1	→ Nested Loop Left Join (cost=43,546.46..2,986,105.05 rows=205,639 width=294) (actual time=323.934..2,187.840 rows=205,639 loops=1)
4.	93.722	1,236.008	↑ 1.0	205,639	1	→ Hash Left Join (cost=43,532.31..72,174.71 rows=205,639 width=258) (actual time=323.844..1,236.008 rows=205,639 loops=1) Hash Cond: (cr.codigo = cr_fat.contasareceber_codigo)
5.	55.297	1,088.468	↑ 1.0	205,639	1	→ Hash Left Join (cost=37,799.81..63,617.07 rows=205,639 width=254) (actual time=268.962..1,088.468 rows=205,639 loops=1) Hash Cond: (cr_sit.situacoesconta_codigo = sc.codigo)
6.	89.513	1,033.158	↑ 1.0	205,639	1	→ Hash Left Join (cost=37,798.68..60,788.39 rows=205,639 width=257) (actual time=268.940..1,033.158 rows=205,639 loops=1) Hash Cond: (cr.codigo = cr_sit.contasareceber_codigo)
7.	109.550	870.450	↑ 1.0	205,639	1	→ Hash Right Join (cost=32,260.80..52,422.98 rows=205,639 width=253) (actual time=194.625..870.450 rows=205,639 loops=1) Hash Cond: (crpg.contasareceber_faturamentos_cr_codigo = cr.codigo)
8.	548.492	699.462	↑ 1.0	204,168	1	→ GroupAggregate (cost=25,157.92..40,470.97 rows=204,174 width=24) (actual time=132.106..699.462 rows=204,168 loops=1) Group Key: crpg.contasareceber_faturamentos_cr_codigo
9.	99.680	150.970	↑ 1.0	204,170	1	→ Sort (cost=25,157.92..25,668.36 rows=204,174 width=24) (actual time=132.081..150.970 rows=204,170 loops=1) Sort Key: crpg.contasareceber_faturamentos_cr_codigo Sort Method: quicksort Memory: 22095kB
10.	37.986	51.290	↑ 1.0	204,170	1	→ Hash Anti Join (cost=5.67..7,150.35 rows=204,174 width=24) (actual time=0.058..51.290 rows=204,170 loops=1) Hash Cond: (crpg.codigo = crpg_e.contasareceber_pagtos_codigo)
11.	13.256	13.256	↑ 1.0	204,337	1	→ Seq Scan on contasareceber_pagtos crpg (cost=0.00..4,566.37 rows=204,337 width=28) (actual time=0.003..13.256 rows=204,337 loops=1)
12.	0.031	0.048	↓ 1.0	167	1	→ Hash (cost=3.63..3.63 rows=163 width=4) (actual time=0.048..0.048 rows=167 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 14kB
13.	0.017	0.017	↓ 1.0	167	1	→ Seq Scan on contasareceber_pagtos_estornos crpg_e (cost=0.00..3.63 rows=163 width=4) (actual time=0.005..0.017 rows=167 loops=1)
14.	45.816	61.438	↑ 1.0	205,639	1	→ Hash (cost=4,532.39..4,532.39 rows=205,639 width=57) (actual time=61.438..61.438 rows=205,639 loops=1) Buckets: 262144 Batches: 1 Memory Usage: 22040kB
15.	15.622	15.622	↑ 1.0	205,639	1	→ Seq Scan on contasareceber cr (cost=0.00..4,532.39 rows=205,639 width=57) (actual time=0.006..15.622 rows=205,639 loops=1)
16.	51.317	73.195	↑ 1.0	205,639	1	→ Hash (cost=2,967.39..2,967.39 rows=205,639 width=8) (actual time=73.195..73.195 rows=205,639 loops=1) Buckets: 262144 Batches: 1 Memory Usage: 10081kB
17.	21.878	21.878	↑ 1.0	205,639	1	→ Seq Scan on contasareceber_situacao cr_sit (cost=0.00..2,967.39 rows=205,639 width=8) (actual time=0.010..21.878 rows=205,639 loops=1)
18.	0.010	0.013	↑ 1.0	6	1	→ Hash (cost=1.06..1.06 rows=6 width=5) (actual time=0.013..0.013 rows=6 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB
19.	0.003	0.003	↑ 1.0	6	1	→ Seq Scan on situacoescontas sc (cost=0.00..1.06 rows=6 width=5) (actual time=0.003..0.003 rows=6 loops=1)
20.	34.028	53.818	↓ 1.0	205,443	1	→ Hash (cost=3,165.00..3,165.00 rows=205,400 width=4) (actual time=53.818..53.818 rows=205,443 loops=1) Buckets: 262144 Batches: 1 Memory Usage: 9271kB
21.	19.790	19.790	↓ 1.0	205,443	1	→ Seq Scan on contasareceber_faturamentos cr_fat (cost=0.00..3,165.00 rows=205,400 width=4) (actual time=0.005..19.790 rows=205,443 loops=1)
22.	0.000	822.556	↑ 1.0	1	205,639	→ Limit (cost=14.15..14.15 rows=1 width=21) (actual time=0.004..0.004 rows=1 loops=205,639)
23.	205.639	822.556	↑ 1.0	1	205,639	→ Sort (cost=14.15..14.15 rows=1 width=21) (actual time=0.004..0.004 rows=1 loops=205,639) Sort Key: crpg_1.dhpagamento DESC Sort Method: quicksort Memory: 25kB
24.	205.589	616.917	↑ 1.0	1	205,639	→ Hash Anti Join (cost=6.09..14.14 rows=1 width=21) (actual time=0.003..0.003 rows=1 loops=205,639) Hash Cond: (crpg_1.codigo = crpg_e_1.contasareceber_pagtos_codigo)
25.	411.278	411.278	↑ 1.0	1	205,639	→ Index Scan using contasareceber_pagtos_cr_codigo_dhpagamento_idx on contasareceber_pagtos crpg_1 (cost=0.42..8.45 rows=1 width=25) (actual time=0.002..0.002 rows=1 loops=205,639) Index Cond: (contasareceber_faturamentos_cr_codigo = cr.codigo) Filter: ((dhpagamento)::date <= (now())::date)
26.	0.027	0.050	↓ 1.0	167	1	→ Hash (cost=3.63..3.63 rows=163 width=4) (actual time=0.050..0.050 rows=167 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 14kB
27.	0.023	0.023	↓ 1.0	167	1	→ Seq Scan on contasareceber_pagtos_estornos crpg_e_1 (cost=0.00..3.63 rows=163 width=4) (actual time=0.004..0.023 rows=167 loops=1)
28.						CTE datas_uteis
29.	57.128	110.656	↓ 7.4	7,423	1	→ Function Scan on obter_datas_uteis diautil (cost=4,627.14..4,637.14 rows=1,000 width=4) (actual time=110.375..110.656 rows=7,423 loops=1)
30.						Initplan (forFunction Scan)
31.	14.056	53.528	↑ 1.0	1	1	→ Aggregate (cost=4,626.88..4,626.89 rows=1 width=4) (actual time=53.527..53.528 rows=1 loops=1)
32.	39.472	39.472	↑ 1.0	205,639	1	→ CTE Scan on contas (cost=0.00..4,112.78 rows=205,639 width=4) (actual time=0.001..39.472 rows=205,639 loops=1)
33.						CTE config_carencia_juros
34.	0.100	0.100	↑ 1.0	1	1	→ Function Scan on carenciajuros (cost=0.26..0.27 rows=1 width=4) (actual time=0.099..0.100 rows=1 loops=1)
35.						CTE contas_saldos_ultimopagtoatrasado
36.	64.716	3,196.175	↑ 1.0	205,639	1	→ Nested Loop (cost=0.00..23,149.85 rows=205,639 width=427) (actual time=324.039..3,196.175 rows=205,639 loops=1)
37.	0.101	0.101	↑ 1.0	1	1	→ CTE Scan on config_carencia_juros (cost=0.00..0.02 rows=1 width=4) (actual time=0.100..0.101 rows=1 loops=1)
38.	2,357.158	2,357.158	↑ 1.0	205,639	1	→ CTE Scan on contas contas_1 (cost=0.00..4,112.78 rows=205,639 width=423) (actual time=323.938..2,357.158 rows=205,639 loops=1)
39.						SubPlan (forNested Loop)
40.	0.000	774.200	↑ 1.0	1	1,106	→ Limit (cost=0.00..0.08 rows=1 width=4) (actual time=0.700..0.700 rows=1 loops=1,106)
41.	774.200	774.200	↑ 333.0	1	1,106	→ CTE Scan on datas_uteis (cost=0.00..27.50 rows=333 width=4) (actual time=0.700..0.700 rows=1 loops=1,106) Filter: (diautil >= (contas_1.datavencimento + config_carencia_juros.carenciajuros)) Rows Removed by Filter: 6287
42.						CTE contas_final
43.	3,507.617	3,507.617	↑ 1.0	205,639	1	→ CTE Scan on contas_saldos_ultimopagtoatrasado (cost=0.00..10,796.05 rows=205,639 width=428) (actual time=324.048..3,507.617 rows=205,639 loops=1)

CTE Scan on contas_final  (cost=3024688.35..3034427.93 rows=204611 width=305) (actual time=324.054..3800.096 rows=205443 loops=1)
  Filter: (contasareceber_faturamentos_codigo IS NOT NULL)
  Rows Removed by Filter: 196
  CTE contas
    ->  Nested Loop Left Join  (cost=43546.46..2986105.05 rows=205639 width=294) (actual time=323.934..2187.840 rows=205639 loops=1)
          ->  Hash Left Join  (cost=43532.31..72174.71 rows=205639 width=258) (actual time=323.844..1236.008 rows=205639 loops=1)
                Hash Cond: (cr.codigo = cr_fat.contasareceber_codigo)
                ->  Hash Left Join  (cost=37799.81..63617.07 rows=205639 width=254) (actual time=268.962..1088.468 rows=205639 loops=1)
                      Hash Cond: (cr_sit.situacoesconta_codigo = sc.codigo)
                      ->  Hash Left Join  (cost=37798.68..60788.39 rows=205639 width=257) (actual time=268.940..1033.158 rows=205639 loops=1)
                            Hash Cond: (cr.codigo = cr_sit.contasareceber_codigo)
                            ->  Hash Right Join  (cost=32260.80..52422.98 rows=205639 width=253) (actual time=194.625..870.450 rows=205639 loops=1)
                                  Hash Cond: (crpg.contasareceber_faturamentos_cr_codigo = cr.codigo)
                                  ->  GroupAggregate  (cost=25157.92..40470.97 rows=204174 width=24) (actual time=132.106..699.462 rows=204168 loops=1)
                                        Group Key: crpg.contasareceber_faturamentos_cr_codigo
                                        ->  Sort  (cost=25157.92..25668.36 rows=204174 width=24) (actual time=132.081..150.970 rows=204170 loops=1)
                                              Sort Key: crpg.contasareceber_faturamentos_cr_codigo
                                              Sort Method: quicksort  Memory: 22095kB
                                              ->  Hash Anti Join  (cost=5.67..7150.35 rows=204174 width=24) (actual time=0.058..51.290 rows=204170 loops=1)
                                                    Hash Cond: (crpg.codigo = crpg_e.contasareceber_pagtos_codigo)
                                                    ->  Seq Scan on contasareceber_pagtos crpg  (cost=0.00..4566.37 rows=204337 width=28) (actual time=0.003..13.256 rows=204337 loops=1)
                                                    ->  Hash  (cost=3.63..3.63 rows=163 width=4) (actual time=0.048..0.048 rows=167 loops=1)
                                                          Buckets: 1024  Batches: 1  Memory Usage: 14kB
                                                          ->  Seq Scan on contasareceber_pagtos_estornos crpg_e  (cost=0.00..3.63 rows=163 width=4) (actual time=0.005..0.017 rows=167 loops=1)
                                  ->  Hash  (cost=4532.39..4532.39 rows=205639 width=57) (actual time=61.438..61.438 rows=205639 loops=1)
                                        Buckets: 262144  Batches: 1  Memory Usage: 22040kB
                                        ->  Seq Scan on contasareceber cr  (cost=0.00..4532.39 rows=205639 width=57) (actual time=0.006..15.622 rows=205639 loops=1)
                            ->  Hash  (cost=2967.39..2967.39 rows=205639 width=8) (actual time=73.195..73.195 rows=205639 loops=1)
                                  Buckets: 262144  Batches: 1  Memory Usage: 10081kB
                                  ->  Seq Scan on contasareceber_situacao cr_sit  (cost=0.00..2967.39 rows=205639 width=8) (actual time=0.010..21.878 rows=205639 loops=1)
                      ->  Hash  (cost=1.06..1.06 rows=6 width=5) (actual time=0.013..0.013 rows=6 loops=1)
                            Buckets: 1024  Batches: 1  Memory Usage: 9kB
                            ->  Seq Scan on situacoescontas sc  (cost=0.00..1.06 rows=6 width=5) (actual time=0.003..0.003 rows=6 loops=1)
                ->  Hash  (cost=3165.00..3165.00 rows=205400 width=4) (actual time=53.818..53.818 rows=205443 loops=1)
                      Buckets: 262144  Batches: 1  Memory Usage: 9271kB
                      ->  Seq Scan on contasareceber_faturamentos cr_fat  (cost=0.00..3165.00 rows=205400 width=4) (actual time=0.005..19.790 rows=205443 loops=1)
          ->  Limit  (cost=14.15..14.15 rows=1 width=21) (actual time=0.004..0.004 rows=1 loops=205639)
                ->  Sort  (cost=14.15..14.15 rows=1 width=21) (actual time=0.004..0.004 rows=1 loops=205639)
                      Sort Key: crpg_1.dhpagamento DESC
                      Sort Method: quicksort  Memory: 25kB
                      ->  Hash Anti Join  (cost=6.09..14.14 rows=1 width=21) (actual time=0.003..0.003 rows=1 loops=205639)
                            Hash Cond: (crpg_1.codigo = crpg_e_1.contasareceber_pagtos_codigo)
                            ->  Index Scan using contasareceber_pagtos_cr_codigo_dhpagamento_idx on contasareceber_pagtos crpg_1  (cost=0.42..8.45 rows=1 width=25) (actual time=0.002..0.002 rows=1 loops=205639)
                                  Index Cond: (contasareceber_faturamentos_cr_codigo = cr.codigo)
                                  Filter: ((dhpagamento)::date <= (now())::date)
                            ->  Hash  (cost=3.63..3.63 rows=163 width=4) (actual time=0.050..0.050 rows=167 loops=1)
                                  Buckets: 1024  Batches: 1  Memory Usage: 14kB
                                  ->  Seq Scan on contasareceber_pagtos_estornos crpg_e_1  (cost=0.00..3.63 rows=163 width=4) (actual time=0.004..0.023 rows=167 loops=1)
  CTE datas_uteis
    ->  Function Scan on obter_datas_uteis diautil  (cost=4627.14..4637.14 rows=1000 width=4) (actual time=110.375..110.656 rows=7423 loops=1)
          InitPlan 2 (returns $2)
            ->  Aggregate  (cost=4626.88..4626.89 rows=1 width=4) (actual time=53.527..53.528 rows=1 loops=1)
                  ->  CTE Scan on contas  (cost=0.00..4112.78 rows=205639 width=4) (actual time=0.001..39.472 rows=205639 loops=1)
  CTE config_carencia_juros
    ->  Function Scan on carenciajuros  (cost=0.26..0.27 rows=1 width=4) (actual time=0.099..0.100 rows=1 loops=1)
  CTE contas_saldos_ultimopagtoatrasado
    ->  Nested Loop  (cost=0.00..23149.85 rows=205639 width=427) (actual time=324.039..3196.175 rows=205639 loops=1)
          ->  CTE Scan on config_carencia_juros  (cost=0.00..0.02 rows=1 width=4) (actual time=0.100..0.101 rows=1 loops=1)
          ->  CTE Scan on contas contas_1  (cost=0.00..4112.78 rows=205639 width=423) (actual time=323.938..2357.158 rows=205639 loops=1)
          SubPlan 5
            ->  Limit  (cost=0.00..0.08 rows=1 width=4) (actual time=0.700..0.700 rows=1 loops=1106)
                  ->  CTE Scan on datas_uteis  (cost=0.00..27.50 rows=333 width=4) (actual time=0.700..0.700 rows=1 loops=1106)
                        Filter: (diautil >= (contas_1.datavencimento + config_carencia_juros.carenciajuros))
                        Rows Removed by Filter: 6287
  CTE contas_final
    ->  CTE Scan on contas_saldos_ultimopagtoatrasado  (cost=0.00..10796.05 rows=205639 width=428) (actual time=324.048..3507.617 rows=205639 loops=1)
Planning time: 1.119 ms
Execution time: 3872.901 ms

Per node type stats

node type	count	sum of times	% of query
Aggregate	1	14.056 ms	0.4 %
CTE Scan	6	10,478.644 ms	275.7 %
Function Scan	2	57.228 ms	1.5 %
GroupAggregate	1	548.492 ms	14.4 %
Hash	6	131.229 ms	3.5 %
Hash Anti Join	2	243.575 ms	6.4 %
Hash Left Join	3	238.532 ms	6.3 %
Hash Right Join	1	109.550 ms	2.9 %
Index Scan	1	411.278 ms	10.8 %
Limit	2	0.000 ms	0.0 %
Nested Loop	1	64.716 ms	1.7 %
Nested Loop Left Join	1	129.276 ms	3.4 %
Seq Scan	7	70.589 ms	1.9 %
Sort	2	305.319 ms	8.0 %

Per table stats

Table name	Scan count	Total time	% of query
scan type	count	sum of times	% of table
contasareceber	1	15.622 ms	0.4 %
Seq Scan	1	15.622 ms	100.0 %
contasareceber_faturamentos	1	19.790 ms	0.5 %
Seq Scan	1	19.790 ms	100.0 %
contasareceber_pagtos	2	424.534 ms	11.2 %
Index Scan	1	411.278 ms	96.9 %
Seq Scan	1	13.256 ms	3.1 %
contasareceber_pagtos_estornos	2	0.040 ms	0.0 %
Seq Scan	2	0.040 ms	100.0 %
contasareceber_situacao	1	21.878 ms	0.6 %
Seq Scan	1	21.878 ms	100.0 %
situacoescontas	1	0.003 ms	0.0 %
Seq Scan	1	0.003 ms	100.0 %

explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kX9k : Optimization for: plan #rXW

Color mode:

Visible columns:

Optimization path:

Per node type stats

Per table stats