explain.depesz.com

PostgreSQL's explain analyze made readable

Result: H6MP

Settings
# exclusive inclusive rows x rows loops node
1. 1.563 9.677 ↓ 1.2 5 1

WindowAgg (cost=104.08..363.80 rows=4 width=1,170) (actual time=8.916..9.677 rows=5 loops=1)

2. 0.026 7.979 ↓ 1.2 5 1

Nested Loop (cost=104.08..341.23 rows=4 width=1,124) (actual time=5.362..7.979 rows=5 loops=1)

3. 0.045 5.263 ↓ 5.0 5 1

Nested Loop (cost=45.00..144.10 rows=1 width=1,096) (actual time=4.241..5.263 rows=5 loops=1)

4. 0.023 5.013 ↓ 5.0 5 1

Nested Loop (cost=44.72..133.92 rows=1 width=908) (actual time=4.130..5.013 rows=5 loops=1)

5. 0.031 4.670 ↓ 5.0 5 1

Nested Loop (cost=44.43..121.96 rows=1 width=832) (actual time=3.986..4.670 rows=5 loops=1)

  • Join Filter: (p.cod_prod_role = pr.cod_prod_role)
  • Rows Removed by Join Filter: 10
6. 0.081 4.614 ↓ 5.0 5 1

Nested Loop (cost=44.43..120.89 rows=1 width=824) (actual time=3.978..4.614 rows=5 loops=1)

  • Join Filter: (pggpb.cod_prod_use = pu.cod_prod_use)
  • Rows Removed by Join Filter: 15
7. 0.050 4.488 ↓ 5.0 5 1

Nested Loop (cost=44.43..119.80 rows=1 width=811) (actual time=3.950..4.488 rows=5 loops=1)

  • Join Filter: (pggpb.cod_prod_bid_type = pbt.cod_prod_bid_type)
  • Rows Removed by Join Filter: 30
8. 0.076 4.408 ↓ 5.0 5 1

Nested Loop (cost=44.43..118.64 rows=1 width=802) (actual time=3.942..4.408 rows=5 loops=1)

  • Join Filter: (p.cod_estado_comercial = ec.cod_estado_comercial)
  • Rows Removed by Join Filter: 10
9. 0.025 4.302 ↓ 5.0 5 1

Nested Loop (cost=44.43..117.57 rows=1 width=790) (actual time=3.932..4.302 rows=5 loops=1)

10. 0.057 4.232 ↓ 5.0 5 1

Nested Loop (cost=44.14..111.16 rows=1 width=736) (actual time=3.917..4.232 rows=5 loops=1)

  • Join Filter: (pggpb.cod_physical_state = pps_i18n.cod_physical_state)
  • Rows Removed by Join Filter: 40
11. 0.023 4.030 ↓ 5.0 5 1

Nested Loop (cost=44.00..110.32 rows=1 width=724) (actual time=3.845..4.030 rows=5 loops=1)

12. 0.023 3.977 ↓ 5.0 5 1

Nested Loop (cost=43.71..102.00 rows=1 width=711) (actual time=3.833..3.977 rows=5 loops=1)

13. 0.076 3.899 ↓ 2.5 5 1

Nested Loop (cost=43.29..88.69 rows=2 width=696) (actual time=3.814..3.899 rows=5 loops=1)

  • Join Filter: (pmkp.cod_state = mkppst.cod_state)
  • Rows Removed by Join Filter: 30
14. 0.022 0.022 ↑ 1.0 7 1

Seq Scan on mkp_prod_states mkppst (cost=0.00..1.07 rows=7 width=227) (actual time=0.015..0.022 rows=7 loops=1)

15. 0.074 3.801 ↓ 2.5 5 7

Materialize (cost=43.29..87.42 rows=2 width=473) (actual time=0.242..0.543 rows=5 loops=7)

16. 0.111 3.727 ↓ 2.5 5 1

Hash Join (cost=43.29..87.41 rows=2 width=473) (actual time=1.662..3.727 rows=5 loops=1)

  • Hash Cond: (pgt.cod_proveedor = pprov.cod_proveedor)
17. 0.079 3.555 ↑ 10.0 5 1

Nested Loop (cost=32.21..76.06 rows=50 width=473) (actual time=1.524..3.555 rows=5 loops=1)

18. 0.034 0.121 ↑ 1.0 5 1

Bitmap Heap Scan on prod_mkp pmkp (cost=4.32..20.76 rows=5 width=120) (actual time=0.106..0.121 rows=5 loops=1)

  • Recheck Cond: (cod_producto = 10001110)
  • Heap Blocks: exact=3
19. 0.087 0.087 ↑ 1.0 5 1

