explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZkrT : Optimization for: plan #vWBJ

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.085 3,162.983 ↑ 28.3 145 1

Sort (cost=659,650.56..659,660.81 rows=4,100 width=474) (actual time=3,162.976..3,162.983 rows=145 loops=1)

  • Sort Key: foo.codigo
  • Sort Method: quicksort Memory: 64kB
2. 0.293 3,162.898 ↑ 28.3 145 1

WindowAgg (cost=628,519.86..659,404.53 rows=4,100 width=474) (actual time=2,366.027..3,162.898 rows=145 loops=1)

3. 44.882 3,162.605 ↑ 28.3 145 1

Subquery Scan on foo (cost=628,519.86..659,271.28 rows=4,100 width=442) (actual time=2,354.982..3,162.605 rows=145 loops=1)

  • Filter: (foo.clientes_codigo = 5620)
  • Rows Removed by Filter: 819893
4. 660.443 3,117.723 ↑ 1.0 820,038 1

WindowAgg (cost=628,519.86..649,020.81 rows=820,038 width=442) (actual time=2,282.785..3,117.723 rows=820,038 loops=1)

5. 650.175 2,457.280 ↑ 1.0 820,038 1

Sort (cost=628,519.86..630,569.95 rows=820,038 width=443) (actual time=2,282.774..2,457.280 rows=820,038 loops=1)

  • Sort Key: (COALESCE(pvpg.codprevenda, snfpg.codsaidasnf, cr.codigo)), (CASE WHEN (pvpg.codprevenda IS NOT NULL) THEN 'Pré-venda'::text WHEN (snfpg.codsaidasnf IS NOT NULL) THEN 'Nota de Saída'::text WHEN (cr.codigo IS NOT NULL) THEN 'Conta Avulsa'::text ELSE NULL::text END), cr.datavencimento, cr.codigo
  • Sort Method: external merge Disk: 105904kB
6. 102.681 1,807.105 ↑ 1.0 820,038 1

