explain.depesz.com

PostgreSQL's explain analyze made readable

Result: y9Bq : Optimization for: Optimization for: ach; plan #Knhc; plan #ykW2

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.007 33,005.465 ↑ 1.0 1 1

GroupAggregate (cost=174,847.73..174,847.77 rows=1 width=33) (actual time=33,005.465..33,005.465 rows=1 loops=1)

  • Group Key: (date(t.fecha_creacion)), d.banco, (date_part('day'::text, (date_trunc('day'::text, ('now'::cstring)::timestamp without time zone) - date_trunc('day'::text, t.fecha_creacion))))
2. 0.079 33,005.458 ↓ 10.0 10 1

Sort (cost=174,847.73..174,847.74 rows=1 width=25) (actual time=33,005.456..33,005.458 rows=10 loops=1)

  • Sort Key: (date(t.fecha_creacion)), d.banco, (date_part('day'::text, (date_trunc('day'::text, ('now'::cstring)::timestamp without time zone) - date_trunc('day'::text, t.fecha_creacion))))
  • Sort Method: quicksort Memory: 25kB
3. 0.059 33,005.379 ↓ 10.0 10 1

Nested Loop (cost=130,730.77..174,847.72 rows=1 width=25) (actual time=32,837.025..33,005.379 rows=10 loops=1)

4. 26,039.235 33,004.900 ↓ 12.0 12 1

Bitmap Heap Scan on transaccion t (cost=130,730.21..174,844.07 rows=1 width=45) (actual time=32,836.951..33,004.900 rows=12 loops=1)

  • Recheck Cond: (((medio_pago)::text = 'BANCOLOMBIA_ACH_DEBIT'::text) AND (datos_bancarios_ach_detalle_base_id IS NOT NULL))
  • Filter: (((tipo)::text = ANY ('{AUTHORIZATION,AUTHORIZATION_AND_CAPTURE}'::text[])) AND ((state)::text = 'PENDING'::text) AND (fecha_creacion >= date_trunc('month'::text, (('now'::cstring)::date)::timestamp with time zone)) AND ((date_part('day'::text, (date_trunc('day'::text, ('now'::cstring)::timestamp without time zone) - date_trunc('day'::text, fecha_creacion))) = '5'::double precision) OR (date_part('day'::text, (date_trunc('day'::text, ('now'::cstring)::timestamp without time zone) - date_trunc('day'::text, fecha_creacion))) = '7'::double precision)))
  • Rows Removed by Filter: 4428471
  • Heap Blocks: exact=3757609
5. 1,469.408 6,965.665 ↓ 0.0 0 1

BitmapAnd (cost=130,730.21..130,730.21 rows=28,347 width=0) (actual time=6,965.665..6,965.665 rows=0 loops=1)

6. 2,566.698 2,566.698 ↓ 1.0 4,498,027 1

Bitmap Index Scan on idx_transaccion_medio_pago_rebuild_20200519 (cost=0.00..60,766.97 rows=4,423,386 width=0) (actual time=2,566.698..2,566.698 rows=4,498,027 loops=1)

  • Index Cond: ((medio_pago)::text = 'BANCOLOMBIA_ACH_DEBIT'::text)
7. 2,929.559 2,929.559 ↓ 1.0 4,497,717 1

Bitmap Index Scan on idx_transaccion_datos_bancarios_ach_detalle_ba_rebuild_20200520 (cost=0.00..69,962.99 rows=4,423,387 width=0) (actual time=2,929.559..2,929.559 rows=4,497,717 loops=1)

8. 0.420 0.420 ↑ 1.0 1 12

Index Scan using datos_bancarios_ach_detalle_pkey on datos_bancarios_ach_detalle d (cost=0.56..3.62 rows=1 width=50) (actual time=0.035..0.035 rows=1 loops=12)

  • Index Cond: ((datos_bancarios_ach_detalle_id)::text = (t.datos_bancarios_ach_detalle_base_id)::text)
  • Filter: ((((banco)::text = 'BANCOLOMBIA'::text) OR ((banco)::text <> 'BANCOLOMBIA'::text)) AND (((date_part('day'::text, (date_trunc('day'::text, ('now'::cstring)::timestamp without time zone) - date_trunc('day'::text, t.fecha_creacion))) = '5'::double precision) AND ((banco)::text = 'BANCOLOMBIA'::text)) OR ((date_part('day'::text, (date_trunc('day'::text, ('now'::cstring)::timestamp without time zone) - date_trunc('day'::text, t.fecha_creacion))) = '7'::double precision) AND ((banco)::text <> 'BANCOLOMBIA'::text))))
  • Rows Removed by Filter: 0
Planning time : 3.503 ms
Execution time : 33,010.102 ms