explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NuCc

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 2,579.244 15,340.846 ↑ 5.1 24 1

Gather Merge (cost=802,850.69..802,864.92 rows=122 width=142) (actual time=12,811.568..15,340.846 rows=24 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • JIT:
  • Functions: 144
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 30.345 ms, Inlining 237.070 ms, Optimization 1958.893 ms, Emission 1330.474 ms, Total 3556.783 ms
2. 0.104 12,761.602 ↑ 7.6 8 3 / 3

Sort (cost=801,850.66..801,850.82 rows=61 width=142) (actual time=12,761.399..12,761.602 rows=8 loops=3)

  • Sort Key: (to_char(s.dt_hora_sorteo, 'dd/mm/yyyy HH24:MI'::text)), p.boleta
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 26kB
  • Worker 1: Sort Method: quicksort Memory: 26kB
3. 861.560 12,761.498 ↑ 7.6 8 3 / 3

Parallel Hash Left Join (cost=552,293.89..801,848.86 rows=61 width=142) (actual time=12,153.300..12,761.498 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.277 18.439 ↑ 7.6 8 3 / 3

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

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

Hash Join (cost=20.09..3,983.74 rows=61 width=81) (actual time=0.520..6.736 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. 6.215 6.215 ↑ 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.066..6.215 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.177 0.299 ↓ 1.2 51 3 / 3

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

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

Bitmap Heap Scan on t_sorteo s (cost=4.86..19.57 rows=41 width=23) (actual time=0.095..0.122 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.070 0.070 ↑ 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.070..0.070 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.052 11.426 ↑ 1.0 2 3 / 3

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 72kB
11. 11.374 11.374 ↑ 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=3.860..11.374 rows=2 loops=3)

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

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

  • Buckets: 65,536 Batches: 512 Memory Usage: 3,968kB
13. 6,813.358 6,813.358 ↓ 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,176.577..6,813.358 rows=5,688,675 loops=3)

Planning time : 3.364 ms
Execution time : 15,399.058 ms