Bitmap Index Scan on pmkp_cpcmpkcsidx (cost=0.00..4.31 rows=5 width=0) (actual time=0.087..0.087 rows=5 loops=1)

  • Index Cond: (cod_producto = 10001110)
20. 0.115 3.355 ↑ 9.0 1 5

Hash Join (cost=27.90..33.29 rows=9 width=361) (actual time=0.524..0.671 rows=1 loops=5)

  • Hash Cond: ((cr.fromcur)::text = (t.cod_divisa)::text)
21. 1.065 1.065 ↑ 10.7 3 5

Seq Scan on currency_rules cr (cost=0.00..5.06 rows=32 width=17) (actual time=0.031..0.213 rows=3 loops=5)

  • Filter: (((tocur)::text = 'EUR'::text) AND (ruledatestart <= (now())::date) AND (COALESCE(ruledateend, now()) >= (now())::date))
  • Rows Removed by Filter: 99
22. 0.065 2.175 ↑ 4.0 1 5

Hash (cost=27.85..27.85 rows=4 width=348) (actual time=0.435..0.435 rows=1 loops=5)

  • Buckets: 1024 Batches: 1 Memory Usage: 5kB
23. 0.055 2.110 ↑ 4.0 1 5

Nested Loop (cost=1.20..27.85 rows=4 width=348) (actual time=0.412..0.422 rows=1 loops=5)

24. 0.030 0.030 ↑ 1.0 1 5

Index Scan using mkp_pk on marketplaces mkp (cost=0.13..2.55 rows=1 width=241) (actual time=0.005..0.006 rows=1 loops=5)

  • Index Cond: (cod_mkp = pmkp.cod_mkp)
25. 0.035 2.025 ↑ 10.0 1 5

Nested Loop (cost=1.07..25.20 rows=10 width=111) (actual time=0.398..0.405 rows=1 loops=5)

26. 0.025 0.025 ↑ 1.0 1 5

Index Scan using cod_tienda_pk on tiendas t (cost=0.15..5.50 rows=1 width=83) (actual time=0.005..0.005 rows=1 loops=5)

  • Index Cond: (cod_tienda = mkp.cod_tienda)
27. 0.075 1.965 ↑ 5.0 1 5

