explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1,741.719 189,727.524 ↓ 81.0 81 1

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

2.          

Initplan (forResult)

3. 0.011 0.011 ↓ 0.0 0 1

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

  • Index Cond: (id = '4acee0df-4acd-49f1-9859-f994714698d0'::uuid)
4. 0.004 0.004 ↓ 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.004..0.004 rows=0 loops=1)

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

Sort (cost=295,528.79..295,528.80 rows=1 width=61) (actual time=187,984.793..187,984.899 rows=81 loops=1)

  • Sort Key: pontos.dt_ponto
  • Sort Method: quicksort Memory: 36kB
6. 0.105 187,984.738 ↓ 81.0 81 1

Hash Join (cost=292,244.23..295,528.78 rows=1 width=61) (actual time=187,971.063..187,984.738 rows=81 loops=1)

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

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

  • Filter: (situacao AND (user_id = '01546725-1e25-4256-9ec1-21d5a89885ce'::uuid))
  • Rows Removed by Filter: 112091
8. 0.070 187,970.941 ↑ 7.1 79 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
9. 0.066 187,970.871 ↑ 7.1 79 1

Subquery Scan on dia (cost=289,557.49..292,237.27 rows=557 width=24) (actual time=934.505..187,970.871 rows=79 loops=1)

10. 187,970.805 187,970.805 ↑ 7.1 79 1

CTE Scan on ponto (cost=289,557.49..292,231.70 rows=557 width=236) (actual time=934.504..187,970.805 rows=79 loops=1)

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

CTE ponto

12. 186,942.357 187,724.280 ↑ 1.0 111,416 1

Result (cost=54,315.70..289,557.49 rows=111,489 width=180) (actual time=709.173..187,724.280 rows=111,416 loops=1)

13. 196.340 781.923 ↑ 1.0 111,416 1

Sort (cost=54,315.70..54,594.42 rows=111,489 width=124) (actual time=708.389..781.923 rows=111,416 loops=1)

  • Sort Key: pontos_1.user_id, pontos_1.dt_ponto
  • Sort Method: external sort Disk: 9432kB
14. 105.433 585.583 ↑ 1.0 111,416 1

WindowAgg (cost=30,480.99..37,727.77 rows=111,489 width=124) (actual time=363.137..585.583 rows=111,416 loops=1)

15. 86.661 480.150 ↑ 1.0 111,416 1

WindowAgg (cost=30,480.99..33,825.66 rows=111,489 width=40) (actual time=363.132..480.150 rows=111,416 loops=1)

16. 136.056 393.489 ↑ 1.0 111,416 1

Sort (cost=30,480.99..30,759.71 rows=111,489 width=36) (actual time=363.124..393.489 rows=111,416 loops=1)

  • Sort Key: pontos_1.user_id, ((pontos_1.dt_ponto)::date)
  • Sort Method: external sort Disk: 5440kB
17. 76.895 257.433 ↑ 1.0 111,416 1

WindowAgg (cost=15,295.34..18,082.56 rows=111,489 width=36) (actual time=145.144..257.433 rows=111,416 loops=1)

18. 138.337 180.538 ↑ 1.0 111,416 1

Sort (cost=15,295.34..15,574.06 rows=111,489 width=28) (actual time=145.137..180.538 rows=111,416 loops=1)

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

Seq Scan on pontos pontos_1 (cost=0.00..3,278.41 rows=111,489 width=28) (actual time=0.003..42.201 rows=111,416 loops=1)

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

SubPlan (forResult)

21. 0.243 0.648 ↓ 0.0 0 81

Result (cost=0.00..1.17 rows=1 width=318) (actual time=0.008..0.008 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.846 ms