explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1,740.374 187,713.239 ↓ 81.0 81 1

Result (cost=295,615.01..295,626.20 rows=1 width=487) (actual time=185,990.578..187,713.239 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.167 185,971.878 ↓ 81.0 81 1

Sort (cost=295,598.69..295,598.70 rows=1 width=61) (actual time=185,971.794..185,971.878 rows=81 loops=1)

  • Sort Key: pontos.dt_ponto
  • Sort Method: quicksort Memory: 36kB
6. 0.102 185,971.711 ↓ 81.0 81 1

Hash Join (cost=292,314.78..295,598.68 rows=1 width=61) (actual time=185,957.677..185,971.711 rows=81 loops=1)

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

Seq Scan on pontos (cost=0.00..3,279.46 rows=61 width=41) (actual time=0.113..14.060 rows=79 loops=1)

  • Filter: (situacao AND (user_id = '01546725-1e25-4256-9ec1-21d5a89885ce'::uuid))
  • Rows Removed by Filter: 112081
8. 0.077 185,957.549 ↑ 7.1 79 1

Hash (cost=292,307.80..292,307.80 rows=558 width=24) (actual time=185,957.549..185,957.549 rows=79 loops=1)

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

Subquery Scan on dia (cost=289,627.11..292,307.80 rows=558 width=24) (actual time=955.296..185,957.472 rows=79 loops=1)

10. 185,957.406 185,957.406 ↑ 7.1 79 1

CTE Scan on ponto (cost=289,627.11..292,302.22 rows=558 width=236) (actual time=955.295..185,957.406 rows=79 loops=1)

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

CTE ponto

12. 184,921.034 185,714.347 ↑ 1.0 111,406 1

Result (cost=54,328.35..289,627.11 rows=111,516 width=180) (actual time=723.282..185,714.347 rows=111,406 loops=1)

13. 200.057 793.313 ↑ 1.0 111,406 1

Sort (cost=54,328.35..54,607.14 rows=111,516 width=124) (actual time=721.279..793.313 rows=111,406 loops=1)

  • Sort Key: pontos_1.user_id, pontos_1.dt_ponto
  • Sort Method: external sort Disk: 9432kB
14. 106.432 593.256 ↑ 1.0 111,406 1

WindowAgg (cost=30,485.93..37,734.47 rows=111,516 width=124) (actual time=367.991..593.256 rows=111,406 loops=1)

15. 87.359 486.824 ↑ 1.0 111,406 1

WindowAgg (cost=30,485.93..33,831.41 rows=111,516 width=40) (actual time=367.984..486.824 rows=111,406 loops=1)

16. 139.086 399.465 ↑ 1.0 111,406 1

Sort (cost=30,485.93..30,764.72 rows=111,516 width=36) (actual time=367.973..399.465 rows=111,406 loops=1)

  • Sort Key: pontos_1.user_id, ((pontos_1.dt_ponto)::date)
  • Sort Method: external sort Disk: 5440kB
17. 77.258 260.379 ↑ 1.0 111,406 1

WindowAgg (cost=15,297.14..18,085.04 rows=111,516 width=36) (actual time=147.334..260.379 rows=111,406 loops=1)

18. 140.330 183.121 ↑ 1.0 111,406 1

Sort (cost=15,297.14..15,575.93 rows=111,516 width=28) (actual time=147.327..183.121 rows=111,406 loops=1)

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

Seq Scan on pontos pontos_1 (cost=0.00..3,277.76 rows=111,516 width=28) (actual time=0.004..42.791 rows=111,406 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.162 0.324 ↓ 0.0 0 81

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