explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tUI3 : Optimization for: Optimization for: Optimization for: plan #CI54; plan #Pkg6; plan #xoV0

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.088 594.262 ↑ 1.3 35 1

GroupAggregate (cost=41,577.14..41,580.31 rows=47 width=92) (actual time=594.176..594.262 rows=35 loops=1)

  • Group Key: t."tasID", t."tasDescricao", t.mes, t."tostemID
2. 0.091 594.174 ↓ 1.3 60 1

Sort (cost=41,577.14..41,577.25 rows=47 width=84) (actual time=594.156..594.174 rows=60 loops=1)

  • Sort Key: t."tasID", t."tasDescricao", t.mes, t."tostemID
  • Sort Method: quicksort Memory: 33kB
3. 0.040 594.083 ↓ 1.3 60 1

Subquery Scan on t (cost=2,249.84..41,575.83 rows=47 width=84) (actual time=119.911..594.083 rows=60 loops=1)

4. 0.032 594.043 ↓ 1.3 60 1

Append (cost=2,249.84..41,575.36 rows=47 width=84) (actual time=119.909..594.043 rows=60 loops=1)

5. 1.324 593.906 ↓ 2.3 25 1

GroupAggregate (cost=2,249.84..41,542.12 rows=11 width=89) (actual time=119.909..593.906 rows=25 loops=1)

  • Group Key: tas."tasID", tos."tostemID
6. 1.124 66.582 ↓ 137.5 1,513 1

Sort (cost=2,249.84..2,249.87 rows=11 width=49) (actual time=66.155..66.582 rows=1,513 loops=1)

  • Sort Key: tas."tasID
  • Sort Method: quicksort Memory: 189kB
7. 1.238 65.458 ↓ 137.5 1,513 1

Nested Loop (cost=0.99..2,249.65 rows=11 width=49) (actual time=0.060..65.458 rows=1,513 loops=1)

8. 1.822 61.194 ↓ 137.5 1,513 1

Nested Loop (cost=0.85..2,247.60 rows=11 width=12) (actual time=0.057..61.194 rows=1,513 loops=1)

9. 3.331 56.346 ↓ 137.5 1,513 1

Nested Loop (cost=0.57..2,230.41 rows=11 width=12) (actual time=0.053..56.346 rows=1,513 loops=1)

10. 5.419 47.481 ↓ 131.8 2,767 1

Subquery Scan on tlsos (cost=0.29..2,071.85 rows=21 width=4) (actual time=0.047..47.481 rows=2,767 loops=1)

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

WindowAgg (cost=0.29..1,854.97 rows=14,459 width=68) (actual time=0.024..42.062 rows=14,254 loops=1)

12. 29.353 29.353 ↑ 1.0 14,254 1

Index Scan using idx_tlsos_tlsostosdesc on "TabLogSituacaoOrdemServico" (cost=0.29..1,601.93 rows=14,459 width=12) (actual time=0.019..29.353 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: 6826
13. 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))
14. 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")
15. 3.026 3.026 ↑ 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.002 rows=1 loops=1,513)

  • Index Cond: ("tasID" = tpc."tpctasID")
16.          

SubPlan (for GroupAggregate)

17. 0.450 130.450 ↑ 1.0 1 25

Aggregate (cost=892.99..893.00 rows=1 width=8) (actual time=5.218..5.218 rows=1 loops=25)

18. 3.345 130.000 ↓ 47.0 47 25

Nested Loop (cost=4.52..892.98 rows=1 width=16) (actual time=0.804..5.200 rows=47 loops=25)

19. 82.225 82.225 ↓ 8.4 118 25

Seq Scan on "TabReceita" trec (cost=0.00..291.29 rows=14 width=4) (actual time=0.244..3.289 rows=118 loops=25)

  • Filter: ("trectosID" = ANY (array_agg(DISTINCT tos."tosID")))
  • Rows Removed by Filter: 8048
20. 35.544 44.430 ↓ 0.0 0 2,962

Bitmap Heap Scan on "TabReceitaParcela" trp (cost=4.52..42.97 rows=1 width=20) (actual time=0.013..0.015 rows=0 loops=2,962)

  • Recheck Cond: ("trptrecID" = trec."trecID")
  • 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
  • Heap Blocks: exact=22340
21. 8.886 8.886 ↓ 1.1 11 2,962

Bitmap Index Scan on idx_trp_trecid_trpsubstituida (cost=0.00..4.52 rows=10 width=0) (actual time=0.003..0.003 rows=11 loops=2,962)

  • Index Cond: (("trptrecID" = trec."trecID") AND ("trpSubstituida" = false))
22. 0.425 131.950 ↑ 1.0 1 25

Aggregate (cost=892.99..893.00 rows=1 width=8) (actual time=5.277..5.278 rows=1 loops=25)