Gather (cost=86,900.66..217,227.27 rows=820,038 width=443) (actual time=1,204.991..1,807.105 rows=820,038 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 103.420 1,704.424 ↑ 1.2 273,346 3 / 3

Hash Left Join (cost=85,900.66..134,223.47 rows=341,682 width=443) (actual time=1,202.805..1,704.424 rows=273,346 loops=3)

  • Hash Cond: (cr.tipodocumento_codigo = t.codigo)
8. 71.739 1,600.985 ↑ 1.2 273,346 3 / 3

Hash Left Join (cost=85,899.18..133,144.06 rows=341,682 width=329) (actual time=1,202.770..1,600.985 rows=273,346 loops=3)

  • Hash Cond: (cr.gruposcontas_codigo = grct.codigo)
9. 90.144 1,529.243 ↑ 1.2 273,346 3 / 3

Hash Left Join (cost=85,879.28..132,227.25 rows=341,682 width=211) (actual time=1,202.750..1,529.243 rows=273,346 loops=3)

  • Hash Cond: (cr_sit.situacoesconta_codigo = sc.codigo)
10. 69.624 1,439.086 ↑ 1.2 273,346 3 / 3

Hash Left Join (cost=85,859.61..131,301.81 rows=341,682 width=89) (actual time=1,202.733..1,439.086 rows=273,346 loops=3)

  • Hash Cond: (cr.codigo = crav.contasareceber_codigo)
11. 246.780 1,369.442 ↑ 1.2 273,346 3 / 3

Parallel Hash Left Join (cost=85,803.71..130,349.00 rows=341,682 width=81) (actual time=1,202.699..1,369.442 rows=273,346 loops=3)

  • Hash Cond: (cr.codigo = cr_sit.contasareceber_codigo)
12. 59.252 1,050.792 ↑ 1.2 273,346 3 / 3

Hash Left Join (cost=73,151.86..106,789.23 rows=341,682 width=77) (actual time=844.498..1,050.792 rows=273,346 loops=3)

  • Hash Cond: (cr.codigo = snfpg_cr.contasareceber_codigo)
13. 220.325 991.340 ↑ 1.2 273,346 3 / 3

Parallel Hash Left Join (cost=61,811.93..94,158.58 rows=341,682 width=69) (actual time=844.284..991.340 rows=273,346 loops=3)

  • Hash Cond: (cr.codigo = cr_fat.contasareceber_codigo)
14. 203.165 683.539 ↑ 1.2 273,346 3 / 3

Parallel Hash Left Join (cost=48,022.40..70,461.13 rows=341,682 width=57) (actual time=544.201..683.539 rows=273,346 loops=3)

  • Hash Cond: (cr.codigo = pvpg_cr.contasareceber_codigo)
15. 59.032 59.032 ↑ 1.2 273,346 3 / 3

Parallel Seq Scan on contasareceber cr (cost=0.00..12,056.83 rows=341,682 width=45) (actual time=0.025..59.032 rows=273,346 loops=3)

16. 45.732 421.342 ↑ 1.2 273,332 3 / 3

Parallel Hash (cost=42,082.59..42,082.59 rows=341,665 width=16) (actual time=421.342..421.342 rows=273,332 loops=3)

  • Buckets: 131072 Batches: 16 Memory Usage: 3488kB
17. 122.827 375.610 ↑ 1.2 273,332 3 / 3

Parallel Hash Left Join (cost=25,622.17..42,082.59 rows=341,665 width=16) (actual time=294.990..375.610 rows=273,332 loops=3)

  • Hash Cond: (pvpg.codprevenda = pv.codigo)
18. 123.063 197.251 ↑ 1.2 273,332 3 / 3

Parallel Hash Left Join (cost=8,822.38..20,260.91 rows=341,665 width=12) (actual time=115.257..197.251 rows=273,332 loops=3)

  • Hash Cond: (pvpg_cr.prevendas_pagtos_codigo = pvpg.codigo)
19. 26.991 26.991 ↑ 1.2 273,332 3 / 3

Parallel Seq Scan on prevendas_pagtos_contasareceber pvpg_cr (cost=0.00..7,045.65 rows=341,665 width=8) (actual time=0.006..26.991 rows=273,332 loops=3)

20. 26.867 47.197 ↑ 1.3 169,151 3 / 3

Parallel Hash (cost=5,353.39..5,353.39 rows=211,439 width=8) (actual time=47.197..47.197 rows=169,151 loops=3)

  • Buckets: 131072 Batches: 8 Memory Usage: 3552kB
21. 20.330 20.330 ↑ 1.3 169,151 3 / 3

Parallel Seq Scan on prevenda_pagto pvpg (cost=0.00..5,353.39 rows=211,439 width=8) (actual time=0.013..20.330 rows=169,151 loops=3)

22. 26.201 55.532 ↑ 1.2 161,024 3 / 3

Parallel Hash (cost=13,496.80..13,496.80 rows=201,280 width=8) (actual time=55.532..55.532 rows=161,024 loops=3)

  • Buckets: 131072 Batches: 8 Memory Usage: 3456kB
23. 29.331 29.331 ↑ 1.2 161,024 3 / 3

Parallel Seq Scan on prevendas pv (cost=0.00..13,496.80 rows=201,280 width=8) (actual time=0.164..29.331 rows=161,024 loops=3)

24. 49.664 87.476 ↑ 1.3 273,334 3 / 3

Parallel Hash (cost=7,849.68..7,849.68 rows=341,668 width=12) (actual time=87.476..87.476 rows=273,334 loops=3)

  • Buckets: 131072 Batches: 16 Memory Usage: 3456kB
25. 37.812 37.812 ↑ 1.3 273,334 3 / 3

Parallel Seq Scan on contasareceber_faturamentos cr_fat (cost=0.00..7,849.68 rows=341,668 width=12) (actual time=0.154..37.812 rows=273,334 loops=3)

26. 0.007 0.200 ↑ 64.6 35 3 / 3

Hash (cost=11,311.68..11,311.68 rows=2,260 width=12) (actual time=0.200..0.200 rows=35 loops=3)

  • Buckets: 4096 Batches: 1 Memory Usage: 34kB
27. 0.014 0.193 ↑ 64.6 35 3 / 3

Nested Loop Left Join (cost=0.84..11,311.68 rows=2,260 width=12) (actual time=0.038..0.193 rows=35 loops=3)

28. 0.018 0.109 ↑ 64.6 35 3 / 3

Nested Loop Left Join (cost=0.42..9,943.00 rows=2,260 width=8) (actual time=0.031..0.109 rows=35 loops=3)

29. 0.021 0.021 ↑ 64.6 35 3 / 3

Seq Scan on saidasnf_pagtos_contasareceber snfpg_cr (cost=0.00..32.60 rows=2,260 width=8) (actual time=0.019..0.021 rows=35 loops=3)

30. 0.070 0.070 ↑ 1.0 1 105 / 3

Index Scan using saidasnf_pagto_pkey on saidasnf_pagto snfpg (cost=0.42..4.39 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=105)

  • Index Cond: (snfpg_cr.saidasnf_pagtos_codigo = codigo)
31. 0.070 0.070 ↑ 1.0 1 105 / 3

Index Scan using saidasnf_codigo_index on saidasnf snf (cost=0.42..0.61 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=105)

  • Index Cond: (snfpg.codsaidasnf = codigo)
32. 47.183 71.870 ↑ 1.2 273,346 3 / 3

Parallel Hash (cost=7,045.83..7,045.83 rows=341,682 width=8) (actual time=71.870..71.870 rows=273,346 loops=3)

  • Buckets: 131072 Batches: 16 Memory Usage: 3072kB
33. 24.687 24.687 ↑ 1.2 273,346 3 / 3

Parallel Seq Scan on contasareceber_situacao cr_sit (cost=0.00..7,045.83 rows=341,682 width=8) (actual time=0.008..24.687 rows=273,346 loops=3)

34. 0.003 0.020 ↑ 340.0 6 3 / 3

Hash (cost=30.40..30.40 rows=2,040 width=12) (actual time=0.019..0.020 rows=6 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
35. 0.017 0.017 ↑ 340.0 6 3 / 3

Seq Scan on contasareceber_avulsas crav (cost=0.00..30.40 rows=2,040 width=12) (actual time=0.016..0.017 rows=6 loops=3)

36. 0.002 0.013 ↑ 71.7 6 3 / 3

Hash (cost=14.30..14.30 rows=430 width=126) (actual time=0.013..0.013 rows=6 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.011 0.011 ↑ 71.7 6 3 / 3

Seq Scan on situacoescontas sc (cost=0.00..14.30 rows=430 width=126) (actual time=0.010..0.011 rows=6 loops=3)

38. 0.000 0.003 ↓ 0.0 0 3 / 3

Hash (cost=14.40..14.40 rows=440 width=122) (actual time=0.003..0.003 rows=0 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
39. 0.003 0.003 ↓ 0.0 0 3 / 3

Seq Scan on gruposcontas grct (cost=0.00..14.40 rows=440 width=122) (actual time=0.003..0.003 rows=0 loops=3)

40. 0.007 0.019 ↓ 1.0 22 3 / 3

Hash (cost=1.21..1.21 rows=21 width=82) (actual time=0.019..0.019 rows=22 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
41. 0.012 0.012 ↓ 1.0 22 3 / 3

Seq Scan on tipodocumento t (cost=0.00..1.21 rows=21 width=82) (actual time=0.010..0.012 rows=22 loops=3)

Planning time : 2.532 ms
Execution time : 3,243.872 ms