explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.009 16,575.546 ↑ 1.0 1 1

GroupAggregate (cost=174,835.23..174,835.27 rows=1 width=33) (actual time=16,575.546..16,575.546 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.026 16,575.537 ↓ 10.0 10 1

Sort (cost=174,835.23..174,835.23 rows=1 width=25) (actual time=16,575.536..16,575.537 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.076 16,575.511 ↓ 10.0 10 1

Nested Loop (cost=130,721.38..174,835.22 rows=1 width=25) (actual time=16,418.362..16,575.511 rows=10 loops=1)

4. 10,640.226 16,575.183 ↓ 12.0 12 1

Bitmap Heap Scan on transaccion t (cost=130,720.82..174,831.57 rows=1 width=45) (actual time=16,418.305..16,575.183 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: 4428451
  • Heap Blocks: exact=3757587
5. 1,292.447 5,934.957 ↓ 0.0 0 1

BitmapAnd (cost=130,720.82..130,720.82 rows=28,345 width=0) (actual time=5,934.956..5,934.957 rows=0 loops=1)

6. 2,231.988 2,231.988 ↓ 1.0 4,497,965 1

Bitmap Index Scan on idx_transaccion_medio_pago_rebuild_20200519 (cost=0.00..60,761.94 rows=4,423,115 width=0) (actual time=2,231.988..2,231.988 rows=4,497,965 loops=1)

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

Bitmap Index Scan on idx_transaccion_datos_bancarios_ach_detalle_ba_rebuild_20200520 (cost=0.00..69,958.63 rows=4,423,115 width=0) (actual time=2,410.522..2,410.522 rows=4,497,658 loops=1)

8. 0.252 0.252 ↑ 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.021..0.021 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 : 0.606 ms
Execution time : 16,575.647 ms