explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.008 19,164.848 ↑ 1.0 1 1

GroupAggregate (cost=174,852.61..174,852.65 rows=1 width=33) (actual time=19,164.848..19,164.848 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.024 19,164.840 ↓ 10.0 10 1

Sort (cost=174,852.61..174,852.61 rows=1 width=25) (actual time=19,164.838..19,164.840 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.067 19,164.816 ↓ 10.0 10 1

Nested Loop (cost=130,735.64..174,852.60 rows=1 width=25) (actual time=18,996.910..19,164.816 rows=10 loops=1)

4. 13,110.402 19,164.449 ↓ 12.0 12 1

Bitmap Heap Scan on transaccion t (cost=130,735.08..174,848.95 rows=1 width=45) (actual time=18,996.839..19,164.449 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: 4428478
  • Heap Blocks: exact=3757617
5. 1,333.270 6,054.047 ↓ 0.0 0 1

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

6. 2,262.973 2,262.973 ↓ 1.0 4,498,049 1

Bitmap Index Scan on idx_transaccion_medio_pago_rebuild_20200519 (cost=0.00..60,769.59 rows=4,423,536 width=0) (actual time=2,262.973..2,262.973 rows=4,498,049 loops=1)

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

Bitmap Index Scan on idx_transaccion_datos_bancarios_ach_detalle_ba_rebuild_20200520 (cost=0.00..69,965.24 rows=4,423,536 width=0) (actual time=2,457.804..2,457.804 rows=4,497,739 loops=1)

8. 0.300 0.300 ↑ 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.024..0.025 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.663 ms
Execution time : 19,201.657 ms