explain.depesz.com

PostgreSQL's explain analyze made readable

Result: P80f

Settings
# exclusive inclusive rows x rows loops node
1. 16.697 1,459,086.601 ↓ 408.0 408 1

GroupAggregate (cost=399,942.46..399,942.49 rows=1 width=63) (actual time=1,459,062.526..1,459,086.601 rows=408 loops=1)

  • Group Key: tp.codigo_usuario_destino, rs.codigo_origen, p.tipo_producto, pcv.valor, tp.estado
2. 308.372 1,459,069.904 ↓ 52,582.0 52,582 1

Sort (cost=399,942.46..399,942.46 rows=1 width=39) (actual time=1,459,062.491..1,459,069.904 rows=52,582 loops=1)

  • Sort Key: tp.codigo_usuario_destino, rs.codigo_origen, p.tipo_producto, pcv.valor
  • Sort Method: external sort Disk: 2920kB
3. 916,251.720 1,458,761.532 ↓ 52,582.0 52,582 1

Merge Join (cost=266,596.94..399,942.45 rows=1 width=39) (actual time=3,136.987..1,458,761.532 rows=52,582 loops=1)

  • Merge Cond: ((rs.codigo_usuario_responsable)::text = (tp.codigo_usuario_destino)::text)
  • Join Filter: ((rs.serie_mecanica)::text = (tp.serie_mecanica)::text)
  • Rows Removed by Join Filter: 5483820536
4. 48.139 2,107.730 ↓ 1.9 99,717 1

Nested Loop (cost=1.55..1,335,924.27 rows=53,182 width=42) (actual time=0.698..2,107.730 rows=99,717 loops=1)

5. 56.571 1,760.440 ↓ 1.8 99,717 1

Nested Loop (cost=1.27..1,318,310.62 rows=55,288 width=54) (actual time=0.667..1,760.440 rows=99,717 loops=1)

6. 163.980 1,404.718 ↓ 1.5 99,717 1

Nested Loop (cost=0.85..1,267,012.86 rows=66,523 width=59) (actual time=0.567..1,404.718 rows=99,717 loops=1)

7. 742.153 742.153 ↓ 1.5 99,717 1

Index Scan using recu_series_codigo_usuario_responsable_i on recu_series rs (cost=0.43..1,222,729.96 rows=64,802 width=43) (actual time=0.495..742.153 rows=99,717 loops=1)

  • Filter: (((codigo_posesion)::text = ANY ('{NCLEO,DTHSA}'::text[])) AND ((tipo_serie)::text = ANY ('{EQU,NTB}'::text[])))
  • Rows Removed by Filter: 267603
8. 498.585 498.585 ↑ 1.0 1 99,717

Index Scan using prod_productos_caracteristicas_nombre_i on prod_productos_caracteristicas pc (cost=0.42..0.67 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=99,717)

  • Index Cond: ((producto_id = rs.producto_id) AND ((nombre)::text = 'CATEGORIA'::text))
9. 299.151 299.151 ↑ 1.0 1 99,717

Index Only Scan using prod_productos_caracteristicas_valores_valor_uk on prod_productos_caracteristicas_valores pcv (cost=0.42..0.76 rows=1 width=11) (actual time=0.003..0.003 rows=1 loops=99,717)

  • Index Cond: (producto_caracteristica_id = pc.producto_caracteristica_id)
  • Heap Fetches: 45009
10. 299.151 299.151 ↑ 1.0 1 99,717

Index Scan using prod_productos_pk on prod_productos p (cost=0.28..0.31 rows=1 width=12) (actual time=0.002..0.003 rows=1 loops=99,717)

  • Index Cond: (producto_id = rs.producto_id)
11. 536,808.167 540,402.082 ↓ 12,714.8 5,484,131,104 1

Materialize (cost=264,909.96..267,066.56 rows=431,320 width=42) (actual time=3,094.619..540,402.082 rows=5,484,131,104 loops=1)

12. 1,292.994 3,593.915 ↓ 1.6 678,794 1

Sort (cost=264,909.96..265,988.26 rows=431,320 width=42) (actual time=3,094.611..3,593.915 rows=678,794 loops=1)

  • Sort Key: tp.codigo_usuario_destino
  • Sort Method: external merge Disk: 36488kB
13. 2,164.608 2,300.921 ↓ 1.6 678,795 1

Bitmap Heap Scan on inve_traspasos_producto tp (cost=19,829.30..211,273.36 rows=431,320 width=42) (actual time=143.918..2,300.921 rows=678,795 loops=1)

  • Recheck Cond: ((estado_bloqueo)::text = 'PEND'::text)
  • Rows Removed by Index Recheck: 4578012
  • Filter: ((serie_mecanica IS NOT NULL) AND ((estado)::text = 'C'::text))
  • Rows Removed by Filter: 246434
  • Heap Blocks: exact=42681 lossy=105461
14. 136.313 136.313 ↓ 1.1 934,086 1

Bitmap Index Scan on inve_traspasos_producto_estado_bloqueo_i (cost=0.00..19,721.47 rows=883,871 width=0) (actual time=136.313..136.313 rows=934,086 loops=1)

  • Index Cond: ((estado_bloqueo)::text = 'PEND'::text)
Planning time : 12.849 ms
Execution time : 1,459,104.460 ms