Nested Loop (cost=0.92..19.65 rows=5 width=31) (actual time=0.389..0.393 rows=1 loops=5)

  • -> Index Scan using productos_grupo_tarifas_pk on productos_grupo_tarifas pgt (cost=0.42..6.60 rows=1 width=23) (actual time=0.007.. (...)
  • Index Cond: ((cod_producto = 10001110) AND (cod_grupo_tarifas = t.cod_grupo_tarifas))
28. 1.890 1.890 ↑ 5.0 1 5

Function Scan on f_plazo_prod_cache pz (cost=0.50..13.00 rows=5 width=8) (actual time=0.378..0.378 rows=1 loops=5)

  • Filter: (cod_producto = 10001110)
29. 0.036 0.061 ↓ 2.0 4 1

Hash (cost=11.06..11.06 rows=2 width=12) (actual time=0.061..0.061 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 5kB
30. 0.025 0.025 ↓ 2.0 4 1

Index Scan using pp_cprod_idx on productos_proveedor pprov (cost=0.29..11.06 rows=2 width=12) (actual time=0.014..0.025 rows=4 loops=1)

  • Index Cond: (cod_producto = 10001110)
31. 0.055 0.055 ↑ 1.0 1 5

Index Scan using pggp_basic_pk on prod_grup_gp_basic pggpb (cost=0.42..6.65 rows=1 width=23) (actual time=0.010..0.011 rows=1 loops=5)

  • Index Cond: ((cod_producto = 10001110) AND (cod_grupo_prod_basic = t.cod_grupo_prod_basic))
  • Filter: ((t.cod_idioma)::text = (cod_idioma)::text)
32. 0.030 0.030 ↑ 1.0 1 5

Index Scan using cod_producto on productos p (cost=0.29..8.30 rows=1 width=17) (actual time=0.005..0.006 rows=1 loops=5)

  • Index Cond: (cod_producto = 10001110)
33. 0.145 0.145 ↑ 1.0 9 5

Index Scan using psi18n_cps_ci_pk on prod_physical_state_i18n pps_i18n (cost=0.14..0.73 rows=9 width=16) (actual time=0.018..0.029 rows=9 loops=5)

  • Index Cond: ((cod_idioma)::text = (t.cod_idioma)::text)
34. 0.045 0.045 ↑ 1.0 1 5

Index Scan using cod_productos_idiomas on productos_idiomas pi (cost=0.29..6.40 rows=1 width=70) (actual time=0.007..0.009 rows=1 loops=5)

  • Index Cond: ((cod_producto = 10001110) AND ((cod_idioma)::text = (t.cod_idioma)::text))
35. 0.030 0.030 ↑ 1.0 3 5

Seq Scan on estados_comerciales ec (cost=0.00..1.03 rows=3 width=16) (actual time=0.003..0.006 rows=3 loops=5)

36. 0.030 0.030 ↑ 1.0 7 5

Seq Scan on prod_bid_type pbt (cost=0.00..1.07 rows=7 width=13) (actual time=0.002..0.006 rows=7 loops=5)

37. 0.045 0.045 ↑ 1.0 4 5

Seq Scan on prods_uses pu (cost=0.00..1.04 rows=4 width=17) (actual time=0.006..0.009 rows=4 loops=5)

38. 0.025 0.025 ↑ 1.0 3 5

Seq Scan on prod_role pr (cost=0.00..1.03 rows=3 width=12) (actual time=0.002..0.005 rows=3 loops=5)

39. 0.160 0.320 ↑ 1.0 1 5

GroupAggregate (cost=0.29..11.95 rows=1 width=80) (actual time=0.063..0.064 rows=1 loops=5)

  • Group Key: pa.cod_producto
40. 0.160 0.160 ↑ 1.0 1 5

Index Scan using pa_cp_idx on productos_almacen pa (cost=0.29..11.90 rows=1 width=26) (actual time=0.029..0.032 rows=1 loops=5)

  • Index Cond: (cod_producto = p.cod_producto)
  • Filter: (cod_almacen = ANY (t.cod_alm_a))
  • Rows Removed by Filter: 13
41. 0.115 0.205 ↑ 1.0 1 5

GroupAggregate (cost=0.29..10.16 rows=1 width=196) (actual time=0.040..0.041 rows=1 loops=5)

  • Group Key: pwhs.cod_producto
42. 0.090 0.090 ↑ 1.0 1 5

Index Scan using pws_cpca_pk on prod_wh_stats pwhs (cost=0.29..10.10 rows=1 width=28) (actual time=0.012..0.018 rows=1 loops=5)

  • Index Cond: (cod_producto = p.cod_producto)
  • Filter: (cod_almacen = ANY (t.cod_alm_a))
  • Rows Removed by Filter: 12
43. 0.070 2.690 ↑ 4.0 1 5

Group (cost=59.07..197.05 rows=4 width=106) (actual time=0.530..0.538 rows=1 loops=5)

  • Group Key: pgt3.cod_grupo_tarifas, t_1.cod_grupo_tarifas, pp.precio_coste, pgt3.cod_producto, gt.cod_grupo_tarifas, t_1.cod_divisa, cr_1.facmul, cr_1.facdiv, pp.cod_proveedor
44.          

Initplan (forGroup)

45. 0.175 0.310 ↑ 1.0 1 5

Aggregate (cost=11.88..11.89 rows=1 width=8) (actual time=0.061..0.062 rows=1 loops=5)

46. 0.135 0.135 ↓ 7.0 14 5

Index Only Scan using pa_cp_idx on productos_almacen pa1 (cost=0.29..11.88 rows=2 width=4) (actual time=0.007..0.027 rows=14 loops=5)

  • Index Cond: (cod_producto = $6)
  • Heap Fetches: 70
47. 0.240 1.805 ↑ 2.0 2 5

Sort (cost=47.18..47.19 rows=4 width=80) (actual time=0.356..0.361 rows=2 loops=5)

  • Sort Key: t_1.cod_grupo_tarifas, pp.precio_coste, t_1.cod_divisa, cr_1.facmul, cr_1.facdiv, pp.cod_proveedor
  • Sort Method: quicksort Memory: 17kB
48. 0.098 1.565 ↑ 2.0 2 5

Hash Left Join (cost=6.17..47.14 rows=4 width=80) (actual time=0.192..0.313 rows=2 loops=5)

  • Hash Cond: ((t_1.cod_divisa)::text = (cr_1.fromcur)::text)
49. 0.060 1.205 ↑ 1.0 2 5

Nested Loop Left Join (cost=0.71..41.55 rows=2 width=67) (actual time=0.123..0.241 rows=2 loops=5)

  • Join Filter: (pgt3.cod_grupo_tarifas = t_1.cod_grupo_tarifas)
50. 0.025 0.210 ↑ 1.0 1 5

Nested Loop Left Join (cost=0.71..17.93 rows=1 width=59) (actual time=0.034..0.042 rows=1 loops=5)

51. 0.030 0.100 ↑ 1.0 1 5

Nested Loop (cost=0.42..9.61 rows=1 width=53) (actual time=0.016..0.020 rows=1 loops=5)

52. 0.035 0.035 ↑ 1.0 1 5

Seq Scan on grupo_tarifas gt (cost=0.00..1.16 rows=1 width=21) (actual time=0.005..0.007 rows=1 loops=5)

  • Filter: (cod_grupo_tarifas = pgt.cod_grupo_tarifas)
  • Rows Removed by Filter: 12
53. 0.035 0.035 ↑ 1.0 1 5

Index Scan using productos_grupo_tarifas_pk on productos_grupo_tarifas pgt3 (cost=0.42..8.44 rows=1 width=32) (actual time=0.006..0.007 rows=1 loops=5)

  • Index Cond: ((cod_producto = pmkp.cod_producto) AND (cod_grupo_tarifas = pgt.cod_grupo_tarifas))
54. 0.085 0.085 ↑ 1.0 1 5

Index Scan using pp_pk on productos_proveedor pp (cost=0.29..8.31 rows=1 width=14) (actual time=0.015..0.017 rows=1 loops=5)

  • Index Cond: ((pgt3.cod_producto = cod_producto) AND (cod_producto = pmkp.cod_producto) AND (cod_proveedor = pgt3.cod_proveedor))
55. 0.935 0.935 ↑ 1.0 2 5

Seq Scan on tiendas t_1 (cost=0.00..23.59 rows=2 width=8) (actual time=0.085..0.187 rows=2 loops=5)

  • Filter: ((cod_tienda <> 0) AND (cod_grupo_tarifas = pgt.cod_grupo_tarifas) AND (cod_tienda_padre = 0))
  • Rows Removed by Filter: 432
56. 0.031 0.262 ↑ 10.7 3 1

Hash (cost=5.06..5.06 rows=32 width=17) (actual time=0.262..0.262 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 5kB
57. 0.231 0.231 ↑ 10.7 3 1

Seq Scan on currency_rules cr_1 (cost=0.00..5.06 rows=32 width=17) (actual time=0.040..0.231 rows=3 loops=1)

  • Filter: (((tocur)::text = 'EUR'::text) AND (ruledatestart <= (now())::date) AND (COALESCE(ruledateend, now()) >= (now())::date))
  • Rows Removed by Filter: 99
58.          

SubPlan (forGroup)

59. 0.335 0.505 ↑ 1.0 1 5

Nested Loop (cost=0.71..18.18 rows=1 width=14) (actual time=0.093..0.101 rows=1 loops=5)

60. 0.030 0.140 ↑ 1.0 1 5

Nested Loop (cost=0.42..9.61 rows=1 width=12) (actual time=0.024..0.028 rows=1 loops=5)

61. 0.080 0.080 ↑ 1.0 1 5

Index Scan using productos_grupo_tarifas_pk on productos_grupo_tarifas pgt2 (cost=0.42..8.44 rows=1 width=16) (actual time=0.015..0.016 rows=1 loops=5)

  • Index Cond: ((cod_producto = pgt3.cod_producto) AND (cod_grupo_tarifas = t_1.cod_grupo_tarifas))
62. 0.030 0.030 ↑ 1.0 1 5

Seq Scan on grupo_tarifas gt_1 (cost=0.00..1.16 rows=1 width=4) (actual time=0.004..0.006 rows=1 loops=5)

  • Filter: (cod_grupo_tarifas = t_1.cod_grupo_tarifas)
  • Rows Removed by Filter: 12
63. 0.030 0.030 ↑ 1.0 1 5

Index Scan using ca_cp_pk on productos_almacen pa3 (cost=0.29..8.31 rows=1 width=6) (actual time=0.005..0.006 rows=1 loops=5)

  • Index Cond: ((cod_almacen = 1) AND (cod_producto = pgt3.cod_producto))
64. 0.000 0.000 ↓ 0.0 0

Seq Scan on currency_rules cr2 (cost=8.16..16.28 rows=11 width=32) (never executed)

  • Filter: (((fromcur)::text = ($18)::text) AND ((tocur)::text = 'EUR'::text) AND (ruledatestart <= (now())::date) AND (COALESCE(ruledateend, now()) >= (now())::date))
65.          

Initplan (forSeq Scan)

66. 0.000 0.000 ↓ 0.0 0

Index Scan using cod_proveedor_pk on proveedores p_1 (cost=0.14..8.16 rows=1 width=4) (never executed)

  • Index Cond: (cod_proveedor = pp.cod_proveedor)
67.          

SubPlan (forWindowAgg)

68. 0.135 0.135 ↓ 0.0 0 5

Seq Scan on prov_paises_entrega ppe (cost=0.00..4.62 rows=1 width=1) (actual time=0.025..0.027 rows=0 loops=5)

  • Filter: (envio_directo AND (cod_proveedor = pgt.cod_proveedor) AND ((cod_pais_entrega)::text = (t.cod_pais)::text))
  • Rows Removed by Filter: 175
Planning time : 43.542 ms
Execution time : 15.968 ms