explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CI54

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.089 978.425 ↑ 1.3 35 1

GroupAggregate (cost=62,513.80..62,516.98 rows=47 width=92) (actual time=978.341..978.425 rows=35 loops=1)

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

Sort (cost=62,513.80..62,513.92 rows=47 width=84) (actual time=978.320..978.336 rows=60 loops=1)

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

Subquery Scan on t (cost=2,249.84..62,512.50 rows=47 width=84) (actual time=164.386..978.210 rows=60 loops=1)

4. 0.051 978.165 ↓ 1.3 60 1

Append (cost=2,249.84..62,512.03 rows=47 width=84) (actual time=164.384..978.165 rows=60 loops=1)

5. 1.379 978.012 ↓ 2.3 25 1

GroupAggregate (cost=2,249.84..62,478.78 rows=11 width=89) (actual time=164.384..978.012 rows=25 loops=1)

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

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

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

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

8. 6.355 80.978 ↓ 137.5 1,513 1

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

9. 3.105 70.084 ↓ 137.5 1,513 1

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

10. 4.598 50.377 ↓ 131.8 2,767 1

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

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

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

12. 34.053 34.053 ↑ 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.018..34.053 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. 16.602 16.602 ↑ 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.006..0.006 rows=1 loops=2,767)

  • Index Cond: (("tosID" = tlsos."tlsostosID") AND ("tostemID" = 1))
14. 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.003..0.003 rows=1 loops=1,513)

  • Index Cond: ("tpcID" = tos."tostpcID")
15. 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")
16.          

SubPlan (for GroupAggregate)

17. 0.500 139.600 ↑ 1.0 1 25

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

18. 4.320 139.100 ↓ 47.0 47 25

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

19. 90.350 90.350 ↓ 8.4 118 25

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

  • Filter: ("trectosID" = ANY (array_agg(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.400 315.550 ↑ 1.0 1 25

Aggregate (cost=1,844.66..1,844.67 rows=1 width=8) (actual time=12.622..12.622 rows=1 loops=25)

23. 3.104 315.150 ↓ 48.0 48 25

Nested Loop (cost=1,398.22..1,844.65 rows=1 width=8) (actual time=8.779..12.606 rows=48 loops=25)

24. 58.775 261.875 ↓ 1.9 182 25

Hash Join (cost=1,397.80..1,619.15 rows=96 width=4) (actual time=6.787..10.475 rows=182 loops=25)

  • Hash Cond: (trec_1."trectosID" = tos_1."tosID")
25. 46.900 46.900 ↓ 1.0 8,166 25

Seq Scan on "TabReceita" trec_1 (cost=0.00..190.02 rows=8,102 width=8) (actual time=0.003..1.876 rows=8,166 loops=25)

26. 1.100 156.200 ↓ 1.7 125 25

Hash (cost=1,396.90..1,396.90 rows=72 width=4) (actual time=6.248..6.248 rows=125 loops=25)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 45.300 155.100 ↓ 1.7 125 25

Hash Join (cost=1,056.83..1,396.90 rows=72 width=4) (actual time=3.106..6.204 rows=125 loops=25)

  • Hash Cond: (tos_1."tostpcID" = tpc_1."tpcID")
28. 45.175 45.175 ↓ 1.0 6,164 25

Seq Scan on "TabOrdemServico" tos_1 (cost=0.00..316.35 rows=6,135 width=8) (actual time=0.004..1.807 rows=6,164 loops=25)

29. 5.725 64.625 ↓ 2.1 201 25

Hash (cost=1,055.62..1,055.62 rows=96 width=4) (actual time=2.585..2.585 rows=201 loops=25)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
30. 58.900 58.900 ↓ 2.1 201 25

Seq Scan on "TabPropostaComercial" tpc_1 (cost=0.00..1,055.62 rows=96 width=4) (actual time=0.269..2.356 rows=201 loops=25)

  • Filter: ("tpctasID" = tas."tasID")
  • Rows Removed by Filter: 7981
31. 50.171 50.171 ↓ 0.0 0 4,561

Index Scan using idx_trp_trecid_trpsubstituida on "TabReceitaParcela" trp_1 (cost=0.42..2.34 rows=1 width=12) (actual time=0.009..0.011 rows=0 loops=4,561)

  • 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: 10
32. 0.425 305.200 ↑ 1.0 1 25

Aggregate (cost=1,844.66..1,844.67 rows=1 width=8) (actual time=12.208..12.208 rows=1 loops=25)

33. 3.279 304.775 ↓ 48.0 48 25

Nested Loop (cost=1,398.22..1,844.65 rows=1 width=4) (actual time=8.344..12.191 rows=48 loops=25)

34. 59.125 251.325 ↓ 1.9 182 25

Hash Join (cost=1,397.80..1,619.15 rows=96 width=4) (actual time=6.334..10.053 rows=182 loops=25)

  • Hash Cond: (trec_2."trectosID" = tos_2."tosID")
35. 47.425 47.425 ↓ 1.0 8,166 25

Seq Scan on "TabReceita" trec_2 (cost=0.00..190.02 rows=8,102 width=8) (actual time=0.004..1.897 rows=8,166 loops=25)

36. 1.075 144.775 ↓ 1.7 125 25

Hash (cost=1,396.90..1,396.90 rows=72 width=4) (actual time=5.791..5.791 rows=125 loops=25)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 44.325 143.700 ↓ 1.7 125 25

Hash Join (cost=1,056.83..1,396.90 rows=72 width=4) (actual time=2.816..5.748 rows=125 loops=25)

  • Hash Cond: (tos_2."tostpcID" = tpc_2."tpcID")
38. 41.650 41.650 ↓ 1.0 6,164 25

Seq Scan on "TabOrdemServico" tos_2 (cost=0.00..316.35 rows=6,135 width=8) (actual time=0.004..1.666 rows=6,164 loops=25)

39. 1.725 57.725 ↓ 2.1 201 25

Hash (cost=1,055.62..1,055.62 rows=96 width=4) (actual time=2.309..2.309 rows=201 loops=25)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
40. 56.000 56.000 ↓ 2.1 201 25

Seq Scan on "TabPropostaComercial" tpc_2 (cost=0.00..1,055.62 rows=96 width=4) (actual time=0.250..2.240 rows=201 loops=25)

  • Filter: ("tpctasID" = tas."tasID")
  • Rows Removed by Filter: 7981
41. 50.171 50.171 ↓ 0.0 0 4,561

Index Scan using idx_trp_trecid_trpsubstituida on "TabReceitaParcela" trp_2 (cost=0.42..2.34 rows=1 width=8) (actual time=0.009..0.011 rows=0 loops=4,561)

  • Index Cond: (("trptrecID" = trec_2."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: 10
42. 0.425 130.675 ↑ 1.0 1 25

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

43. 2.795 130.250 ↓ 47.0 47 25

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

44. 83.025 83.025 ↓ 8.4 118 25

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

  • Filter: ("trectosID" = ANY (array_agg(tos."tosID")))
  • Rows Removed by Filter: 8048
45. 35.544 44.430 ↓ 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.015 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
46. 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))
47. 0.025 0.102 ↑ 1.0 35 1

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

48. 0.077 0.077 ↑ 1.0 35 1

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

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