explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BZsQ

Settings
# exclusive inclusive rows x rows loops node
1. 52.099 35,424.068 ↑ 1.0 200 1

Subquery Scan on final (cost=2,804.90..2,277,779.89 rows=200 width=32) (actual time=554.964..35,424.068 rows=200 loops=1)

2. 0.126 35,371.969 ↑ 1.0 200 1

Limit (cost=2,713.73..2,276,095.48 rows=200 width=357) (actual time=550.665..35,371.969 rows=200 loops=1)

3. 0.619 35,371.843 ↑ 4.4 200 1

Nested Loop (cost=2,713.73..9,982,859.60 rows=878 width=357) (actual time=550.664..35,371.843 rows=200 loops=1)

  • Join Filter: ((mc_transacciones_ram.tx_codigo_comercio)::text = (lpad(((terminales_2.id_saldo_logico)::character varying)::text, 8, '0'::text)
  • Rows Removed by Join Filter: 72
4. 27,637.834 35,370.224 ↑ 95.7 200 1

Nested Loop (cost=1,833.44..9,685,234.36 rows=19,134 width=119) (actual time=549.695..35,370.224 rows=200 loops=1)

  • Join Filter: (terminales_1.numero_terminal = (mc_transacciones_ram.tx_codigo_terminal)::bigint)
  • Rows Removed by Join Filter: 184,166,552
  • -> Index Scan Backward using mc_transacciones_ram_02 on mc_transacciones_ram (cost=77.09..9008246.08 rows=11,253,570 width=119) (actual
  • Index Cond: ((tx_fecha_hora_in_mc >= '2020-10-01 00:00:00'::timestamp without time zone) AND (tx_fecha_hora_in_mc <= '2020-10-10 2
  • Filter: (((tx_estado_trx)::text <> 'DUPLICADA'::text) AND (((tx_mti_in)::text = '0200'::text) OR ((tx_mti_in)::text = '0202'::text
  • Rows Removed by Filter: 1,088,431
  • -> Seq Scan on ta_transacciones_web ta_transacciones_web_5 (cost=0.00..24.09 rows=6 width=14) (actual time=0.017..0.059 rows=1
  • Filter: ((cod_grupo = 4) AND (cod_subgrupo = 5))
  • Rows Removed by Filter: 337
  • -> Seq Scan on ta_transacciones_web ta_transacciones_web_6 (cost=0.00..24.09 rows=347 width=32) (actual time=0.028..0.181 rows
5. 7,728.716 7,732.197 ↓ 262.0 262 702,927

Materialize (cost=1,756.35..1,774.09 rows=1 width=16) (actual time=0.000..0.011 rows=262 loops=702,927)

6. 0.078 3.481 ↓ 262.0 262 1

Merge Join (cost=1,756.35..1,774.08 rows=1 width=16) (actual time=3.361..3.481 rows=262 loops=1)

  • Merge Cond: (terminales_1.numero_terminal = terminales.numero_terminal)
7. 0.033 3.109 ↑ 1.1 262 1

Unique (cost=878.17..879.59 rows=283 width=8) (actual time=3.065..3.109 rows=262 loops=1)

8. 3.076 3.076 ↑ 1.1 262 1

Sort (cost=878.17..878.88 rows=283 width=8) (actual time=3.064..3.076 rows=262 loops=1)

  • Sort Key: terminales_1.numero_terminal
  • Sort Method: quicksort Memory: 37kB
  • -> Bitmap Heap Scan on terminales terminales_1 (cost=9.61..866.65 rows=283 width=8) (actual time=0.144..2.933
  • Recheck Cond: ((rut_comercio)::text = '96648580-9'::text)
  • Heap Blocks: exact=199
  • -> Bitmap Index Scan on bo_terminales_new_04 (cost=0.00..9.54 rows=283 width=0) (actual time=0.103..0.10
  • Index Cond: ((rut_comercio)::text = '96648580-9'::text)
9. 0.083 0.294 ↑ 1.1 262 1

Sort (cost=878.17..878.88 rows=283 width=8) (actual time=0.283..0.294 rows=262 loops=1)

  • Sort Key: terminales.numero_terminal
  • Sort Method: quicksort Memory: 37kB
10. 0.211 0.211 ↑ 1.1 262 1

Bitmap Heap Scan on terminales (cost=9.61..866.65 rows=283 width=8) (actual time=0.059..0.211 rows=262 loops=1)

  • Recheck Cond: ((rut_comercio)::text = '96648580-9'::text)
  • Heap Blocks: exact=199
  • -> Bitmap Index Scan on bo_terminales_new_04 (cost=0.00..9.54 rows=283 width=0) (actual time=0.042..0.042 rows
  • Index Cond: ((rut_comercio)::text = '96648580-9'::text)
11.          

SubPlan (for Nested Loop)

12. 0.085 0.193 ↑ 1.0 16 1

HashAggregate (cost=26.63..27.43 rows=16 width=10) (actual time=0.191..0.193 rows=16 loops=1)

  • Group Key: tabla_convenios.con_rubro
13. 0.108 0.108 ↑ 1.0 393 1

Seq Scan on tabla_convenios (cost=0.00..25.65 rows=393 width=10) (actual time=0.032..0.108 rows=393 loops=1)

14. 0.015 1.000 ↑ 282.0 1 200

Materialize (cost=880.30..897.22 rows=282 width=32) (actual time=0.005..0.005 rows=1 loops=200)

15. 0.023 0.985 ↑ 16.6 17 1

Unique (cost=880.30..881.71 rows=282 width=32) (actual time=0.946..0.985 rows=17 loops=1)

16. 0.962 0.962 ↑ 1.3 224 1

Sort (cost=880.30..881.00 rows=283 width=32) (actual time=0.945..0.962 rows=224 loops=1)

  • Sort Key: (lpad(((terminales_2.id_saldo_logico)::character varying)::text, 8, '0'::text))
  • Sort Method: quicksort Memory: 37kB
  • -> Bitmap Heap Scan on terminales terminales_2 (cost=9.61..868.77 rows=283 width=32) (actual time=0.098..0.684 rows=262 lo
  • Recheck Cond: ((rut_comercio)::text = '96648580-9'::text)
  • Heap Blocks: exact=199
  • -> Bitmap Index Scan on bo_terminales_new_04 (cost=0.00..9.54 rows=283 width=0) (actual time=0.045..0.045 rows=262 l
  • Index Cond: ((rut_comercio)::text = '96648580-9'::text)
17.          

SubPlan (for Subquery Scan)

18. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on ta_transacciones_web (cost=3.74..9.40 rows=2 width=14) (never executed)

  • Recheck Cond: (cod_grupo = 2)
  • Filter: (cod_subgrupo = 2)
19. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on iddx_ta_transacciones_web (cost=0.00..3.74 rows=12 width=0) (never executed)

  • Index Cond: (cod_grupo = 2)
20. 0.000 0.000 ↓ 0.0 0

Seq Scan on ta_transacciones_web ta_transacciones_web_1 (cost=0.00..24.09 rows=14 width=14) (never executed)

  • Filter: ((cod_grupo = 4) AND (cod_subgrupo = 1))
21. 0.000 0.000 ↓ 0.0 0

Seq Scan on ta_transacciones_web ta_transacciones_web_2 (cost=0.00..24.09 rows=14 width=14) (never executed)

  • Filter: ((cod_grupo = 4) AND (cod_subgrupo = 1))
22. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on ta_transacciones_web ta_transacciones_web_3 (cost=3.74..9.40 rows=2 width=14) (never executed)

  • Recheck Cond: (cod_grupo = 2)
  • Filter: (cod_subgrupo = 2)
23. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on iddx_ta_transacciones_web (cost=0.00..3.74 rows=12 width=0) (never executed)

  • Index Cond: (cod_grupo = 2)
24. 0.000 0.000 ↓ 0.0 0

Seq Scan on ta_transacciones_web ta_transacciones_web_4 (cost=0.00..24.09 rows=14 width=14) (never executed)

  • Filter: ((cod_grupo = 4) AND (cod_subgrupo = 1))
Planning time : 8.360 ms
Execution time : 35,427.125 ms