explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Knhc : ach

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.003 22,915.056 ↓ 3.3 10 1

Unique (cost=178,144.74..178,144.78 rows=3 width=33) (actual time=22,915.052..22,915.056 rows=10 loops=1)

2. 0.021 22,915.053 ↓ 3.3 10 1

Sort (cost=178,144.74..178,144.75 rows=3 width=33) (actual time=22,915.052..22,915.053 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)))), (count(*))
  • Sort Method: quicksort Memory: 25kB
3. 0.640 22,915.032 ↓ 3.3 10 1

GroupAggregate (cost=178,144.60..178,144.72 rows=3 width=33) (actual time=22,914.170..22,915.032 rows=10 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))))
4. 2.614 22,914.392 ↓ 1,295.0 3,885 1

Sort (cost=178,144.60..178,144.60 rows=3 width=25) (actual time=22,914.116..22,914.392 rows=3,885 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: 400kB
5. 9.881 22,911.778 ↓ 1,295.0 3,885 1

Nested Loop (cost=130,717.67..178,144.57 rows=3 width=25) (actual time=21,217.803..22,911.778 rows=3,885 loops=1)

6. 15,313.726 21,572.509 ↓ 8.2 7,729 1

Bitmap Heap Scan on transaccion t (cost=130,717.11..174,755.45 rows=939 width=45) (actual time=20,972.073..21,572.509 rows=7,729 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 (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: 4420728
  • Heap Blocks: exact=3757579
7. 1,320.696 6,258.783 ↓ 0.0 0 1

BitmapAnd (cost=130,717.11..130,717.11 rows=28,344 width=0) (actual time=6,258.783..6,258.783 rows=0 loops=1)

8. 2,367.517 2,367.517 ↓ 1.0 4,497,937 1

Bitmap Index Scan on idx_transaccion_medio_pago_rebuild_20200519 (cost=0.00..60,759.73 rows=4,423,021 width=0) (actual time=2,367.517..2,367.517 rows=4,497,937 loops=1)

  • Index Cond: ((medio_pago)::text = 'BANCOLOMBIA_ACH_DEBIT'::text)
9. 2,570.570 2,570.570 ↓ 1.0 4,497,627 1

Bitmap Index Scan on idx_transaccion_datos_bancarios_ach_detalle_ba_rebuild_20200520 (cost=0.00..69,956.66 rows=4,423,021 width=0) (actual time=2,570.570..2,570.570 rows=4,497,627 loops=1)

10. 1,329.388 1,329.388 ↑ 1.0 1 7,729

Index Scan using datos_bancarios_ach_detalle_pkey on datos_bancarios_ach_detalle d (cost=0.56..3.60 rows=1 width=50) (actual time=0.171..0.172 rows=1 loops=7,729)

  • 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.615 ms
Execution time : 22,915.168 ms