explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Chgc : Optimization for: Optimization for: Optimization for: plan #8x6nX; plan #5Iv; plan #8BRp

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1,701.918 189,742.529 ↓ 81.0 81 1

Result (cost=295,545.11..295,556.30 rows=1 width=487) (actual time=188,058.045..189,742.529 rows=81 loops=1)

2.          

Initplan (forResult)

3. 0.036 0.036 ↓ 0.0 0 1

Index Scan using configuracoes_pkey on configuracoes (cost=0.14..8.16 rows=1 width=32) (actual time=0.036..0.036 rows=0 loops=1)

  • Index Cond: (id = '4acee0df-4acd-49f1-9859-f994714698d0'::uuid)
4. 0.005 0.005 ↓ 0.0 0 1

Index Scan using configuracoes_pkey on configuracoes configuracoes_1 (cost=0.14..8.16 rows=1 width=32) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: (id = '838bf5b4-cc31-45ff-a437-69d016a13f1b'::uuid)
5. 0.141 188,039.760 ↓ 81.0 81 1

Sort (cost=295,528.79..295,528.80 rows=1 width=61) (actual time=188,039.674..188,039.760 rows=81 loops=1)

  • Sort Key: pontos.dt_ponto
  • Sort Method: quicksort Memory: 36kB
6. 0.104 188,039.619 ↓ 81.0 81 1

Hash Join (cost=292,244.23..295,528.78 rows=1 width=61) (actual time=188,025.897..188,039.619 rows=81 loops=1)

  • Hash Cond: (pontos.dt_ponto = dia.dt_ponto)
7. 13.741 13.741 ↓ 1.3 79 1

Seq Scan on pontos (cost=0.00..3,280.11 rows=61 width=41) (actual time=0.110..13.741 rows=79 loops=1)

  • Filter: (situacao AND (user_id = '01546725-1e25-4256-9ec1-21d5a89885ce'::uuid))
  • Rows Removed by Filter: 112090
8. 0.075 188,025.774 ↑ 7.1 79 1

Hash (cost=292,237.27..292,237.27 rows=557 width=24) (actual time=188,025.774..188,025.774 rows=79 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
9. 0.067 188,025.699 ↑ 7.1 79 1

Subquery Scan on dia (cost=289,557.49..292,237.27 rows=557 width=24) (actual time=945.293..188,025.699 rows=79 loops=1)

10. 188,025.632 188,025.632 ↑ 7.1 79 1

CTE Scan on ponto (cost=289,557.49..292,231.70 rows=557 width=236) (actual time=945.292..188,025.632 rows=79 loops=1)

  • Filter: (user_id = '01546725-1e25-4256-9ec1-21d5a89885ce'::uuid)
  • Rows Removed by Filter: 111336
11.          

CTE ponto

12. 186,990.763 187,782.089 ↑ 1.0 111,415 1

Result (cost=54,315.70..289,557.49 rows=111,489 width=180) (actual time=715.626..187,782.089 rows=111,415 loops=1)

13. 200.835 791.326 ↑ 1.0 111,415 1

Sort (cost=54,315.70..54,594.42 rows=111,489 width=124) (actual time=714.781..791.326 rows=111,415 loops=1)

  • Sort Key: pontos_1.user_id, pontos_1.dt_ponto
  • Sort Method: external sort Disk: 9432kB
14. 105.909 590.491 ↑ 1.0 111,415 1

WindowAgg (cost=30,480.99..37,727.77 rows=111,489 width=124) (actual time=366.657..590.491 rows=111,415 loops=1)

15. 87.398 484.582 ↑ 1.0 111,415 1

WindowAgg (cost=30,480.99..33,825.66 rows=111,489 width=40) (actual time=366.653..484.582 rows=111,415 loops=1)

16. 137.377 397.184 ↑ 1.0 111,415 1

Sort (cost=30,480.99..30,759.71 rows=111,489 width=36) (actual time=366.645..397.184 rows=111,415 loops=1)

  • Sort Key: pontos_1.user_id, ((pontos_1.dt_ponto)::date)
  • Sort Method: external sort Disk: 5440kB
17. 78.560 259.807 ↑ 1.0 111,415 1

WindowAgg (cost=15,295.34..18,082.56 rows=111,489 width=36) (actual time=145.911..259.807 rows=111,415 loops=1)

18. 138.377 181.247 ↑ 1.0 111,415 1

Sort (cost=15,295.34..15,574.06 rows=111,489 width=28) (actual time=145.903..181.247 rows=111,415 loops=1)

  • Sort Key: pontos_1.user_id, pontos_1.dt_ponto
  • Sort Method: external merge Disk: 4152kB
19. 42.870 42.870 ↑ 1.0 111,415 1

Seq Scan on pontos pontos_1 (cost=0.00..3,278.41 rows=111,489 width=28) (actual time=0.004..42.870 rows=111,415 loops=1)

  • Filter: situacao
  • Rows Removed by Filter: 754
20.          

SubPlan (forResult)

21. 0.162 0.567 ↓ 0.0 0 81

Result (cost=0.00..1.17 rows=1 width=318) (actual time=0.007..0.007 rows=0 loops=81)

  • One-Time Filter: pontos.situacao
22. 0.405 0.405 ↓ 0.0 0 81

Seq Scan on feriados (cost=0.00..1.17 rows=1 width=318) (actual time=0.005..0.005 rows=0 loops=81)

  • Filter: (data = (pontos.dt_ponto)::date)
  • Rows Removed by Filter: 11
23. 0.081 0.243 ↓ 0.0 0 81

Result (cost=0.00..1.17 rows=1 width=1) (actual time=0.003..0.003 rows=0 loops=81)

  • One-Time Filter: pontos.situacao
24. 0.162 0.162 ↓ 0.0 0 81

Seq Scan on feriados feriados_1 (cost=0.00..1.17 rows=1 width=1) (actual time=0.002..0.002 rows=0 loops=81)

  • Filter: (data = (pontos.dt_ponto)::date)
  • Rows Removed by Filter: 11
Planning time : 0.818 ms