explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8Ei4 : Optimization for: Optimization for: Optimization for: plan #CI54; plan #nH4h; plan #mLSU

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.428 1,060.671 ↑ 1.3 35 1

Sort (cost=3,033.35..3,033.47 rows=47 width=92) (actual time=1,060.663..1,060.671 rows=35 loops=1)

  • Sort Key: (sum(t.qtd_os)) DESC
  • Sort Method: quicksort Memory: 29kB
2.          

CTE ungrouped

3. 0.690 71.752 ↓ 32.9 1,548 1

Append (cost=0.99..2,286.44 rows=47 width=53) (actual time=0.544..71.752 rows=1,548 loops=1)

4. 2.297 70.940 ↓ 137.5 1,513 1

Nested Loop (cost=0.99..2,253.65 rows=11 width=53) (actual time=0.543..70.940 rows=1,513 loops=1)

5. 1.972 67.130 ↓ 137.5 1,513 1

Nested Loop (cost=0.85..2,251.60 rows=11 width=12) (actual time=0.535..67.130 rows=1,513 loops=1)

6. 5.040 60.619 ↓ 137.5 1,513 1

Nested Loop (cost=0.57..2,234.41 rows=11 width=12) (actual time=0.521..60.619 rows=1,513 loops=1)

7. 4.906 50.045 ↓ 131.8 2,767 1

Subquery Scan on tlsos (cost=0.29..2,075.85 rows=21 width=4) (actual time=0.506..50.045 rows=2,767 loops=1)

  • Filter: ((tlsos."tlsosSituacao" = ANY ('{3,9}'::integer[])) AND (tlsos.r = 1))
  • Rows Removed by Filter: 11487
8. 17.059 45.139 ↑ 1.0 14,254 1

WindowAgg (cost=0.29..1,858.97 rows=14,459 width=68) (actual time=0.382..45.139 rows=14,254 loops=1)

9. 28.080 28.080 ↑ 1.0 14,254 1

Index Scan using idx_tlsos_tlsostosdesc on "TabLogSituacaoOrdemServico" (cost=0.29..1,605.93 rows=14,459 width=12) (actual time=0.039..28.080 rows=14,254 loops=1)

  • Filter: ("tlsosDataHora" < (date_trunc('month'::text, (('2019-1-1'::cstring)::date)::timestamp with time zone) + '1 mon -00:00:01'::interval))
  • Rows Removed by Filter: 6844
10. 5.534 5.534 ↑ 1.0 1 2,767

Index Scan using "TabOrdemServico_Unique_tosID_tostemID" on "TabOrdemServico" tos (cost=0.28..7.54 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=2,767)

  • Index Cond: (("tosID" = tlsos."tlsostosID") AND ("tostemID" = 1))
11. 4.539 4.539 ↑ 1.0 1 1,513

Index Scan using "TabPropostaComercial_tpcID" on "TabPropostaComercial" tpc (cost=0.28..1.55 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=1,513)

  • Index Cond: ("tpcID" = tos."tostpcID")
12. 1.513 1.513 ↑ 1.0 1 1,513

Index Scan using "TabAssessoria_tasID" on "TabAssessoria" tas (cost=0.14..0.18 rows=1 width=41) (actual time=0.001..0.001 rows=1 loops=1,513)

  • Index Cond: ("tasID" = tpc."tpctasID")
13. 0.122 0.122 ↑ 1.0 35 1

Seq Scan on "TabAssessoria" (cost=0.00..32.33 rows=36 width=53) (actual time=0.020..0.122 rows=35 loops=1)

  • Filter: ("tastemID" = 1)
  • Rows Removed by Filter: 72
14.          

CTE join_previsto

15. 1.583 291.088 ↓ 1,147.0 1,147 1

GroupAggregate (cost=368.59..368.62 rows=1 width=20) (actual time=288.971..291.088 rows=1,147 loops=1)

  • Group Key: trec."trectosID
16. 6.847 289.505 ↓ 1,182.0 1,182 1

Sort (cost=368.59..368.60 rows=1 width=24) (actual time=288.955..289.505 rows=1,182 loops=1)

  • Sort Key: trec."trectosID
  • Sort Method: quicksort Memory: 141kB
17. 3.594 282.658 ↓ 1,182.0 1,182 1

Nested Loop (cost=1.95..368.58 rows=1 width=24) (actual time=106.022..282.658 rows=1,182 loops=1)

18. 7.043 86.534 ↓ 44.9 2,962 1

Hash Semi Join (cost=1.53..213.55 rows=66 width=8) (actual time=77.920..86.534 rows=2,962 loops=1)

  • Hash Cond: (trec."trectosID" = ungrouped."tosID")
19. 2.752 2.752 ↓ 1.0 8,168 1

Seq Scan on "TabReceita" trec (cost=0.00..190.02 rows=8,102 width=8) (actual time=0.010..2.752 rows=8,168 loops=1)

20. 3.613 76.739 ↓ 32.9 1,548 1

