explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EhMb

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Aggregate (cost=6,786.59..6,786.60 rows=1 width=0) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

HashAggregate (cost=6,786.54..6,786.57 rows=2 width=128) (actual rows= loops=)

  • Group Key: myo.ai_offer, myo.id_priority_level, myo.fl_presales
3. 0.000 0.000 ↓ 0.0

HashAggregate (cost=6,786.39..6,786.50 rows=2 width=324) (actual rows= loops=)

  • Group Key: myo.ai_offer, sco.id_product, myi.nm_product, myi.id_structure, COALESCE(myi.id_model_code, ''::character varying), myi.id_brand, myo.id_priority_level, myo.fl_presales
  • Filter: (array_to_string(array_agg(sco.fl_exclusion), ';'::text) !~~ '%Y%'::text)
4. 0.000 0.000 ↓ 0.0

Group (cost=6,786.26..6,786.32 rows=2 width=324) (actual rows= loops=)

  • Group Key: myo.ai_offer, myo.id_priority_level, sco.id_product, myi.nm_product, myi.id_structure, myi.id_model_code, myi.id_brand, sco.ai_offer_scope, myo.fl_presales, sco.fl_exclusion
5.          

CTE my_offer

6. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,661.26..3,345.13 rows=273 width=13) (actual rows= loops=)

  • Hash Cond: (fl_offer.ai_offer = tih.ai_offer)
  • Filter: ((tim.ai_offer IS NULL) OR (tih.ai_offer IS NOT NULL))
7. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,656.00..3,338.16 rows=273 width=20) (actual rows= loops=)

  • Hash Cond: (fl_offer.ai_offer = tim.ai_offer)
8. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,651.70..3,330.78 rows=273 width=13) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=1,651.28..1,673.76 rows=625 width=13) (actual rows= loops=)

  • Hash Cond: (ocr.ai_offer = fl_offer.ai_offer)
  • Filter: ((ocr.ai_offer IS NULL) OR ((cr.id_cashregister)::text = '0031'::text))
10. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=1.68..21.43 rows=433 width=12) (actual rows= loops=)

  • Hash Cond: (cr.ai_cr_group = ocr.ai_cr_group)
11. 0.000 0.000 ↓ 0.0

Index Only Scan using fl_cashregister_pkey on fl_cashregister cr (cost=0.28..14.27 rows=114 width=10) (actual rows= loops=)

  • Index Cond: (id_shop = '008'::text)
12. 0.000 0.000 ↓ 0.0

Hash (cost=1.18..1.18 rows=18 width=12) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Seq Scan on fl_offer_cashregister ocr (cost=0.00..1.18 rows=18 width=12) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Hash (cost=1,618.15..1,618.15 rows=2,516 width=13) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Seq Scan on fl_offer (cost=0.00..1,618.15 rows=2,516 width=13) (actual rows= loops=)

  • Filter: (('2019-02-05'::date >= ts_validity_start) AND ('2019-02-05'::date <= ts_validity_end) AND (id_offer_level = 4::numeric) AND ((cd_type)::text = 'OP'::text) AND ((cd_creation_status)::text = 'PROD'::text) AND ((fl_web)::text = 'A'::text))
16. 0.000 0.000 ↓ 0.0

Index Scan using fl_offer_shop_pkey on fl_offer_shop sho_ofr (cost=0.42..2.64 rows=1 width=6) (actual rows= loops=)

  • Index Cond: ((ai_offer = fl_offer.ai_offer) AND ((id_shop)::text = '008'::text))
  • Filter: ((fl_exclusion)::text = 'N'::text)
17. 0.000 0.000 ↓ 0.0

Hash (cost=4.01..4.01 rows=23 width=7) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Seq Scan on fl_validity_time tim (cost=0.00..4.01 rows=23 width=7) (actual rows= loops=)

  • Filter: (wd_day = 3::numeric)
19. 0.000 0.000 ↓ 0.0

Hash (cost=5.22..5.22 rows=3 width=7) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Seq Scan on fl_validity_time tih (cost=0.00..5.22 rows=3 width=7) (actual rows= loops=)

  • Filter: (('12:00:00'::time without time zone >= tm_starttime) AND ('12:00:00'::time without time zone <= tm_endtime) AND (tm_endtime <> '00:00:00'::time without time zone) AND (wd_day = 3::numeric))
21.          

CTE my_item

22. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.86..30.77 rows=2 width=82) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Index Scan using pk_item on rf_item rif (cost=0.43..13.84 rows=2 width=80) (actual rows= loops=)

  • Index Cond: ((id_product)::text = ANY ('{8836089,8837493}'::text[]))
