explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lsbG : Optimization for: plan #myAc

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 52.354 52.354 ↑ 1.0 40 1

CTE Scan on join_workingpoints t (cost=8,919.93..8,920.73 rows=40 width=541) (actual time=52.299..52.354 rows=40 loops=1)

2.          

CTE tasks

3. 0.005 1.904 ↑ 1.0 40 1

Limit (cost=98.36..101.96 rows=40 width=653) (actual time=1.662..1.904 rows=40 loops=1)

4. 0.281 1.899 ↑ 4.6 40 1

WindowAgg (cost=98.36..114.83 rows=183 width=653) (actual time=1.661..1.899 rows=40 loops=1)

5. 0.283 1.458 ↑ 1.1 161 1

HashAggregate (cost=98.36..100.19 rows=183 width=453) (actual time=1.377..1.458 rows=161 loops=1)

  • Group Key: tos."tosID", tpc."tpcPrazoValidade", tpc."tpcDescricaoAssessoria", tlsos."tlsosSituacao", tcl."tclID", tpe."tpeTipoPessoa", tpe."tpeNome", tpe."tpeCNPJCPF", tlsos."tlsosDataHora", tde."tdeNome", tse."tseID", tac."tacID
6. 0.069 1.175 ↑ 1.1 161 1

Hash Left Join (cost=60.39..92.87 rows=183 width=453) (actual time=0.760..1.175 rows=161 loops=1)

  • Hash Cond: (tos."tostpcID" = tpc."tpcID")
7. 0.084 1.008 ↑ 1.1 161 1

Hash Left Join (cost=47.09..79.08 rows=183 width=436) (actual time=0.648..1.008 rows=161 loops=1)

  • Hash Cond: (tos."tostlsosUltimoID" = tlsos."tlsosID")
8. 0.067 0.837 ↑ 1.1 161 1

Hash Left Join (cost=39.62..71.12 rows=183 width=428) (actual time=0.538..0.837 rows=161 loops=1)

  • Hash Cond: (tac."tactosID" = tos."tosID")
9. 0.059 0.650 ↑ 1.1 161 1

Hash Join (cost=31.22..62.23 rows=183 width=398) (actual time=0.407..0.650 rows=161 loops=1)

  • Hash Cond: (tac."tactdeID" = tde."tdeID")
10. 0.055 0.575 ↑ 1.1 161 1

Hash Left Join (cost=30.02..60.33 rows=183 width=366) (actual time=0.377..0.575 rows=161 loops=1)

  • Hash Cond: (tac."tactseID" = tse."tseID")
11. 0.079 0.506 ↑ 1.1 161 1

Hash Left Join (cost=28.77..58.53 rows=183 width=334) (actual time=0.350..0.506 rows=161 loops=1)

  • Hash Cond: (tac."tactclID" = tcl."tclID")
12. 0.101 0.101 ↑ 1.1 161 1

Seq Scan on "TabAssessoriaCliente" tac (cost=0.00..27.50 rows=183 width=283) (actual time=0.010..0.101 rows=161 loops=1)

  • Filter: (("tacDataFinalizado" IS NULL) AND ("tacDataFinalizado" IS NULL) AND ("tacEstado" = 1) AND ((("tacDataIniciado" >= '2010-01-01 00:00:00-03'::timestamp with time zone) AND ("tacDataIniciado" <= '2020-12-31 23:59:59-03'::timestamp with time zone)) OR ("tacDataIniciado" IS NULL)))
  • Rows Removed by Filter: 164
13. 0.067 0.326 ↓ 1.0 251 1

