explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Pkg6 : Optimization for: plan #CI54

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.091 2,625.481 ↑ 1.3 35 1

GroupAggregate (cost=83,450.47..83,453.64 rows=47 width=92) (actual time=2,625.397..2,625.481 rows=35 loops=1)

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

Sort (cost=83,450.47..83,450.59 rows=47 width=84) (actual time=2,625.372..2,625.390 rows=60 loops=1)

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

Subquery Scan on t (cost=2,249.84..83,449.17 rows=47 width=84) (actual time=766.623..2,625.218 rows=60 loops=1)

4. 0.046 2,625.167 ↓ 1.3 60 1

Append (cost=2,249.84..83,448.70 rows=47 width=84) (actual time=766.622..2,625.167 rows=60 loops=1)

5. 1.359 2,624.908 ↓ 2.3 25 1

GroupAggregate (cost=2,249.84..83,415.45 rows=11 width=89) (actual time=766.621..2,624.908 rows=25 loops=1)

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

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

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

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

8. 1.961 62.840 ↓ 137.5 1,513 1

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

9. 1.408 50.288 ↓ 137.5 1,513 1

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

10. 4.537 43.346 ↓ 131.8 2,767 1

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

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

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

12. 26.989 26.989 ↑ 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.027..26.989 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. 10.591 10.591 ↑ 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.006..0.007 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.675 1,609.725 ↑ 1.0 1 25

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

18. 2.266 1,609.050 ↓ 48.0 48 25

Nested Loop (cost=1,398.22..1,844.65 rows=1 width=16) (actual time=21.679..64.362 rows=48 loops=25)

19. 64.375 265.850 ↓ 1.9 182 25

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

  • Hash Cond: (trec."trectosID" = tos_1."tosID")
20. 48.750 48.750 ↓ 1.0 8,166 25

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

21. 1.150 152.725 ↓ 1.7 125 25

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 44.500 151.575 ↓ 1.7 125 25

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

  • Hash Cond: (tos_1."tostpcID" = tpc_1."tpcID")
23. 42.750 42.750 ↓ 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.710 rows=6,164 loops=25)

24. 1.700 64.325 ↓ 2.1 201 25

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

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

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

  • Filter: ("tpctasID" = tas."tasID")
  • Rows Removed by Filter: 7983
26. 1,340.934 1,340.934 ↓ 0.0 0 4,561

Index Scan using idx_trp_trecid_trpsubstituida on "TabReceitaParcela" trp (cost=0.42..2.34 rows=1 width=20) (actual time=0.277..0.294 rows=0 loops=4,561)

  • 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: 10
27. 0.450 317.650 ↑ 1.0 1 25

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

28. 4.054 317.200 ↓ 48.0 48 25

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

29. 59.900 262.975 ↓ 1.9 182 25

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

  • Hash Cond: (trec_1."trectosID" = tos_2."tosID")
30. 50.750 50.750 ↓ 1.0 8,166 25

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

31. 1.075 152.325 ↓ 1.7 125 25

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 46.800 151.250 ↓ 1.7 125 25

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

  • Hash Cond: (tos_2."tostpcID" = tpc_2."tpcID")
33. 43.050 43.050 ↓ 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.722 rows=6,164 loops=25)

34. 1.775 61.400 ↓ 2.1 201 25

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
35. 59.625 59.625 ↓ 2.1 201 25

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

  • Filter: ("tpctasID" = tas."tasID")
  • Rows Removed by Filter: 7983
36. 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.010..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
37. 0.450 320.825 ↑ 1.0 1 25

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

38. 3.468 320.375 ↓ 48.0 48 25

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

39. 59.975 262.175 ↓ 1.9 182 25

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

  • Hash Cond: (trec_2."trectosID" = tos_3."tosID")
40. 48.025 48.025 ↓ 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.921 rows=8,166 loops=25)

41. 1.125 154.175 ↓ 1.7 125 25

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
42. 44.550 153.050 ↓ 1.7 125 25

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

  • Hash Cond: (tos_3."tostpcID" = tpc_3."tpcID")
43. 43.750 43.750 ↓ 1.0 6,164 25

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

44. 1.750 64.750 ↓ 2.1 201 25

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
45. 63.000 63.000 ↓ 2.1 201 25

Seq Scan on "TabPropostaComercial" tpc_3 (cost=0.00..1,055.62 rows=96 width=4) (actual time=0.282..2.520 rows=201 loops=25)

  • Filter: ("tpctasID" = tas."tasID")
  • Rows Removed by Filter: 7983
46. 54.732 54.732 ↓ 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.011..0.012 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
47. 0.425 307.675 ↑ 1.0 1 25

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

48. 3.765 307.250 ↓ 48.0 48 25

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

49. 60.500 257.875 ↓ 1.9 182 25

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

  • Hash Cond: (trec_3."trectosID" = tos_4."tosID")
50. 47.550 47.550 ↓ 1.0 8,166 25

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

51. 1.125 149.825 ↓ 1.7 125 25

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
52. 44.850 148.700 ↓ 1.7 125 25

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

  • Hash Cond: (tos_4."tostpcID" = tpc_4."tpcID")
53. 42.700 42.700 ↓ 1.0 6,164 25

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

54. 1.725 61.150 ↓ 2.1 201 25

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
55. 59.425 59.425 ↓ 2.1 201 25

Seq Scan on "TabPropostaComercial" tpc_4 (cost=0.00..1,055.62 rows=96 width=4) (actual time=0.273..2.377 rows=201 loops=25)

  • Filter: ("tpctasID" = tas."tasID")
  • Rows Removed by Filter: 7983
56. 45.610 45.610 ↓ 0.0 0 4,561

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

  • Index Cond: (("trptrecID" = trec_3."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: 10
57. 0.030 0.213 ↑ 1.0 35 1

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

58. 0.183 0.183 ↑ 1.0 35 1

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

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