explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HG1h : 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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 100.937 35,693.115 ↓ 81.0 81 1

Merge Join (cost=292,658.14..292,672.42 rows=1 width=487) (actual time=35,592.525..35,693.115 rows=81 loops=1)

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

Initplan (forMerge 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.011 0.011 ↓ 0.0 0 1

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

  • Filter: (id = '838bf5b4-cc31-45ff-a437-69d016a13f1b'::uuid)
  • Rows Removed by Filter: 2
5. 0.068 0.209 ↓ 1.3 79 1

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

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

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

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

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

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

Sort (cost=292,437.59..292,438.98 rows=558 width=24) (actual time=35,591.262..35,591.303 rows=81 loops=1)

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

Subquery Scan on dia (cost=289,730.54..292,412.13 rows=558 width=24) (actual time=734.151..35,591.212 rows=79 loops=1)

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

CTE Scan on ponto (cost=289,730.54..292,406.55 rows=558 width=236) (actual time=734.150..35,591.156 rows=79 loops=1)

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

CTE ponto

12. 34,605.804 35,375.137 ↑ 1.0 111,461 1

Result (cost=54,347.38..289,730.54 rows=111,556 width=180) (actual time=705.960..35,375.137 rows=111,461 loops=1)

13. 184.443 769.333 ↑ 1.0 111,461 1

Sort (cost=54,347.38..54,626.27 rows=111,556 width=124) (actual time=705.423..769.333 rows=111,461 loops=1)

  • Sort Key: pontos_1.user_id, pontos_1.dt_ponto
  • Sort Method: external sort Disk: 9432kB
14. 106.359 584.890 ↑ 1.0 111,461 1

WindowAgg (cost=30,498.71..37,749.85 rows=111,556 width=124) (actual time=359.664..584.890 rows=111,461 loops=1)

15. 88.085 478.531 ↑ 1.0 111,461 1

WindowAgg (cost=30,498.71..33,845.39 rows=111,556 width=40) (actual time=359.659..478.531 rows=111,461 loops=1)

16. 133.815 390.446 ↑ 1.0 111,461 1

Sort (cost=30,498.71..30,777.60 rows=111,556 width=36) (actual time=359.653..390.446 rows=111,461 loops=1)

  • Sort Key: pontos_1.user_id, ((pontos_1.dt_ponto)::date)
  • Sort Method: external sort Disk: 5448kB
17. 77.603 256.631 ↑ 1.0 111,461 1

WindowAgg (cost=15,305.29..18,094.19 rows=111,556 width=36) (actual time=143.881..256.631 rows=111,461 loops=1)

18. 136.184 179.028 ↑ 1.0 111,461 1

Sort (cost=15,305.29..15,584.18 rows=111,556 width=28) (actual time=143.874..179.028 rows=111,461 loops=1)

  • Sort Key: pontos_1.user_id, pontos_1.dt_ponto
  • Sort Method: external merge Disk: 4160kB
19. 42.844 42.844 ↑ 1.0 111,461 1

Seq Scan on pontos pontos_1 (cost=0.00..3,282.26 rows=111,556 width=28) (actual time=0.006..42.844 rows=111,461 loops=1)

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

SubPlan (forMerge Join)

21. 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
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.774 ms