explain.depesz.com

PostgreSQL's explain analyze made readable

Result: R3bM

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

Merge Right Join (cost=519,252.27..1,704,750.61 rows=3,362,830 width=1,631) (actual rows= loops=)

  • Merge Cond: (product_popularity.product_id = p.product_id)
2.          

CTE product_popularity

3. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=0.29..1,943.84 rows=20,153 width=12) (actual rows= loops=)

  • Group Key: pp.product_id
4. 0.000 0.000 ↓ 0.0

Index Scan using product_popularity_pkey on product_popularity pp (cost=0.29..1,543.02 rows=29,782 width=12) (actual rows= loops=)

5.          

CTE p

6. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=407,954.56..466,240.47 rows=33,373 width=1,964) (actual rows= loops=)

  • Group Key: pp_1.product_id
7. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=407,954.56..425,388.13 rows=305,460 width=1,964) (actual rows= loops=)

  • Merge Cond: (pp_1.product_id = ppa.product_id)
  • Join Filter: (ps.shop_id = ppa.shop_id)
8. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=407,952.88..420,345.67 rows=305,460 width=1,960) (actual rows= loops=)

  • Merge Cond: (pp_1.product_id = p_price.product_id)
9. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=407,945.25..410,079.37 rows=305,460 width=1,946) (actual rows= loops=)

  • Merge Cond: (pp_1.product_id = ps.product_id)
10. 0.000 0.000 ↓ 0.0

Sort (cost=402,743.54..403,507.19 rows=305,460 width=1,942) (actual rows= loops=)

  • Sort Key: pp_1.product_id
11. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=67,270.42..118,078.18 rows=305,460 width=1,942) (actual rows= loops=)

  • Hash Cond: (pv_manufacturer.value_id = pvs_manufacturer.value_id)
12. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=67,265.59..115,394.00 rows=305,460 width=1,928) (actual rows= loops=)

  • Hash Cond: (pptv.value_id = pv_brand.value_id)
13. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=64,449.14..111,232.73 rows=305,460 width=1,749) (actual rows= loops=)

  • Hash Cond: (pptv.value_id = pv_manufacturer.value_id)
14. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=61,692.66..107,100.97 rows=305,460 width=1,580) (actual rows= loops=)

  • Hash Cond: (pptv.value_id = pv.value_id)
15. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=57,361.50..98,569.74 rows=305,460 width=1,401) (actual rows= loops=)

  • Hash Cond: ((pptv_type.value_id = pv_type.value_id) AND (ppitop.property_id = pv_type.property_id))
16. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=52,883.86..91,796.68 rows=305,460 width=1,230) (actual rows= loops=)

  • Hash Cond: (pptv_type.property_id = ppitop.property_id)
17. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=52,879.55..89,020.17 rows=305,460 width=1,230) (actual rows= loops=)

  • Hash Cond: (pptv_type.product_id = pp_1.product_id)
18. 0.000 0.000 ↓ 0.0

Seq Scan on product_to_value pptv_type (cost=0.00..25,300.24 rows=1,245,724 width=12) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Hash (cost=51,727.19..51,727.19 rows=92,189 width=1,222) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=19,566.00..51,727.19 rows=92,189 width=1,222) (actual rows= loops=)

  • Hash Cond: (pptv.property_id = ppr.property_id)
21. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=19,526.96..50,420.55 rows=92,189 width=1,131) (actual rows= loops=)

  • Hash Cond: (pptv.product_id = pp_1.product_id)
22. 0.000 0.000 ↓ 0.0

Seq Scan on product_to_value pptv (cost=0.00..25,300.24 rows=1,245,724 width=12) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Hash (cost=19,179.17..19,179.17 rows=27,823 width=1,123) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=10,838.88..19,179.17 rows=27,823 width=1,123) (actual rows= loops=)

  • Hash Cond: (pp_1.product_id = pps.product_id)
25. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=10,837.63..19,073.58 rows=27,823 width=607) (actual rows= loops=)

  • Hash Cond: (pptcs.category_site_id = pcss.category_site_id)
26. 0.000 0.000 ↓ 0.0

Hash Join (cost=10,836.23..18,957.61 rows=27,823 width=95) (actual rows= loops=)

  • Hash Cond: (pptcs.category_site_id = ppcs.category_site_id)
27. 0.000 0.000 ↓ 0.0

Hash Join (cost=10,797.94..18,524.16 rows=31,184 width=60) (actual rows= loops=)

  • Hash Cond: (pptcs.product_id = pp_1.product_id)
28. 0.000 0.000 ↓ 0.0

Seq Scan on product_to_category_site pptcs (cost=0.00..6,097.00 rows=351,300 width=9) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Hash (cost=10,380.78..10,380.78 rows=33,373 width=55) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on product pp_1 (cost=615.90..10,380.78 rows=33,373 width=55) (actual rows= loops=)

  • Recheck Cond: (product_status_id = ANY ('{4,5,6}'::integer[]))
31. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on fki_product_product_status_id_fkey (cost=0.00..607.56 rows=33,373 width=0) (actual rows= loops=)

  • Index Cond: (product_status_id = ANY ('{4,5,6}'::integer[]))
32. 0.000 0.000 ↓ 0.0

Hash (cost=29.70..29.70 rows=687 width=35) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Seq Scan on category_site ppcs (cost=0.00..29.70 rows=687 width=35) (actual rows= loops=)

  • Filter: (deleted_at IS NULL)
