explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 97.045 35,572.676 ↓ 81.0 81 1

Merge Join (cost=292,517.06..292,531.33 rows=1 width=487) (actual time=35,476.007..35,572.676 rows=81 loops=1)

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

Initplan (forMerge Join)

3. 0.006 0.006 ↓ 0.0 0 1

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

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

  • Index Cond: (id = '838bf5b4-cc31-45ff-a437-69d016a13f1b'::uuid)
5. 0.071 0.218 ↓ 1.3 79 1

Sort (cost=218.50..218.65 rows=61 width=41) (actual time=0.191..0.218 rows=79 loops=1)

  • Sort Key: pontos.dt_ponto
  • Sort Method: quicksort Memory: 31kB
6. 0.129 0.147 ↓ 1.3 79 1

Bitmap Heap Scan on pontos (cost=4.89..216.69 rows=61 width=41) (actual time=0.030..0.147 rows=79 loops=1)

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

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

  • Index Cond: (user_id = '01546725-1e25-4256-9ec1-21d5a89885ce'::uuid)
8. 0.091 35,474.837 ↑ 6.9 81 1

Sort (cost=292,282.24..292,283.63 rows=557 width=24) (actual time=35,474.801..35,474.837 rows=81 loops=1)

  • Sort Key: dia.dt_ponto
  • Sort Method: quicksort Memory: 31kB
9. 0.057 35,474.746 ↑ 7.1 79 1

Subquery Scan on dia (cost=289,576.90..292,256.83 rows=557 width=24) (actual time=748.116..35,474.746 rows=79 loops=1)

10. 35,474.689 35,474.689 ↑ 7.1 79 1

CTE Scan on ponto (cost=289,576.90..292,251.26 rows=557 width=236) (actual time=748.115..35,474.689 rows=79 loops=1)

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

CTE ponto

12. 34,475.570 35,254.992 ↑ 1.0 111,432 1

Result (cost=54,320.34..289,576.90 rows=111,496 width=180) (actual time=718.850..35,254.992 rows=111,432 loops=1)

13. 186.827 779.422 ↑ 1.0 111,432 1

Sort (cost=54,320.34..54,599.08 rows=111,496 width=124) (actual time=717.591..779.422 rows=111,432 loops=1)

  • Sort Key: pontos_1.user_id, pontos_1.dt_ponto
  • Sort Method: external sort Disk: 9432kB
14. 105.853 592.595 ↑ 1.0 111,432 1

WindowAgg (cost=30,484.53..37,731.77 rows=111,496 width=124) (actual time=368.349..592.595 rows=111,432 loops=1)

15. 87.925 486.742 ↑ 1.0 111,432 1

WindowAgg (cost=30,484.53..33,829.41 rows=111,496 width=40) (actual time=368.343..486.742 rows=111,432 loops=1)

16. 138.995 398.817 ↑ 1.0 111,432 1

Sort (cost=30,484.53..30,763.27 rows=111,496 width=36) (actual time=368.335..398.817 rows=111,432 loops=1)

  • Sort Key: pontos_1.user_id, ((pontos_1.dt_ponto)::date)
  • Sort Method: external sort Disk: 5448kB
17. 78.105 259.822 ↑ 1.0 111,432 1

WindowAgg (cost=15,298.07..18,085.47 rows=111,496 width=36) (actual time=146.166..259.822 rows=111,432 loops=1)

18. 138.579 181.717 ↑ 1.0 111,432 1

Sort (cost=15,298.07..15,576.81 rows=111,496 width=28) (actual time=146.159..181.717 rows=111,432 loops=1)

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

Seq Scan on pontos pontos_1 (cost=0.00..3,280.51 rows=111,496 width=28) (actual time=0.007..43.138 rows=111,432 loops=1)

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

SubPlan (forMerge Join)

21. 0.081 0.324 ↓ 0.0 0 81

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

  • One-Time Filter: pontos.situacao
22. 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
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.686 ms