Hash (cost=25.76..25.76 rows=241 width=55) (actual time=0.326..0.326 rows=251 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 31kB
14. 0.117 0.259 ↓ 1.0 251 1

Hash Right Join (cost=7.42..25.76 rows=241 width=55) (actual time=0.101..0.259 rows=251 loops=1)

  • Hash Cond: (tpe."tpeID" = tcl."tcltpeID")
15. 0.060 0.060 ↑ 1.1 542 1

Seq Scan on "TabPessoa" tpe (cost=0.00..16.80 rows=580 width=51) (actual time=0.004..0.060 rows=542 loops=1)

16. 0.043 0.082 ↓ 1.0 251 1

Hash (cost=4.41..4.41 rows=241 width=12) (actual time=0.082..0.082 rows=251 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
17. 0.039 0.039 ↓ 1.0 251 1

Seq Scan on "TabCliente" tcl (cost=0.00..4.41 rows=241 width=12) (actual time=0.006..0.039 rows=251 loops=1)

18. 0.007 0.014 ↓ 1.2 13 1

Hash (cost=1.11..1.11 rows=11 width=36) (actual time=0.014..0.014 rows=13 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
19. 0.007 0.007 ↓ 1.2 13 1

Seq Scan on "TabServico" tse (cost=0.00..1.11 rows=11 width=36) (actual time=0.005..0.007 rows=13 loops=1)

20. 0.007 0.016 ↑ 1.0 9 1

Hash (cost=1.09..1.09 rows=9 width=36) (actual time=0.016..0.016 rows=9 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
21. 0.009 0.009 ↑ 1.0 9 1

Seq Scan on "TabDepartamento" tde (cost=0.00..1.09 rows=9 width=36) (actual time=0.006..0.009 rows=9 loops=1)

22. 0.057 0.120 ↓ 1.0 248 1

Hash (cost=5.40..5.40 rows=240 width=34) (actual time=0.120..0.120 rows=248 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 25kB
23. 0.063 0.063 ↓ 1.0 248 1

Seq Scan on "TabOrdemServico" tos (cost=0.00..5.40 rows=240 width=34) (actual time=0.006..0.063 rows=248 loops=1)

24. 0.046 0.087 ↓ 1.0 251 1

Hash (cost=4.43..4.43 rows=243 width=16) (actual time=0.087..0.087 rows=251 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 21kB
25. 0.041 0.041 ↓ 1.0 251 1

Seq Scan on "TabLogSituacaoOrdemServico" tlsos (cost=0.00..4.43 rows=243 width=16) (actual time=0.007..0.041 rows=251 loops=1)

26. 0.047 0.098 ↑ 1.1 248 1

Hash (cost=9.80..9.80 rows=280 width=21) (actual time=0.098..0.098 rows=248 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 22kB
27. 0.051 0.051 ↑ 1.1 248 1

Seq Scan on "TabPropostaComercial" tpc (cost=0.00..9.80 rows=280 width=21) (actual time=0.005..0.051 rows=248 loops=1)

28.          

SubPlan (for WindowAgg)

29. 0.040 0.040 ↑ 1.0 1 40

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=40)

30. 0.040 0.040 ↑ 1.0 1 40

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=40)

31. 0.040 0.040 ↑ 1.0 1 40

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=40)

32. 0.040 0.040 ↑ 1.0 1 40

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=40)

33. 0.000 0.000 ↑ 1.0 1 40

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=40)

34.          

CTE tasks_workingpoints

35. 0.050 46.239 ↑ 3.7 33 1

Hash Semi Join (cost=1.30..7,617.08 rows=121 width=86) (actual time=31.641..46.239 rows=33 loops=1)

  • Hash Cond: (twp."twptacID" = tasks."tacID")
36. 45.863 45.863 ↓ 1.4 238 1

Seq Scan on "TabWorkingPoint" twp (cost=0.00..7,613.98 rows=173 width=86) (actual time=24.071..45.863 rows=238 loops=1)

  • Filter: (("twpManualFix" IS FALSE) AND ("twpIsDeleted" IS FALSE))
  • Rows Removed by Filter: 85
37. 0.014 0.326 ↑ 1.0 40 1

Hash (cost=0.80..0.80 rows=40 width=4) (actual time=0.326..0.326 rows=40 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
38. 0.312 0.312 ↑ 1.0 40 1

CTE Scan on tasks (cost=0.00..0.80 rows=40 width=4) (actual time=0.002..0.312 rows=40 loops=1)

39.          

CTE add_objects

40. 1.774 52.014 ↑ 1.0 40 1

CTE Scan on tasks t_1 (cost=0.00..1,195.97 rows=40 width=541) (actual time=48.116..52.014 rows=40 loops=1)

41.          

SubPlan (for CTE Scan)

42. 0.560 49.960 ↑ 1.0 1 40

Aggregate (cost=17.30..17.31 rows=1 width=32) (actual time=1.249..1.249 rows=1 loops=40)

43.          

Initplan (for Aggregate)

44. 0.040 0.160 ↑ 1.0 1 40

Aggregate (cost=2.73..2.74 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=40)

45. 0.120 0.120 ↑ 1.0 1 40

CTE Scan on tasks_workingpoints twp3 (cost=0.00..2.72 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=40)

  • Filter: ("twptacID" = t_1."tacID")
  • Rows Removed by Filter: 32
46. 0.058 49.240 ↑ 1.0 1 40

Nested Loop Left Join (cost=9.62..14.56 rows=1 width=65) (actual time=1.228..1.231 rows=1 loops=40)

  • Join Filter: (twp2."twptuiID" = tui."tuiID")
  • Rows Removed by Join Filter: 0
47. 0.108 2.520 ↑ 1.0 1 40

Nested Loop Left Join (cost=6.85..11.76 rows=1 width=53) (actual time=0.061..0.063 rows=1 loops=40)

48. 0.320 2.160 ↑ 1.0 1 40

Hash Join (cost=6.44..9.41 rows=1 width=30) (actual time=0.053..0.054 rows=1 loops=40)

  • Hash Cond: (tui."tuiID" = tacui."tactuituiID")
49. 0.160 0.160 ↑ 2.0 35 40

Seq Scan on "TabUsuarioInterno" tui (cost=0.00..2.70 rows=70 width=26) (actual time=0.001..0.004 rows=35 loops=40)

50. 0.080 1.680 ↑ 1.0 1 40

Hash (cost=6.42..6.42 rows=1 width=8) (actual time=0.042..0.042 rows=1 loops=40)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
51. 1.600 1.600 ↑ 1.0 1 40

Seq Scan on "TabAssessoriaClienteUsuarioInterno" tacui (cost=0.00..6.42 rows=1 width=8) (actual time=0.030..0.040 rows=1 loops=40)

  • Filter: ("tactuitacID" = t_1."tacID")
  • Rows Removed by Filter: 353
52. 0.084 0.252 ↑ 1.0 1 42

Nested Loop Left Join (cost=0.42..2.34 rows=1 width=31) (actual time=0.005..0.006 rows=1 loops=42)

53. 0.084 0.084 ↑ 1.0 1 42

Index Scan using "TabFuncionario_tfuID" on "TabFuncionario" tfu (cost=0.14..0.33 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=42)

  • Index Cond: ("tfuID" = tui."tuitfuID")
54. 0.084 0.084 ↑ 1.0 1 42

Index Scan using "TabPessoaID" on "TabPessoa" tpe_resposavel (cost=0.28..2.01 rows=1 width=31) (actual time=0.002..0.002 rows=1 loops=42)

  • Index Cond: ("tpeID" = tfu."tfutpeID")
55. 0.000 46.662 ↓ 0.0 0 42

Subquery Scan on twp2 (cost=2.76..2.78 rows=1 width=16) (actual time=1.111..1.111 rows=0 loops=42)

  • Filter: (twp2.r = 1)
  • Rows Removed by Filter: 1
56. 0.142 46.662 ↑ 1.0 1 42

Sort (cost=2.76..2.77 rows=1 width=94) (actual time=1.110..1.111 rows=1 loops=42)

  • Sort Key: twp2_1."twpID" DESC
  • Sort Method: quicksort Memory: 25kB
57. 0.040 46.520 ↑ 1.0 1 40

WindowAgg (cost=2.73..2.75 rows=1 width=94) (actual time=1.163..1.163 rows=1 loops=40)

58. 0.080 46.480 ↑ 1.0 1 40

Sort (cost=2.73..2.74 rows=1 width=16) (actual time=1.162..1.162 rows=1 loops=40)

  • Sort Key: twp2_1."twptuiID", twp2_1."twpID" DESC
  • Sort Method: quicksort Memory: 25kB
59. 46.400 46.400 ↑ 1.0 1 40

CTE Scan on tasks_workingpoints twp2_1 (cost=0.00..2.72 rows=1 width=16) (actual time=1.159..1.160 rows=1 loops=40)

  • Filter: ("twptacID" = t_1."tacID")
  • Rows Removed by Filter: 32
60. 0.120 0.280 ↑ 1.0 1 40

Aggregate (cost=12.55..12.56 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=40)

61. 0.000 0.160 ↓ 0.0 0 40

Nested Loop (cost=0.00..2.30 rows=1 width=19) (actual time=0.004..0.004 rows=0 loops=40)

  • Join Filter: (tltac."tltactlID" = tl."tlID")
62. 0.160 0.160 ↓ 0.0 0 40

Seq Scan on "TabLabels" tl (cost=0.00..1.16 rows=1 width=15) (actual time=0.004..0.004 rows=0 loops=40)

  • Filter: ("tlISDeleted" IS FALSE)
  • Rows Removed by Filter: 16
63. 0.000 0.000 ↓ 0.0 0

Seq Scan on "TabLabelsAssessoriaCliente" tltac (cost=0.00..1.10 rows=3 width=8) (never executed)

  • Filter: (t_1."tacID" = "tltactacID")
64.          

SubPlan (for Aggregate)

65. 0.000 0.000 ↓ 0.0 0

Function Scan on proc_component_getlabelscolor (cost=0.25..10.25 rows=1,000 width=32) (never executed)

66.          

CTE join_workingpoints

67. 0.074 52.299 ↑ 1.0 40 1

Sort (cost=4.82..4.92 rows=40 width=553) (actual time=52.297..52.299 rows=40 loops=1)

  • Sort Key: twp_1."twpDue" DESC, twp_1."twpID
  • Sort Method: quicksort Memory: 57kB
68. 0.062 52.225 ↑ 1.0 40 1

Hash Left Join (cost=2.79..3.75 rows=40 width=553) (actual time=48.198..52.225 rows=40 loops=1)

  • Hash Cond: (t_2."tacID" = twp_1."twptacID")
69. 52.111 52.111 ↑ 1.0 40 1

CTE Scan on add_objects t_2 (cost=0.00..0.80 rows=40 width=541) (actual time=48.120..52.111 rows=40 loops=1)

70. 0.006 0.052 ↓ 2.0 2 1

Hash (cost=2.78..2.78 rows=1 width=16) (actual time=0.052..0.052 rows=2 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
71. 0.003 0.046 ↓ 2.0 2 1

Subquery Scan on twp_1 (cost=2.76..2.78 rows=1 width=16) (actual time=0.045..0.046 rows=2 loops=1)

  • Filter: (twp_1.r = 1)
  • Rows Removed by Filter: 2
72. 0.016 0.043 ↓ 4.0 4 1

Sort (cost=2.76..2.77 rows=1 width=94) (actual time=0.043..0.043 rows=4 loops=1)

  • Sort Key: tasks_workingpoints."twpID" DESC
  • Sort Method: quicksort Memory: 25kB
73. 0.009 0.027 ↓ 4.0 4 1

WindowAgg (cost=2.73..2.75 rows=1 width=94) (actual time=0.023..0.027 rows=4 loops=1)

74. 0.013 0.018 ↓ 4.0 4 1

Sort (cost=2.73..2.74 rows=1 width=16) (actual time=0.017..0.018 rows=4 loops=1)

  • Sort Key: tasks_workingpoints."twptacID", tasks_workingpoints."twpID" DESC
  • Sort Method: quicksort Memory: 25kB
75. 0.005 0.005 ↓ 4.0 4 1

CTE Scan on tasks_workingpoints (cost=0.00..2.72 rows=1 width=16) (actual time=0.002..0.005 rows=4 loops=1)

  • Filter: ("twptuiID" = 3)
  • Rows Removed by Filter: 29
Planning time : 12.192 ms
Execution time : 53.041 ms