explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mPGj : Optimization for: plan #CI54

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 8.711 1,065.864 ↑ 1.3 35 1

Sort (cost=2,695.85..2,695.97 rows=47 width=92) (actual time=1,065.857..1,065.864 rows=35 loops=1)

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

CTE ungrouped

3. 0.687 96.847 ↓ 32.9 1,548 1

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

4. 2.293 95.018 ↓ 137.5 1,513 1

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

5. 2.409 83.647 ↓ 137.5 1,513 1

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

6. 1.664 78.212 ↓ 137.5 1,513 1

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

7. 7.669 71.014 ↓ 131.8 2,767 1

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

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

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

9. 42.592 42.592 ↑ 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.910..42.592 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: 6848
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. 3.026 3.026 ↑ 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.002 rows=1 loops=1,513)

  • Index Cond: ("tpcID" = tos."tostpcID")
12. 9.078 9.078 ↑ 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.006..0.006 rows=1 loops=1,513)

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

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

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

CTE parcelas

15. 17.034 171.075 ↓ 61.8 31,532 1

Nested Loop (cost=1.95..363.96 rows=510 width=48) (actual time=109.244..171.075 rows=31,532 loops=1)

16. 16.155 115.535 ↓ 44.9 2,962 1

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

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

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

18. 4.802 97.204 ↓ 32.9 1,548 1

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

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

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

20. 38.506 38.506 ↓ 1.2 11 2,962

Index Scan using idx_trp_trecid_trpsubstituida on "TabReceitaParcela" trp (cost=0.42..2.19 rows=9 width=48) (actual time=0.003..0.013 rows=11 loops=2,962)

  • Index Cond: (("trptrecID" = trec."trecID") AND ("trpSubstituida" = false))
  • Filter: (("trpSubstituida" IS FALSE) AND ("trpBaixada" IS FALSE) AND (NOT "trpIsExcluido"))
  • Rows Removed by Filter: 1
21.          

CTE join_previsto

22. 1.190 307.149 ↓ 1,147.0 1,147 1

GroupAggregate (cost=17.86..17.89 rows=1 width=20) (actual time=305.555..307.149 rows=1,147 loops=1)

  • Group Key: trp_1."tosID
23. 11.698 305.959 ↓ 1,182.0 1,182 1

Sort (cost=17.86..17.87 rows=1 width=24) (actual time=305.540..305.959 rows=1,182 loops=1)

  • Sort Key: trp_1."tosID
  • Sort Method: quicksort Memory: 141kB
24. 294.261 294.261 ↓ 1,182.0 1,182 1

CTE Scan on parcelas trp_1 (cost=0.00..17.85 rows=1 width=24) (actual time=112.894..294.261 rows=1,182 loops=1)

  • Filter: ((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: 30350
25.          

CTE join_recebido

26. 0.744 14.269 ↓ 1,098.0 1,098 1

GroupAggregate (cost=17.86..17.88 rows=1 width=20) (actual time=13.244..14.269 rows=1,098 loops=1)

  • Group Key: trp_2."tosID
27. 2.106 13.525 ↓ 1,182.0 1,182 1

Sort (cost=17.86..17.87 rows=1 width=16) (actual time=13.241..13.525 rows=1,182 loops=1)

  • Sort Key: trp_2."tosID
  • Sort Method: quicksort Memory: 141kB
28. 11.419 11.419 ↓ 1,182.0 1,182 1

CTE Scan on parcelas trp_2 (cost=0.00..17.85 rows=1 width=16) (actual time=0.309..11.419 rows=1,182 loops=1)

  • Filter: ((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: 30350
29.          

CTE grouped

30. 1.030 1,057.030 ↑ 1.3 35 1

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

  • Group Key: ug."tasID", ug."tasDescricao", ug.mes, ug."tostemID
31. 9.685 1,056.000 ↓ 32.9 1,548 1

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

  • Sort Key: ug."tasID", ug."tasDescricao", ug.mes, ug."tostemID
  • Sort Method: quicksort Memory: 262kB
32. 9.434 1,046.315 ↓ 32.9 1,548 1

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

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

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

34. 0.698 1,029.533 ↓ 1,147.0 1,147 1

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

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 96kB
35. 318.349 1,028.835 ↓ 1,147.0 1,147 1

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

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

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

37. 402.597 402.597 ↓ 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.012..0.351 rows=1,098 loops=1,147)

38. 0.095 1,057.153 ↑ 1.3 35 1

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

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

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

Planning time : 14.932 ms
Execution time : 1,087.433 ms