explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nH4h : Optimization for: plan #CI54

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.095 2,340.045 ↑ 1.3 35 1

GroupAggregate (cost=56,142.86..56,146.03 rows=47 width=92) (actual time=2,339.963..2,340.045 rows=35 loops=1)

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

Sort (cost=56,142.86..56,142.98 rows=47 width=84) (actual time=2,339.938..2,339.950 rows=60 loops=1)

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

Subquery Scan on t (cost=2,249.84..56,141.55 rows=47 width=84) (actual time=847.134..2,339.827 rows=60 loops=1)

4. 0.044 2,339.783 ↓ 1.3 60 1

Append (cost=2,249.84..56,141.08 rows=47 width=84) (actual time=847.133..2,339.783 rows=60 loops=1)

5. 1.672 2,336.077 ↓ 2.3 25 1

GroupAggregate (cost=2,249.84..56,107.84 rows=11 width=89) (actual time=847.133..2,336.077 rows=25 loops=1)

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

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

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

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

8. 2.328 57.946 ↓ 137.5 1,513 1

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

9. 1.276 52.592 ↓ 137.5 1,513 1

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

10. 4.612 45.782 ↓ 131.8 2,767 1

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

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

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

12. 29.227 29.227 ↑ 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.024..29.227 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: 6837
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.001..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. 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. 1.400 939.175 ↑ 1.0 1 25

Aggregate (cost=313.81..313.82 rows=1 width=8) (actual time=37.567..37.567 rows=1 loops=25)

18. 3.738 937.775 ↓ 47.0 47 25

Nested Loop (cost=63.15..313.81 rows=1 width=16) (actual time=7.923..37.511 rows=47 loops=25)

19. 62.100 116.525 ↓ 9.1 118 25

Hash Join (cost=62.74..283.27 rows=13 width=4) (actual time=1.213..4.661 rows=118 loops=25)

  • Hash Cond: (trec."trectosID" = tos_."tosID")
20. 49.750 49.750 ↓ 1.0 8,168 25

Seq Scan on "TabReceita" trec (cost=0.00..190.02 rows=8,102 width=8) (actual time=0.002..1.990 rows=8,168 loops=25)

21. 2.225 4.675 ↓ 6.1 61 25

Hash (cost=62.61..62.61 rows=10 width=4) (actual time=0.187..0.187 rows=61 loops=25)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 2.450 2.450 ↓ 6.1 61 25

Index Only Scan using "TabOrdemServico_tosID" on "TabOrdemServico" tos_ (cost=0.28..62.61 rows=10 width=4) (actual time=0.007..0.098 rows=61 loops=25)

  • Index Cond: ("tosID" = ANY (array_agg(DISTINCT tos."tosID")))
  • Heap Fetches: 1339
23. 817.512 817.512 ↓ 0.0 0 2,962

Index Scan using idx_trp_trecid_trpsubstituida on "TabReceitaParcela" trp (cost=0.42..2.34 rows=1 width=20) (actual time=0.238..0.276 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
24. 0.500 808.125 ↑ 1.0 1 25

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

25. 4.997 807.625 ↓ 48.0 48 25

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

26. 69.550 433.025 ↓ 1.9 183 25

Hash Join (cost=1,397.80..1,619.15 rows=96 width=4) (actual time=7.778..17.321 rows=183 loops=25)

  • Hash Cond: (trec_1."trectosID" = tos_1."tosID")
27. 185.725 185.725 ↓ 1.0 8,168 25

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

28. 1.775 177.750 ↓ 1.7 125 25

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
29. 49.700 175.975 ↓ 1.7 125 25

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

  • Hash Cond: (tos_1."tostpcID" = tpc_1."tpcID")
30. 52.925 52.925 ↓ 1.0 6,166 25

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

31. 4.750 73.350 ↓ 2.1 201 25

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 68.600 68.600 ↓ 2.1 201 25

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

  • Filter: ("tpctasID" = tas."tasID")
  • Rows Removed by Filter: 7984
33. 369.603 369.603 ↓ 0.0 0 4,563

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

  • 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
34. 0.500 379.400 ↑ 1.0 1 25

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

35. 4.942 378.900 ↓ 48.0 48 25

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

36. 63.900 300.950 ↓ 1.9 183 25

Hash Join (cost=1,397.80..1,619.15 rows=96 width=4) (actual time=7.235..12.038 rows=183 loops=25)

  • Hash Cond: (trec_2."trectosID" = tos_2."tosID")
37. 71.475 71.475 ↓ 1.0 8,168 25

Seq Scan on "TabReceita" trec_2 (cost=0.00..190.02 rows=8,102 width=8) (actual time=0.003..2.859 rows=8,168 loops=25)

38. 1.100 165.575 ↓ 1.7 125 25

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
39. 48.250 164.475 ↓ 1.7 125 25

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

  • Hash Cond: (tos_2."tostpcID" = tpc_2."tpcID")
40. 52.425 52.425 ↓ 1.0 6,166 25

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

41. 1.750 63.800 ↓ 2.1 201 25

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
42. 62.050 62.050 ↓ 2.1 201 25

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

  • Filter: ("tpctasID" = tas."tasID")
  • Rows Removed by Filter: 7984
43. 73.008 73.008 ↓ 0.0 0 4,563

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

  • 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
44. 0.650 142.425 ↑ 1.0 1 25

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

45. 4.708 141.775 ↓ 47.0 47 25

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

46. 89.675 89.675 ↓ 8.4 118 25

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

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

Bitmap Heap Scan on "TabReceitaParcela" trp_3 (cost=4.52..42.97 rows=1 width=8) (actual time=0.014..0.016 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=22344
48. 11.848 11.848 ↓ 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.004..0.004 rows=11 loops=2,962)

  • Index Cond: (("trptrecID" = trec_3."trecID") AND ("trpSubstituida" = false))
49. 0.031 3.662 ↑ 1.0 35 1

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

50. 3.631 3.631 ↑ 1.0 35 1

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

  • Filter: ("tastemID" = 1)
  • Rows Removed by Filter: 72
Planning time : 8.935 ms
Execution time : 2,346.713 ms