explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Vwes

Settings
# exclusive inclusive rows x rows loops node
1. 0.051 16,789.209 ↑ 16.9 30 1

Sort (cost=3,969,294.93..3,969,296.19 rows=506 width=8) (actual time=16,789.194..16,789.209 rows=30 loops=1)

  • Sort Key: ((generate_series.generate_series)::timestamp without time zone)
  • Sort Method: quicksort Memory: 26kB
2. 0.089 16,789.158 ↑ 16.9 30 1

HashAggregate (cost=3,969,267.14..3,969,272.20 rows=506 width=8) (actual time=16,789.137..16,789.158 rows=30 loops=1)

  • Group Key: ((generate_series.generate_series)::timestamp without time zone)
3. 0.075 16,789.069 ↑ 16.9 30 1

Append (cost=18.99..3,969,265.88 rows=506 width=8) (actual time=2.674..16,789.069 rows=30 loops=1)

4. 0.290 2.990 ↑ 17.9 28 1

Function Scan on generate_series (cost=18.99..3,218,550.75 rows=500 width=8) (actual time=2.670..2.990 rows=28 loops=1)

  • Filter: (NOT (SubPlan 2))
  • Rows Removed by Filter: 2
5.          

SubPlan (for Function Scan)

6. 0.126 2.700 ↑ 8.5 2 30

Materialize (cost=18.98..6,455.97 rows=17 width=4) (actual time=0.018..0.090 rows=2 loops=30)

7. 0.019 2.574 ↑ 8.5 2 1

Nested Loop (cost=18.98..6,455.89 rows=17 width=4) (actual time=0.488..2.574 rows=2 loops=1)

8. 0.026 0.026 ↑ 2.0 1 1

Index Scan using idx_epontows_tfunciohora_funccontrafk on t_funcionario_horario t_funcionario_horario_1 (cost=0.29..11.24 rows=2 width=8) (actual time=0.022..0.026 rows=1 loops=1)

  • Index Cond: (funcionario_contrato_fk = 274)
9. 2.385 2.529 ↑ 4.5 2 1

Bitmap Heap Scan on t_batida_rel t_batida_rel_1 (cost=18.69..3,222.23 rows=9 width=12) (actual time=0.455..2.529 rows=2 loops=1)

  • Recheck Cond: (funcionario_horario_fk = t_funcionario_horario_1.funcionario_horario_pk)
  • Filter: ((data >= '2020-08-01'::date) AND (data <= '2020-08-30'::date))
  • Rows Removed by Filter: 104
  • Heap Blocks: exact=99
10. 0.144 0.144 ↑ 7.9 106 1

Bitmap Index Scan on idx_epontows_tbatidarel_func_hor_fk (cost=0.00..18.69 rows=834 width=0) (actual time=0.144..0.144 rows=106 loops=1)

  • Index Cond: (funcionario_horario_fk = t_funcionario_horario_1.funcionario_horario_pk)
11. 0.008 16,786.004 ↑ 3.0 2 1

Subquery Scan on *SELECT* 2 (cost=489,974.09..750,710.13 rows=6 width=4) (actual time=16,516.026..16,786.004 rows=2 loops=1)

12. 101.614 16,785.996 ↑ 3.0 2 1

Hash Semi Join (cost=489,974.09..750,710.07 rows=6 width=4) (actual time=16,516.021..16,785.996 rows=2 loops=1)

  • Hash Cond: (t_batida_rel.funcionario_horario_fk = t_funcionario_horario.funcionario_horario_pk)
13. 250.428 16,684.358 ↓ 3.0 202,020 1

Bitmap Heap Scan on t_batida_rel (cost=489,962.83..750,520.60 rows=67,861 width=12) (actual time=16,513.388..16,684.358 rows=202,020 loops=1)

  • Recheck Cond: ((data >= '2020-08-01'::date) AND (data <= '2020-08-30'::date))
  • Filter: ((batida_1 IS NULL) AND (NOT (hashed SubPlan 1)))
  • Rows Removed by Filter: 97,624
  • Heap Blocks: exact=4,822
14. 52.127 52.127 ↓ 1.6 299,644 1

Bitmap Index Scan on idx_epontows_tbatidarel_data (cost=0.00..4,013.46 rows=191,302 width=0) (actual time=52.127..52.127 rows=299,644 loops=1)

  • Index Cond: ((data >= '2020-08-01'::date) AND (data <= '2020-08-30'::date))
15.          

SubPlan (for Bitmap Heap Scan)

16. 16,381.803 16,381.803 ↓ 8.1 41,487 1

Seq Scan on t_ponto_processado (cost=0.00..485,919.65 rows=5,102 width=8) (actual time=0.016..16,381.803 rows=41,487 loops=1)

  • Filter: ((batida_rel_fk IS NOT NULL) AND (processamento_fk = 1,450))
  • Rows Removed by Filter: 17,946,974
17. 0.008 0.024 ↑ 2.0 1 1

Hash (cost=11.24..11.24 rows=2 width=8) (actual time=0.024..0.024 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
18. 0.016 0.016 ↑ 2.0 1 1

Index Scan using idx_epontows_tfunciohora_funccontrafk on t_funcionario_horario (cost=0.29..11.24 rows=2 width=8) (actual time=0.014..0.016 rows=1 loops=1)

  • Index Cond: (funcionario_contrato_fk = 274)
Planning time : 1.734 ms
Execution time : 16,789.441 ms