explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YCPx : Optimization for: plan #NuCc

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1,336.523 9,192.094 ↑ 5.1 24 1

Gather Merge (cost=802,850.54..802,864.77 rows=122 width=118) (actual time=7,905.896..9,192.094 rows=24 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • JIT:
  • Functions: 144
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 31.444 ms, Inlining 167.000 ms, Optimization 1991.792 ms, Emission 1321.376 ms, Total 3511.612 ms
2. 0.082 7,855.571 ↑ 7.6 8 3 / 3

Sort (cost=801,850.51..801,850.66 rows=61 width=118) (actual time=7,855.485..7,855.571 rows=8 loops=3)

  • Sort Key: s.dt_hora_sorteo, p.boleta
  • Sort Method: quicksort Memory: 26kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 26kB
3. 867.234 7,855.489 ↑ 7.6 8 3 / 3

Parallel Hash Left Join (cost=552,293.89..801,848.70 rows=61 width=118) (actual time=7,188.589..7,855.489 rows=8 loops=3)

  • Hash Cond: ((s.id_sorteo = t.nu_sorteo) AND (s.id_juego = t.id_transaccion) AND ("substring"((p.boleta)::text, 1, 9) = (t.boleta)::text))
  • Join Filter: (p.accion = 'MTL'::bpchar)
4. 0.106 13.792 ↑ 7.6 8 3 / 3

Parallel Hash Anti Join (cost=3,708.05..7,672.79 rows=61 width=68) (actual time=8.399..13.792 rows=8 loops=3)

  • Hash Cond: ((p.boleta = pint.boleta) AND (p.sorteo = pint.sorteo) AND (p.juego = pint.juego))
5. 0.176 5.790 ↑ 7.6 8 3 / 3

Hash Join (cost=20.09..3,983.74 rows=61 width=81) (actual time=0.410..5.790 rows=8 loops=3)

  • Hash Cond: ((p.sorteo)::text = to_char(s.dt_hora_sorteo, 'yyyymmddHH24MI'::text))
  • Join Filter: (((s.id_juego = '012'::bpchar) AND (p.juego = 'ORO'::bpchar)) OR ((s.id_juego = '010'::bpchar) AND (p.juego = 'QUI'::bpchar)) OR ((s.id_juego = '011'::bpchar) AND (p.juego = 'TOM'::bpchar)) OR ((s.id_juego = '018'::bpchar) AND (p.juego = 'LOT'::bpchar)))
  • Rows Removed by Join Filter: 2
6. 5.375 5.375 ↑ 2.7 416 3 / 3

Parallel Seq Scan on t_caja_pago_nacional p (cost=0.00..3,936.85 rows=1,134 width=58) (actual time=0.064..5.375 rows=416 loops=3)

  • Filter: ((banca = '17'::bpchar) AND ('01'::bpchar = agencia) AND ((accion = 'MTK'::bpchar) OR (accion = 'MTL'::bpchar)) AND ((juego = 'ORO'::bpchar) OR (juego = 'QUI'::bpchar) OR (juego = 'TOM'::bpchar) OR (juego = 'LOT'::bpchar)))
  • Rows Removed by Filter: 12,777
7. 0.130 0.239 ↓ 1.2 51 3 / 3

Hash (cost=19.57..19.57 rows=41 width=23) (actual time=0.236..0.239 rows=51 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
8. 0.043 0.109 ↓ 1.2 51 3 / 3

Bitmap Heap Scan on t_sorteo s (cost=4.86..19.57 rows=41 width=23) (actual time=0.087..0.109 rows=51 loops=3)

  • Recheck Cond: ((dt_hora_sorteo >= to_timestamp('01/08/2020 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND (dt_hora_sorteo <= to_timestamp('16/08/2020 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)))
  • Filter: ((id_juego = '012'::bpchar) OR (id_juego = '010'::bpchar) OR (id_juego = '011'::bpchar) OR (id_juego = '018'::bpchar))
  • Heap Blocks: exact=2
9. 0.066 0.066 ↑ 1.1 51 3 / 3

Bitmap Index Scan on t_sorteo_dt_hora_sorteo_idx (cost=0.00..4.85 rows=57 width=0) (actual time=0.065..0.066 rows=51 loops=3)

  • Index Cond: ((dt_hora_sorteo >= to_timestamp('01/08/2020 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)) AND (dt_hora_sorteo <= to_timestamp('16/08/2020 00:00:00'::text, 'DD/MM/YYYY HH24:MI:SS'::text)))
10. 0.059 7.896 ↑ 1.0 2 3 / 3

Parallel Hash (cost=3,687.93..3,687.93 rows=2 width=28) (actual time=7.894..7.896 rows=2 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 72kB
11. 7.837 7.837 ↑ 1.0 2 3 / 3

Parallel Seq Scan on t_caja_pago_nacional pint (cost=0.00..3,687.93 rows=2 width=28) (actual time=2.685..7.837 rows=2 loops=3)

  • Filter: ((accion = 'DTK'::bpchar) OR (accion = 'DTL'::bpchar))
  • Rows Removed by Filter: 13,191
12. 3,977.902 6,974.463 ↓ 1.3 5,688,675 3 / 3

Parallel Hash (cost=423,723.40..423,723.40 rows=4,273,340 width=67) (actual time=6,974.463..6,974.463 rows=5,688,675 loops=3)

  • Buckets: 65,536 Batches: 512 Memory Usage: 3,968kB
13. 2,996.561 2,996.561 ↓ 1.3 5,688,675 3 / 3

Parallel Seq Scan on t_transaccion t (cost=0.00..423,723.40 rows=4,273,340 width=67) (actual time=1,161.103..2,996.561 rows=5,688,675 loops=3)

Planning time : 1.660 ms
Execution time : 9,203.275 ms