24. 0.000 0.000 ↓ 0.0

Index Scan using pk_item_nuukik_universe on rf_item_nuukik_universe nuu_2 (cost=0.43..8.45 rows=1 width=10) (actual rows= loops=)

  • Index Cond: ((rif.id_product)::text = (id_product)::text)
25. 0.000 0.000 ↓ 0.0

Sort (cost=3,410.37..3,410.37 rows=2 width=324) (actual rows= loops=)

  • Sort Key: myo.ai_offer, myo.id_priority_level, sco.id_product, myi.nm_product, myi.id_structure, myi.id_model_code, myi.id_brand, sco.ai_offer_scope, myo.fl_presales, sco.fl_exclusion
26. 0.000 0.000 ↓ 0.0

Hash Anti Join (cost=3,410.17..3,410.36 rows=2 width=324) (actual rows= loops=)

  • Hash Cond: (sco.ai_offer_scope = scd.ai_offer_scope)
27. 0.000 0.000 ↓ 0.0

HashAggregate (cost=3,408.92..3,408.99 rows=7 width=192) (actual rows= loops=)

  • Group Key: myo.ai_offer, myo.id_priority_level, myo.fl_presales, sco.id_product, myi.nm_product, myi.id_structure, myi.id_model_code, myi.id_brand, sco.ai_offer_scope, sco.fl_exclusion
28. 0.000 0.000 ↓ 0.0

Append (cost=71.24..3,408.75 rows=7 width=192) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Hash Join (cost=71.24..78.07 rows=1 width=183) (actual rows= loops=)

  • Hash Cond: (myo.ai_offer = sco.ai_offer)
30. 0.000 0.000 ↓ 0.0

CTE Scan on my_offer myo (cost=0.00..5.46 rows=273 width=36) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Hash (cost=71.10..71.10 rows=11 width=154) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Nested Loop (cost=4.48..71.10 rows=11 width=154) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

CTE Scan on my_item myi (cost=0.00..0.04 rows=2 width=164) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on fl_offer_scope sco (cost=4.48..35.47 rows=6 width=22) (actual rows= loops=)

  • Recheck Cond: ((id_product)::text = (myi.id_product)::text)
  • Filter: ((cd_scope)::text = 'PRODUCT'::text)
35. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on fl_offer_scope_id_product (cost=0.00..4.48 rows=8 width=0) (actual rows= loops=)

  • Index Cond: ((id_product)::text = (myi.id_product)::text)
36. 0.000 0.000 ↓ 0.0

Nested Loop (cost=17.38..31.42 rows=1 width=208) (actual rows= loops=)

  • Join Filter: (nuu.ai_offer_scope = sco_1.ai_offer_scope)
37. 0.000 0.000 ↓ 0.0

Hash Join (cost=16.96..23.45 rows=1 width=214) (actual rows= loops=)

  • Hash Cond: (myo_1.ai_offer = nuu.ai_offer)
38. 0.000 0.000 ↓ 0.0

CTE Scan on my_offer myo_1 (cost=0.00..5.46 rows=273 width=36) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Hash (cost=16.95..16.95 rows=1 width=178) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..16.95 rows=1 width=178) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

CTE Scan on my_item myi_1 (cost=0.00..0.04 rows=2 width=164) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Index Scan using fl_offer_nuukik_pkey on fl_offer_nuukik nuu (cost=0.42..8.44 rows=1 width=21) (actual rows= loops=)

  • Index Cond: ((id_customer = 1141::numeric) AND ((cd_scope)::text = 'PRODUCT'::text) AND ((id_product)::text = (myi_1.id_product)::text))
43. 0.000 0.000 ↓ 0.0

Index Scan using ix_offer_scope_scope on fl_offer_scope sco_1 (cost=0.42..7.96 rows=1 width=15) (actual rows= loops=)

  • Index Cond: ((ai_offer = myo_1.ai_offer) AND ((cd_scope)::text = 'PRODUCT_NUUKIK'::text))
44. 0.000 0.000 ↓ 0.0

Nested Loop (cost=8.87..22.98 rows=1 width=208) (actual rows= loops=)

  • Join Filter: ((nuu_1.id_product)::text = (myi_2.id_nuukik_universe)::text)
45. 0.000 0.000 ↓ 0.0

Nested Loop (cost=8.87..22.92 rows=1 width=51) (actual rows= loops=)

  • Join Filter: (nuu_1.ai_offer_scope = sco_2.ai_offer_scope)
