explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.008 7,230.728 ↑ 1.0 1 1

GroupAggregate (cost=252,389.60..252,389.64 rows=1 width=33) (actual time=7,230.728..7,230.728 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.          

CTE tx_ach

3. 164.223 7,217.303 ↓ 388.6 8,161 1

Bitmap Heap Scan on transaccion t_1 (cost=166,431.50..252,384.79 rows=21 width=45) (actual time=7,083.326..7,217.303 rows=8,161 loops=1)

  • Recheck Cond: (((medio_pago)::text = 'BANCOLOMBIA_ACH_DEBIT'::text) AND (fecha_creacion >= (('now'::cstring)::date - '10 days'::interval)))
  • Filter: (((tipo)::text = ANY ('{AUTHORIZATION,AUTHORIZATION_AND_CAPTURE}'::text[])) AND ((state)::text = 'PENDING'::text))
  • Rows Removed by Filter: 50731
  • Heap Blocks: exact=60170
4. 1,249.347 7,053.080 ↓ 0.0 0 1

BitmapAnd (cost=166,431.50..166,431.50 rows=56,737 width=0) (actual time=7,053.080..7,053.080 rows=0 loops=1)

5. 2,201.854 2,201.854 ↓ 1.0 4,498,055 1

Bitmap Index Scan on idx_transaccion_medio_pago_rebuild_20200519 (cost=0.00..60,770.06 rows=4,423,598 width=0) (actual time=2,201.853..2,201.854 rows=4,498,055 loops=1)

  • Index Cond: ((medio_pago)::text = 'BANCOLOMBIA_ACH_DEBIT'::text)
6. 3,601.879 3,601.879 ↓ 1.4 12,585,430 1

Bitmap Index Scan on idx_transaccion_fecha_creacion_rebuild_20200509 (cost=0.00..105,661.18 rows=8,853,679 width=0) (actual time=3,601.879..3,601.879 rows=12,585,430 loops=1)

  • Index Cond: (fecha_creacion >= (('now'::cstring)::date - '10 days'::interval))
7. 0.019 7,230.720 ↓ 10.0 10 1

Sort (cost=4.81..4.82 rows=1 width=25) (actual time=7,230.718..7,230.720 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
8. 0.070 7,230.701 ↓ 10.0 10 1

Nested Loop (cost=0.56..4.80 rows=1 width=25) (actual time=7,092.882..7,230.701 rows=10 loops=1)

9. 7,229.985 7,229.985 ↓ 38.0 38 1

CTE Scan on tx_ach t (cost=0.00..1.16 rows=1 width=98) (actual time=7,092.826..7,229.985 rows=38 loops=1)

  • Filter: ((date_part('day'::text, (date_trunc('day'::text, ('now'::cstring)::timestamp without time zone) - date_trunc('day'::text, fecha_creacion))) = '4'::double precision) OR (date_part('day'::text, (date_trunc('day'::text, ('now'::cstring)::timestamp without time zone) - date_trunc('day'::text, fecha_creacion))) = '6'::double precision))
  • Rows Removed by Filter: 8123
10. 0.646 0.646 ↓ 0.0 0 38

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.017..0.017 rows=0 loops=38)

  • 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))) = '4'::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))) = '6'::double precision) AND ((banco)::text <> 'BANCOLOMBIA'::text))))
  • Rows Removed by Filter: 1
Planning time : 0.381 ms
Execution time : 7,230.903 ms