explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.011 18,193.150 ↓ 7.0 7 1

GroupAggregate (cost=174,323.54..174,323.56 rows=1 width=12) (actual time=18,193.138..18,193.150 rows=7 loops=1)

  • Group Key: (date(t.fecha_creacion))
2. 0.036 18,193.139 ↓ 55.0 55 1

Sort (cost=174,323.54..174,323.54 rows=1 width=4) (actual time=18,193.131..18,193.139 rows=55 loops=1)

  • Sort Key: (date(t.fecha_creacion))
  • Sort Method: quicksort Memory: 27kB
3. 0.160 18,193.103 ↓ 55.0 55 1

Nested Loop (cost=130,754.92..174,323.53 rows=1 width=4) (actual time=17,989.148..18,193.103 rows=55 loops=1)

4. 11,826.839 18,192.118 ↓ 7.9 55 1

Bitmap Heap Scan on transaccion t (cost=130,754.36..174,307.43 rows=7 width=45) (actual time=17,989.104..18,192.118 rows=55 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 >= (('now'::cstring)::date - '10 days'::interval)) AND (date_part('day'::text, (date_trunc('day'::text, ('now'::cstring)::timestamp without time zone) - date_trunc('day'::text, fecha_creacion))) >= '4'::double precision))
  • Rows Removed by Filter: 4428461
  • Heap Blocks: exact=3757647
5. 1,374.880 6,365.279 ↓ 0.0 0 1

BitmapAnd (cost=130,754.36..130,754.36 rows=28,351 width=0) (actual time=6,365.279..6,365.279 rows=0 loops=1)

6. 2,373.288 2,373.288 ↓ 1.0 4,498,129 1

Bitmap Index Scan on idx_transaccion_medio_pago_rebuild_20200519 (cost=0.00..60,779.96 rows=4,424,118 width=0) (actual time=2,373.288..2,373.288 rows=4,498,129 loops=1)

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

Bitmap Index Scan on idx_transaccion_datos_bancarios_ach_detalle_ba_rebuild_20200520 (cost=0.00..69,974.15 rows=4,424,118 width=0) (actual time=2,617.111..2,617.111 rows=4,497,819 loops=1)

8. 0.825 0.825 ↑ 1.0 1 55

Index Only Scan using datos_bancarios_ach_detalle_pkey on datos_bancarios_ach_detalle d (cost=0.56..2.29 rows=1 width=37) (actual time=0.015..0.015 rows=1 loops=55)

  • Index Cond: (datos_bancarios_ach_detalle_id = (t.datos_bancarios_ach_detalle_base_id)::text)
  • Heap Fetches: 33
Planning time : 0.513 ms
Execution time : 18,231.637 ms