explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6fa5

Settings
# exclusive inclusive rows x rows loops node
1. 17.716 6,002.831 ↓ 3.6 5,088 1

Sort (cost=64,929.13..64,932.65 rows=1,406 width=290) (actual time=5,998.945..6,002.831 rows=5,088 loops=1)

  • Sort Key: ps.w1 DESC
  • Sort Method: quicksort Memory: 917kB
2. 89.189 5,985.115 ↓ 3.6 5,088 1

Nested Loop (cost=1,891.96..64,855.62 rows=1,406 width=290) (actual time=81.570..5,985.115 rows=5,088 loops=1)

  • Join Filter: (p.cod_producto = pi.cod_producto)
3. 9.360 395.798 ↓ 3.6 5,088 1

Nested Loop (cost=1,891.67..7,894.62 rows=1,395 width=44) (actual time=80.357..395.798 rows=5,088 loops=1)

4. 0.017 0.017 ↑ 1.0 1 1

Index Only Scan using cod_tienda_pk on tiendas t (cost=0.15..6.57 rows=1 width=4) (actual time=0.014..0.017 rows=1 loops=1)

  • Index Cond: (cod_tienda = 2)
  • Heap Fetches: 1
5. 20.328 386.421 ↓ 3.6 5,088 1

Nested Loop (cost=1,891.52..7,874.10 rows=1,395 width=44) (actual time=80.335..386.421 rows=5,088 loops=1)

  • Join Filter: (p.cod_producto = pgi.cod_producto)
6. 18.278 325.389 ↓ 3.6 5,088 1

Nested Loop (cost=1,891.10..6,698.00 rows=1,427 width=40) (actual time=80.317..325.389 rows=5,088 loops=1)

  • Join Filter: (p.cod_producto = pgt.cod_producto)
7. 11.503 271.495 ↓ 3.5 5,088 1

Hash Join (cost=1,890.68..5,356.38 rows=1,474 width=36) (actual time=80.297..271.495 rows=5,088 loops=1)

  • Hash Cond: (pggb.cod_prod_bid_type = pbt.cod_prod_bid_type)
8. 22.954 259.970 ↓ 3.5 5,088 1

Nested Loop (cost=1,889.73..5,349.21 rows=1,474 width=40) (actual time=80.265..259.970 rows=5,088 loops=1)

  • Join Filter: (p.cod_producto = pggb.cod_producto)
9. 12.276 201.400 ↓ 3.5 5,088 1

Hash Join (cost=1,889.31..3,977.38 rows=1,460 width=32) (actual time=80.246..201.400 rows=5,088 loops=1)

  • Hash Cond: (p.cod_marca = m.cod_marca)
10. 28.542 188.826 ↓ 3.5 5,088 1

Nested Loop (cost=1,883.59..3,967.74 rows=1,460 width=36) (actual time=79.941..188.826 rows=5,088 loops=1)

  • Join Filter: (p.cod_producto = pa.cod_producto)
11. 22.522 115.268 ↓ 3.6 5,627 1

Hash Join (cost=1,883.30..3,300.19 rows=1,562 width=32) (actual time=79.912..115.268 rows=5,627 loops=1)

  • Hash Cond: (pts.cod_producto = p.cod_producto)
12. 13.022 14.097 ↑ 1.0 10,721 1

Bitmap Heap Scan on prod_tienda_stats pts (cost=165.22..1,525.39 rows=10,958 width=8) (actual time=1.165..14.097 rows=10,721 loops=1)

  • Recheck Cond: (cod_tienda = 2)
  • Heap Blocks: exact=411
13. 1.075 1.075 ↑ 1.0 10,721 1

Bitmap Index Scan on pts_ct_idx (cost=0.00..162.48 rows=10,958 width=0) (actual time=1.075..1.075 rows=10,721 loops=1)

  • Index Cond: (cod_tienda = 2)
14. 6.347 78.649 ↓ 2.5 5,627 1