23. 2.308 131.525 ↓ 47.0 47 25

Nested Loop (cost=4.52..892.98 rows=1 width=8) (actual time=0.786..5.261 rows=47 loops=25)

24. 81.825 81.825 ↓ 8.4 118 25

Seq Scan on "TabReceita" trec_1 (cost=0.00..291.29 rows=14 width=4) (actual time=0.248..3.273 rows=118 loops=25)

  • Filter: ("trectosID" = ANY (array_agg(DISTINCT tos."tosID")))
  • Rows Removed by Filter: 8048
25. 38.506 47.392 ↓ 0.0 0 2,962

Bitmap Heap Scan on "TabReceitaParcela" trp_1 (cost=4.52..42.97 rows=1 width=12) (actual time=0.014..0.016 rows=0 loops=2,962)

  • Recheck Cond: ("trptrecID" = trec_1."trecID")
  • 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
  • Heap Blocks: exact=22340
26. 8.886 8.886 ↓ 1.1 11 2,962

Bitmap Index Scan on idx_trp_trecid_trpsubstituida (cost=0.00..4.52 rows=10 width=0) (actual time=0.003..0.003 rows=11 loops=2,962)

  • Index Cond: (("trptrecID" = trec_1."trecID") AND ("trpSubstituida" = false))
27. 0.425 135.225 ↑ 1.0 1 25

Aggregate (cost=892.99..893.00 rows=1 width=8) (actual time=5.409..5.409 rows=1 loops=25)

28. 3.608 134.800 ↓ 47.0 47 25

Nested Loop (cost=4.52..892.98 rows=1 width=4) (actual time=0.785..5.392 rows=47 loops=25)

29. 83.800 83.800 ↓ 8.4 118 25

Seq Scan on "TabReceita" trec_2 (cost=0.00..291.29 rows=14 width=4) (actual time=0.244..3.352 rows=118 loops=25)

  • Filter: ("trectosID" = ANY (array_agg(DISTINCT tos."tosID")))
  • Rows Removed by Filter: 8048
30. 38.506 47.392 ↓ 0.0 0 2,962

Bitmap Heap Scan on "TabReceitaParcela" trp_2 (cost=4.52..42.97 rows=1 width=8) (actual time=0.014..0.016 rows=0 loops=2,962)

  • Recheck Cond: ("trptrecID" = trec_2."trecID")
  • 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
  • Heap Blocks: exact=22340
31. 8.886 8.886 ↓ 1.1 11 2,962

Bitmap Index Scan on idx_trp_trecid_trpsubstituida (cost=0.00..4.52 rows=10 width=0) (actual time=0.003..0.003 rows=11 loops=2,962)

  • Index Cond: (("trptrecID" = trec_2."trecID") AND ("trpSubstituida" = false))
32. 0.425 128.375 ↑ 1.0 1 25

Aggregate (cost=892.99..893.00 rows=1 width=8) (actual time=5.135..5.135 rows=1 loops=25)

33. 4.232 127.950 ↓ 47.0 47 25

Nested Loop (cost=4.52..892.98 rows=1 width=4) (actual time=0.777..5.118 rows=47 loops=25)

34. 82.250 82.250 ↓ 8.4 118 25

Seq Scan on "TabReceita" trec_3 (cost=0.00..291.29 rows=14 width=4) (actual time=0.243..3.290 rows=118 loops=25)

  • Filter: ("trectosID" = ANY (array_agg(DISTINCT tos."tosID")))
  • Rows Removed by Filter: 8048
35. 32.582 41.468 ↓ 0.0 0 2,962

Bitmap Heap Scan on "TabReceitaParcela" trp_3 (cost=4.52..42.97 rows=1 width=8) (actual time=0.013..0.014 rows=0 loops=2,962)

  • Recheck Cond: ("trptrecID" = trec_3."trecID")
  • 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
  • Heap Blocks: exact=22340
36. 8.886 8.886 ↓ 1.1 11 2,962

Bitmap Index Scan on idx_trp_trecid_trpsubstituida (cost=0.00..4.52 rows=10 width=0) (actual time=0.003..0.003 rows=11 loops=2,962)

  • Index Cond: (("trptrecID" = trec_3."trecID") AND ("trpSubstituida" = false))
37. 0.027 0.105 ↑ 1.0 35 1

Subquery Scan on *SELECT* 2 (cost=0.00..33.14 rows=36 width=89) (actual time=0.015..0.105 rows=35 loops=1)

38. 0.078 0.078 ↑ 1.0 35 1

Seq Scan on "TabAssessoria" (cost=0.00..32.33 rows=36 width=69) (actual time=0.008..0.078 rows=35 loops=1)

  • Filter: ("tastemID" = 1)
  • Rows Removed by Filter: 72
Planning time : 2.620 ms
Execution time : 594.524 ms