explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ydO : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #8x6nX; plan #5Iv; plan #8BRp; plan #Chgc; plan #t2mf; plan #oirU; plan #J12Z; plan #HG1h

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 101.949 36,127.741 ↓ 81.0 81 1

Merge Join (cost=288,756.04..288,770.32 rows=1 width=487) (actual time=36,026.414..36,127.741 rows=81 loops=1)

  • Merge Cond: (dia.dt_ponto = pontos.dt_ponto)
2.          

Initplan (for Merge Join)

3. 0.007 0.007 ↓ 0.0 0 1

Seq Scan on configuracoes (cost=0.00..1.02 rows=1 width=32) (actual time=0.007..0.007 rows=0 loops=1)

  • Filter: (id = '4acee0df-4acd-49f1-9859-f994714698d0'::uuid)
  • Rows Removed by Filter: 2
4. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on configuracoes configuracoes_1 (cost=0.00..1.02 rows=1 width=32) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: (id = '838bf5b4-cc31-45ff-a437-69d016a13f1b'::uuid)
  • Rows Removed by Filter: 2
5. 0.081 36,024.916 ↑ 7.1 79 1

Sort (cost=288,535.48..288,536.88 rows=558 width=24) (actual time=36,024.891..36,024.916 rows=79 loops=1)

  • Sort Key: dia.dt_ponto
  • Sort Method: quicksort Memory: 31kB
6. 0.056 36,024.835 ↑ 7.1 79 1

Subquery Scan on dia (cost=285,828.77..288,510.03 rows=558 width=24) (actual time=652.686..36,024.835 rows=79 loops=1)

7. 36,024.779 36,024.779 ↑ 7.1 79 1

CTE Scan on ponto (cost=285,828.77..288,504.45 rows=558 width=236) (actual time=652.684..36,024.779 rows=79 loops=1)

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

CTE ponto

9. 35,110.636 35,796.709 ↑ 1.0 111,468 1

Result (cost=50,477.26..285,828.77 rows=111,541 width=180) (actual time=622.760..35,796.709 rows=111,468 loops=1)

10. 186.842 686.073 ↑ 1.0 111,468 1

Sort (cost=50,477.26..50,756.11 rows=111,541 width=124) (actual time=622.208..686.073 rows=111,468 loops=1)

  • Sort Key: pontos_1.user_id, pontos_1.dt_ponto
  • Sort Method: external sort Disk: 9432kB
11. 105.141 499.231 ↑ 1.0 111,468 1

WindowAgg (cost=26,630.93..33,881.10 rows=111,541 width=124) (actual time=275.659..499.231 rows=111,468 loops=1)

12. 87.741 394.090 ↑ 1.0 111,468 1

WindowAgg (cost=26,630.93..29,977.16 rows=111,541 width=40) (actual time=275.652..394.090 rows=111,468 loops=1)

13. 143.111 306.349 ↑ 1.0 111,468 1

Sort (cost=26,630.93..26,909.79 rows=111,541 width=36) (actual time=275.644..306.349 rows=111,468 loops=1)

  • Sort Key: pontos_1.user_id, ((pontos_1.dt_ponto)::date)
  • Sort Method: external sort Disk: 5448kB
14. 83.063 163.238 ↑ 1.0 111,468 1

WindowAgg (cost=0.42..14,227.77 rows=111,541 width=36) (actual time=0.037..163.238 rows=111,468 loops=1)

15. 80.175 80.175 ↑ 1.0 111,468 1

Index Scan using ind_pontos_user_user_id_dt_ponto on pontos pontos_1 (cost=0.42..11,718.10 rows=111,541 width=28) (actual time=0.032..80.175 rows=111,468 loops=1)

  • Filter: situacao
  • Rows Removed by Filter: 754
16. 0.069 0.218 ↓ 1.3 81 1

Sort (cost=218.51..218.66 rows=61 width=41) (actual time=0.184..0.218 rows=81 loops=1)

  • Sort Key: pontos.dt_ponto
  • Sort Method: quicksort Memory: 31kB
17. 0.130 0.149 ↓ 1.3 79 1

Bitmap Heap Scan on pontos (cost=4.89..216.70 rows=61 width=41) (actual time=0.032..0.149 rows=79 loops=1)

  • Recheck Cond: (user_id = '01546725-1e25-4256-9ec1-21d5a89885ce'::uuid)
  • Filter: situacao
  • Heap Blocks: exact=72
18. 0.019 0.019 ↓ 1.3 79 1

Bitmap Index Scan on ind_pontos_user_user_id_dt_ponto (cost=0.00..4.88 rows=61 width=0) (actual time=0.019..0.019 rows=79 loops=1)

  • Index Cond: (user_id = '01546725-1e25-4256-9ec1-21d5a89885ce'::uuid)
19.          

SubPlan (for Merge Join)

20. 0.162 0.405 ↓ 0.0 0 81

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

  • One-Time Filter: pontos.situacao
21. 0.243 0.243 ↓ 0.0 0 81

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

  • Filter: (data = (pontos.dt_ponto)::date)
  • Rows Removed by Filter: 11
22. 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
23. 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.882 ms