Hash (cost=0.94..0.94 rows=47 width=4) (actual time=76.739..76.739 rows=1,548 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 71kB
21. 73.126 73.126 ↓ 32.9 1,548 1

CTE Scan on ungrouped (cost=0.00..0.94 rows=47 width=4) (actual time=0.003..73.126 rows=1,548 loops=1)

22. 192.530 192.530 ↓ 0.0 0 2,962

Index Scan using idx_trp_trecid_trpsubstituida on "TabReceitaParcela" trp (cost=0.42..2.34 rows=1 width=24) (actual time=0.057..0.065 rows=0 loops=2,962)

  • Index Cond: (("trptrecID" = trec."trecID") AND ("trpSubstituida" = false))
  • Filter: (("trpSubstituida" IS FALSE) AND ("trpBaixada" IS FALSE) AND (NOT "trpIsExcluido") AND (date_part('month'::text, ("trpDataVencimento")::timestamp without time zone) = '1'::double precision) AND (date_part('year'::text, ("trpDataVencimento")::timestamp without time zone) = '2019'::double precision))
  • Rows Removed by Filter: 11
23.          

CTE join_recebido

24. 0.731 75.547 ↓ 1,098.0 1,098 1

GroupAggregate (cost=368.59..368.61 rows=1 width=20) (actual time=73.863..75.547 rows=1,098 loops=1)

  • Group Key: trec_1."trectosID
25. 8.684 74.816 ↓ 1,182.0 1,182 1

Sort (cost=368.59..368.60 rows=1 width=16) (actual time=73.856..74.816 rows=1,182 loops=1)

  • Sort Key: trec_1."trectosID
  • Sort Method: quicksort Memory: 141kB
26. 3.087 66.132 ↓ 1,182.0 1,182 1

Nested Loop (cost=1.95..368.58 rows=1 width=16) (actual time=7.914..66.132 rows=1,182 loops=1)

27. 6.407 12.691 ↓ 44.9 2,962 1

Hash Semi Join (cost=1.53..213.55 rows=66 width=8) (actual time=6.352..12.691 rows=2,962 loops=1)

  • Hash Cond: (trec_1."trectosID" = ungrouped_1."tosID")
28. 2.214 2.214 ↓ 1.0 8,168 1

Seq Scan on "TabReceita" trec_1 (cost=0.00..190.02 rows=8,102 width=8) (actual time=0.009..2.214 rows=8,168 loops=1)

29. 3.548 4.070 ↓ 32.9 1,548 1

Hash (cost=0.94..0.94 rows=47 width=4) (actual time=4.070..4.070 rows=1,548 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 71kB
30. 0.522 0.522 ↓ 32.9 1,548 1

CTE Scan on ungrouped ungrouped_1 (cost=0.00..0.94 rows=47 width=4) (actual time=0.003..0.522 rows=1,548 loops=1)

31. 50.354 50.354 ↓ 0.0 0 2,962

Index Scan using idx_trp_trecid_trpsubstituida on "TabReceitaParcela" trp_1 (cost=0.42..2.34 rows=1 width=16) (actual time=0.015..0.017 rows=0 loops=2,962)

  • Index Cond: (("trptrecID" = trec_1."trecID") AND ("trpSubstituida" = false))
  • Filter: (("trpSubstituida" IS FALSE) AND ("trpBaixada" IS FALSE) AND (NOT "trpIsExcluido") AND (date_part('month'::text, COALESCE(("trpDataCompensacao")::timestamp with time zone, "trpDataRecebimento")) = '1'::double precision) AND (date_part('year'::text, COALESCE(("trpDataCompensacao")::timestamp with time zone, "trpDataRecebimento")) = '2019'::double precision))
  • Rows Removed by Filter: 11
32.          

CTE grouped

33. 2.569 1,059.562 ↑ 1.3 35 1

GroupAggregate (cost=2.50..4.38 rows=47 width=132) (actual time=1,056.798..1,059.562 rows=35 loops=1)

  • Group Key: ug."tasID", ug."tasDescricao", ug.mes, ug."tostemID
34. 5.559 1,056.993 ↓ 32.9 1,548 1

Sort (cost=2.50..2.61 rows=47 width=80) (actual time=1,056.598..1,056.993 rows=1,548 loops=1)

  • Sort Key: ug."tasID", ug."tasDescricao", ug.mes, ug."tostemID
  • Sort Method: quicksort Memory: 262kB
35. 2.889 1,051.434 ↓ 32.9 1,548 1

Hash Left Join (cost=0.07..1.19 rows=47 width=80) (actual time=1,050.021..1,051.434 rows=1,548 loops=1)

  • Hash Cond: (ug."tosID" = p."tosID")
36. 1.033 1.033 ↓ 32.9 1,548 1

CTE Scan on ungrouped ug (cost=0.00..0.94 rows=47 width=48) (actual time=0.546..1.033 rows=1,548 loops=1)

37. 6.629 1,047.512 ↓ 1,147.0 1,147 1

Hash (cost=0.05..0.05 rows=1 width=36) (actual time=1,047.512..1,047.512 rows=1,147 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 96kB
38. 334.868 1,040.883 ↓ 1,147.0 1,147 1

Nested Loop Left Join (cost=0.00..0.05 rows=1 width=36) (actual time=365.858..1,040.883 rows=1,147 loops=1)

  • Join Filter: (r."tosID" = p."tosID")
  • Rows Removed by Join Filter: 1258328
39. 291.948 291.948 ↓ 1,147.0 1,147 1

CTE Scan on join_previsto p (cost=0.00..0.02 rows=1 width=20) (actual time=288.975..291.948 rows=1,147 loops=1)

40. 414.067 414.067 ↓ 1,098.0 1,098 1,147

CTE Scan on join_recebido r (cost=0.00..0.02 rows=1 width=20) (actual time=0.065..0.361 rows=1,098 loops=1,147)

41. 0.097 1,060.243 ↑ 1.3 35 1

HashAggregate (cost=2.23..4.00 rows=47 width=92) (actual time=1,060.217..1,060.243 rows=35 loops=1)

  • Group Key: t."tasID", t."tasDescricao", t.mes, t."tostemID
42. 1,060.146 1,060.146 ↑ 1.3 35 1

CTE Scan on grouped t (cost=0.00..0.94 rows=47 width=132) (actual time=1,056.801..1,060.146 rows=35 loops=1)

Planning time : 31.823 ms
Execution time : 1,074.034 ms