Hash (cost=1,689.92..1,689.92 rows=2,253 width=24) (actual time=78.649..78.649 rows=5,627 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 274kB
15. 19.363 72.302 ↓ 2.5 5,627 1

Hash Join (cost=1,269.26..1,689.92 rows=2,253 width=24) (actual time=40.259..72.302 rows=5,627 loops=1)

  • Hash Cond: (ps.cod_producto = p.cod_producto)
16. 12.722 12.722 ↑ 1.0 15,806 1

Seq Scan on prod_stats ps (cost=0.00..338.86 rows=15,806 width=8) (actual time=0.008..12.722 rows=15,806 loops=1)

17. 5.947 40.217 ↓ 2.5 5,627 1

Hash (cost=1,241.09..1,241.09 rows=2,253 width=16) (actual time=40.213..40.217 rows=5,627 loops=1)

  • Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 230kB
18. 10.621 34.270 ↓ 2.5 5,627 1

Hash Join (cost=717.97..1,241.09 rows=2,253 width=16) (actual time=16.634..34.270 rows=5,627 loops=1)

  • Hash Cond: (pgv.cod_producto = p.cod_producto)
19. 7.120 7.835 ↓ 1.0 5,665 1

Bitmap Heap Scan on productos_grupo_visibilidades pgv (cost=93.30..602.20 rows=5,418 width=4) (actual time=0.803..7.835 rows=5,665 loops=1)

  • Recheck Cond: (cod_grupo_visibilidades = 1)
  • Filter: ((visibilidad)::text = 'S'::text)
  • Rows Removed by Filter: 636
  • Heap Blocks: exact=471
20. 0.715 0.715 ↓ 1.0 6,301 1

Bitmap Index Scan on pgv_prod (cost=0.00..91.94 rows=6,247 width=0) (actual time=0.715..0.715 rows=6,301 loops=1)

  • Index Cond: (cod_grupo_visibilidades = 1)
21. 6.143 15.814 ↑ 1.0 6,574 1

Hash (cost=542.49..542.49 rows=6,574 width=12) (actual time=15.813..15.814 rows=6,574 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 238kB
22. 8.927 9.671 ↑ 1.0 6,574 1

Bitmap Heap Scan on productos p (cost=96.32..542.49 rows=6,574 width=12) (actual time=0.811..9.671 rows=6,574 loops=1)

  • Recheck Cond: (cod_estado_comercial = ANY ('{1,3}'::integer[]))
  • Heap Blocks: exact=422
23. 0.744 0.744 ↑ 1.0 6,574 1

Bitmap Index Scan on p_est_com_idx (cost=0.00..94.67 rows=6,574 width=0) (actual time=0.743..0.744 rows=6,574 loops=1)

  • Index Cond: (cod_estado_comercial = ANY ('{1,3}'::integer[]))
24. 45.016 45.016 ↑ 1.0 1 5,627

Index Only Scan using ca_cp_pk on productos_almacen pa (cost=0.29..0.41 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=5,627)

  • Index Cond: ((cod_almacen = 1) AND (cod_producto = ps.cod_producto))
  • Heap Fetches: 5088
25. 0.152 0.298 ↑ 1.0 183 1

Hash (cost=3.43..3.43 rows=183 width=4) (actual time=0.297..0.298 rows=183 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 0.146 0.146 ↑ 1.0 183 1

Seq Scan on marcas m (cost=0.00..3.43 rows=183 width=4) (actual time=0.007..0.146 rows=183 loops=1)

27. 35.616 35.616 ↑ 1.0 1 5,088

Index Scan using pggp_basic_pk on prod_grup_gp_basic pggb (cost=0.42..0.93 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=5,088)

  • Index Cond: ((cod_producto = ps.cod_producto) AND (cod_grupo_prod_basic = 1))
28. 0.010 0.022 ↑ 1.0 7 1

Hash (cost=0.87..0.87 rows=7 width=4) (actual time=0.021..0.022 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 5kB
29. 0.012 0.012 ↑ 1.0 7 1

Seq Scan on prod_bid_type pbt (cost=0.00..0.87 rows=7 width=4) (actual time=0.006..0.012 rows=7 loops=1)

30. 35.616 35.616 ↑ 1.0 1 5,088

Index Only Scan using productos_grupo_tarifas_pk on productos_grupo_tarifas pgt (cost=0.42..0.90 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=5,088)

  • Index Cond: ((cod_producto = pggb.cod_producto) AND (cod_grupo_tarifas = 1))
  • Heap Fetches: 5088
31. 40.704 40.704 ↑ 1.0 1 5,088

Index Only Scan using cod_productos_grupo_importancias_pk on productos_grupo_importancias pgi (cost=0.42..0.81 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=5,088)

  • Index Cond: ((cod_producto = pggb.cod_producto) AND (cod_grupo_importancias = 1))
  • Heap Fetches: 5088
32. 40.704 40.704 ↑ 1.0 1 5,088

Index Scan using cod_productos_idiomas on productos_idiomas pi (cost=0.29..2.27 rows=1 width=48) (actual time=0.008..0.008 rows=1 loops=5,088)

  • Index Cond: ((cod_producto = pggb.cod_producto) AND ((cod_idioma)::text = 'es'::text))
33.          

SubPlan (forNested Loop)

34. 1,836.768 1,836.768 ↑ 5.0 1 5,088

Function Scan on getcamino c (cost=0.25..12.75 rows=5 width=78) (actual time=0.360..0.361 rows=1 loops=5,088)

  • Filter: (profundidad = 1)
  • Rows Removed by Filter: 2
35. 1,811.328 1,811.328 ↑ 5.0 1 5,088

Function Scan on getcamino c_1 (cost=0.25..12.75 rows=5 width=78) (actual time=0.354..0.356 rows=1 loops=5,088)

  • Filter: (profundidad = 2)
  • Rows Removed by Filter: 2
36. 1,811.328 1,811.328 ↑ 5.0 1 5,088

Function Scan on getcamino c_2 (cost=0.25..12.75 rows=5 width=78) (actual time=0.355..0.356 rows=1 loops=5,088)

  • Filter: (profundidad = 3)
  • Rows Removed by Filter: 2
Planning time : 24.539 ms
Execution time : 6,008.021 ms