34. 0.000 0.000 ↓ 0.0

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

35. 0.000 0.000 ↓ 0.0

Seq Scan on category_site_synonyms pcss (cost=0.00..1.18 rows=18 width=520) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Hash (cost=1.11..1.11 rows=11 width=520) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Seq Scan on product_synonyms pps (cost=0.00..1.11 rows=11 width=520) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Hash (cost=28.60..28.60 rows=835 width=99) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1.81..28.60 rows=835 width=99) (actual rows= loops=)

  • Hash Cond: (ppr.measure_id = pm.measure_id)
40. 0.000 0.000 ↓ 0.0

Seq Scan on property ppr (cost=0.00..22.35 rows=835 width=39) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Hash (cost=1.36..1.36 rows=36 width=68) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Seq Scan on measure pm (cost=0.00..1.36 rows=36 width=68) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Hash (cost=2.47..2.47 rows=147 width=4) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Seq Scan on property_is_type_of_product ppitop (cost=0.00..2.47 rows=147 width=4) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Hash (cost=3,598.73..3,598.73 rows=58,594 width=187) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=4.83..3,598.73 rows=58,594 width=187) (actual rows= loops=)

  • Hash Cond: (pv_type.value_id = pvs_type.value_id)
47. 0.000 0.000 ↓ 0.0

Seq Scan on value pv_type (cost=0.00..3,079.94 rows=58,594 width=173) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Hash (cost=3.26..3.26 rows=126 width=18) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Seq Scan on value_synonyms pvs_type (cost=0.00..3.26 rows=126 width=18) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Hash (cost=3,598.73..3,598.73 rows=58,594 width=183) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=4.83..3,598.73 rows=58,594 width=183) (actual rows= loops=)

  • Hash Cond: (pv.value_id = pvs.value_id)
52. 0.000 0.000 ↓ 0.0

Seq Scan on value pv (cost=0.00..3,079.94 rows=58,594 width=169) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Hash (cost=3.26..3.26 rows=126 width=18) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Seq Scan on value_synonyms pvs (cost=0.00..3.26 rows=126 width=18) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Hash (cost=2,701.38..2,701.38 rows=4,408 width=169) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on value pv_manufacturer (cost=86.45..2,701.38 rows=4,408 width=169) (actual rows= loops=)

  • Recheck Cond: (property_id = 147)
57. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on fki_value_property_id_fkey (cost=0.00..85.35 rows=4,408 width=0) (actual rows= loops=)

  • Index Cond: (property_id = 147)
58. 0.000 0.000 ↓ 0.0

Hash (cost=2,768.64..2,768.64 rows=3,824 width=183) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=78.76..2,768.64 rows=3,824 width=183) (actual rows= loops=)

  • Hash Cond: (pv_brand.value_id = pvs_brand.value_id)
60. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on value pv_brand (cost=73.93..2,730.27 rows=3,824 width=169) (actual rows= loops=)

  • Recheck Cond: (property_id = 148)
61. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on fki_value_property_id_fkey (cost=0.00..72.97 rows=3,824 width=0) (actual rows= loops=)

  • Index Cond: (property_id = 148)
62. 0.000 0.000 ↓ 0.0

Hash (cost=3.26..3.26 rows=126 width=18) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Seq Scan on value_synonyms pvs_brand (cost=0.00..3.26 rows=126 width=18) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

Hash (cost=3.26..3.26 rows=126 width=18) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Seq Scan on value_synonyms pvs_manufacturer (cost=0.00..3.26 rows=126 width=18) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

Sort (cost=5,201.64..5,317.24 rows=46,240 width=8) (actual rows= loops=)

  • Sort Key: ps.product_id
67. 0.000 0.000 ↓ 0.0

Seq Scan on shipment ps (cost=0.00..1,618.76 rows=46,240 width=8) (actual rows= loops=)

  • Filter: (amount > '0'::numeric)
68. 0.000 0.000 ↓ 0.0

Index Scan using price_product_id_region_id_key on price p_price (cost=0.42..8,166.46 rows=125,763 width=18) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Index Scan using product_product_activity_product_id_shop_id_unique on product_activity ppa (cost=0.29..3,752.04 rows=41,623 width=12) (actual rows= loops=)

  • Filter: is_active
70. 0.000 0.000 ↓ 0.0

Sort (cost=1,843.87..1,894.25 rows=20,153 width=36) (actual rows= loops=)

  • Sort Key: product_popularity.product_id
71. 0.000 0.000 ↓ 0.0

CTE Scan on product_popularity (cost=0.00..403.06 rows=20,153 width=36) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

Sort (cost=49,224.08..49,307.52 rows=33,373 width=1,599) (actual rows= loops=)

  • Sort Key: p.product_id
73. 0.000 0.000 ↓ 0.0

Merge Right Join (cost=3,175.26..46,716.71 rows=33,373 width=1,599) (actual rows= loops=)

  • Merge Cond: (ppi.product_id = p.product_id)
74. 0.000 0.000 ↓ 0.0

Index Scan using product_info_pkey on product_info ppi (cost=0.42..42,101.10 rows=376,068 width=1,106) (actual rows= loops=)

75. 0.000 0.000 ↓ 0.0

Sort (cost=3,174.84..3,258.27 rows=33,373 width=497) (actual rows= loops=)

  • Sort Key: p.product_id
76. 0.000 0.000 ↓ 0.0

CTE Scan on p (cost=0.00..667.46 rows=33,373 width=497) (actual rows= loops=)