explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.045 604.330 ↑ 1.3 35 1

Sort (cost=3,033.54..3,033.66 rows=47 width=92) (actual time=604.323..604.330 rows=35 loops=1)

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

CTE ungrouped

3. 0.656 268.960 ↓ 32.9 1,548 1

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

4. 5.024 268.182 ↓ 137.5 1,513 1

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

5. 2.434 151.196 ↓ 137.5 1,513 1

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

6. 11.491 145.736 ↓ 137.5 1,513 1

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

7. 4.955 103.808 ↓ 131.8 2,767 1

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

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

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

9. 79.441 79.441 ↑ 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.032..79.441 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. 30.437 30.437 ↑ 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.011..0.011 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. 111.962 111.962 ↑ 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.074..0.074 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.015..0.122 rows=35 loops=1)

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

CTE join_previsto

15. 0.875 430.493 ↓ 1,147.0 1,147 1

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

  • Group Key: trec."trectosID
16. 0.975 429.618 ↓ 1,182.0 1,182 1

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

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

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

18. 20.188 310.241 ↓ 44.9 2,962 1

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

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

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

20. 0.680 279.457 ↓ 32.9 1,548 1

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

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

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

22. 115.518 115.518 ↓ 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.030..0.039 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.761 158.344 ↓ 1,098.0 1,098 1

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

  • Group Key: trec_1."trectosID
25. 2.202 157.583 ↓ 1,182.0 1,182 1

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

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

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

27. 26.113 33.807 ↓ 44.9 2,962 1

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

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

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

29. 0.440 5.530 ↓ 32.9 1,548 1

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

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

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

31. 100.708 100.708 ↓ 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.026..0.034 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. 0.985 604.164 ↑ 1.3 35 1

GroupAggregate (cost=2.68..4.56 rows=47 width=132) (actual time=598.994..604.164 rows=35 loops=1)

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

Sort (cost=2.68..2.80 rows=47 width=80) (actual time=598.800..603.179 rows=1,548 loops=1)

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

Hash Left Join (cost=0.07..1.38 rows=47 width=80) (actual time=590.891..597.964 rows=1,548 loops=1)

  • Hash Cond: (ug."tosID" = r."tosID")
36. 3.574 435.619 ↓ 32.9 1,548 1

Hash Left Join (cost=0.03..1.16 rows=47 width=64) (actual time=431.576..435.619 rows=1,548 loops=1)

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

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

38. 0.327 431.466 ↓ 1,147.0 1,147 1

Hash (cost=0.02..0.02 rows=1 width=20) (actual time=431.466..431.466 rows=1,147 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 75kB
39. 431.139 431.139 ↓ 1,147.0 1,147 1

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

40. 0.346 159.305 ↓ 1,098.0 1,098 1

Hash (cost=0.02..0.02 rows=1 width=20) (actual time=159.304..159.305 rows=1,098 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 72kB
41. 158.959 158.959 ↓ 1,098.0 1,098 1

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

42. 0.090 604.285 ↑ 1.3 35 1

HashAggregate (cost=2.23..4.00 rows=47 width=92) (actual time=604.261..604.285 rows=35 loops=1)

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

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

Planning time : 4.105 ms
Execution time : 607.683 ms