46. 0.000 0.000 ↓ 0.0

Hash Join (cost=8.45..14.94 rows=1 width=57) (actual rows= loops=)

  • Hash Cond: (myo_2.ai_offer = nuu_1.ai_offer)
47. 0.000 0.000 ↓ 0.0

CTE Scan on my_offer myo_2 (cost=0.00..5.46 rows=273 width=36) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Hash (cost=8.44..8.44 rows=1 width=21) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Index Scan using fl_offer_nuukik_pkey on fl_offer_nuukik nuu_1 (cost=0.42..8.44 rows=1 width=21) (actual rows= loops=)

  • Index Cond: ((id_customer = 1141::numeric) AND ((cd_scope)::text = 'NUUKIK_UNIVERSE'::text))
50. 0.000 0.000 ↓ 0.0

Index Scan using ix_offer_scope_scope on fl_offer_scope sco_2 (cost=0.42..7.96 rows=1 width=15) (actual rows= loops=)

  • Index Cond: ((ai_offer = myo_2.ai_offer) AND ((cd_scope)::text = 'PRODUCT_NUUKIK'::text))
51. 0.000 0.000 ↓ 0.0

CTE Scan on my_item myi_2 (cost=0.00..0.04 rows=2 width=196) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..3,198.64 rows=1 width=208) (actual rows= loops=)

  • Join Filter: ((COALESCE(sco_3.cd_bio, myi_3.cd_bio) = myi_3.cd_bio) AND (COALESCE(sco_3.cd_quick_sale, myi_3.cd_quick_sale) = myi_3.cd_quick_sale) AND (((sco_3.id_product)::text = substr((myi_3.id_structure)::text, 1, length((sco_3.id_product)::text))) OR ((sco_3.id_product)::text = 'ALL'::text)) AND ("position"(((myi_3.id_supplier)::text || '|'::text), ((COALESCE(sco_3.id_supplier, myi_3.id_supplier))::text || '|'::text)) > 0) AND ("position"(((COALESCE(sco_3.ty_product, myi_3.cd_product_type))::text || '|'::text), ((myi_3.cd_product_type)::text || '|'::text)) > 0) AND ("position"(((COALESCE(sco_3.id_brand, (myi_3.id_brand)::character varying))::text || '|'::text), (myi_3.id_brand || '|'::text)) > 0))
53. 0.000 0.000 ↓ 0.0

CTE Scan on my_item myi_3 (cost=0.00..0.04 rows=2 width=250) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Materialize (cost=0.42..3,124.69 rows=657 width=74) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..3,121.40 rows=657 width=74) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

CTE Scan on my_offer myo_3 (cost=0.00..5.46 rows=273 width=36) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Index Scan using ix_offer_scope_scope on fl_offer_scope sco_3 (cost=0.42..11.39 rows=2 width=45) (actual rows= loops=)

  • Index Cond: ((ai_offer = myo_3.ai_offer) AND ((cd_scope)::text = 'STRUCTURE'::text))
58. 0.000 0.000 ↓ 0.0

Hash Join (cost=71.02..77.52 rows=1 width=208) (actual rows= loops=)

  • Hash Cond: (myo_4.ai_offer = sco_4.ai_offer)
59. 0.000 0.000 ↓ 0.0

CTE Scan on my_offer myo_4 (cost=0.00..5.46 rows=273 width=36) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Hash (cost=71.00..71.00 rows=2 width=179) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Nested Loop (cost=4.48..71.00 rows=2 width=179) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

CTE Scan on my_item myi_4 (cost=0.00..0.04 rows=2 width=164) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on fl_offer_scope sco_4 (cost=4.48..35.47 rows=1 width=22) (actual rows= loops=)

  • Recheck Cond: ((id_product)::text = (myi_4.id_model_code)::text)
  • Filter: ((cd_scope)::text = 'MODEL_CODE'::text)
64. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on fl_offer_scope_id_product (cost=0.00..4.48 rows=8 width=0) (actual rows= loops=)

  • Index Cond: ((id_product)::text = (myi_4.id_model_code)::text)
65. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 6 (cost=0.00..0.06 rows=2 width=164) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

CTE Scan on my_item myi_5 (cost=0.00..0.04 rows=2 width=164) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

Hash (cost=1.19..1.19 rows=5 width=7) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

Seq Scan on fl_offer_scope_deactivation scd (cost=0.00..1.19 rows=5 width=7) (actual rows= loops=)

  • Filter: ((ts_deactivation IS NOT NULL) AND ((id_shop)::text = '008'::text))