explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZxuA

Settings
# exclusive inclusive rows x rows loops node
1. 4,800.648 3,577,137.706 ↑ 103.8 32,397 1

Merge Right Join (cost=519,252.27..1,704,750.61 rows=3,362,830 width=1,631) (actual time=3,572,424.774..3,577,137.706 rows=32,397 loops=1)

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

CTE product_popularity

3. 49.019 77.105 ↑ 1.0 20,153 1

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

  • Group Key: pp.product_id
4. 28.086 28.086 ↑ 1.0 29,782 1

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

5.          

CTE p

6. 2,496,468.512 3,569,964.496 ↑ 1.0 32,397 1

GroupAggregate (cost=407,954.56..466,240.47 rows=33,373 width=1,964) (actual time=108,210.186..3,569,964.496 rows=32,397 loops=1)

  • Group Key: pp_1.product_id
7. 210,257.512 1,073,495.984 ↓ 869.9 265,717,103 1

Merge Left Join (cost=407,954.56..425,388.13 rows=305,460 width=1,964) (actual time=108,100.771..1,073,495.984 rows=265,717,103 loops=1)

  • Merge Cond: (pp_1.product_id = ppa.product_id)
  • Join Filter: (ps.shop_id = ppa.shop_id)
  • Rows Removed by Join Filter: 370877018
8. 100,846.439 370,568.422 ↓ 869.9 265,717,103 1

Merge Left Join (cost=407,952.88..420,345.67 rows=305,460 width=1,960) (actual time=108,100.745..370,568.422 rows=265,717,103 loops=1)

  • Merge Cond: (pp_1.product_id = p_price.product_id)
9. 21,261.675 168,321.702 ↓ 159.5 48,722,664 1

Merge Left Join (cost=407,945.25..410,079.37 rows=305,460 width=1,946) (actual time=108,100.697..168,321.702 rows=48,722,664 loops=1)

  • Merge Cond: (pp_1.product_id = ps.product_id)
10. 84,288.789 140,118.396 ↓ 86.4 26,380,529 1

Sort (cost=402,743.54..403,507.19 rows=305,460 width=1,942) (actual time=108,071.022..140,118.396 rows=26,380,529 loops=1)

  • Sort Key: pp_1.product_id
  • Sort Method: external merge Disk: 5214400kB
11. 6,655.411 55,829.607 ↓ 86.4 26,380,529 1

Hash Left Join (cost=67,270.42..118,078.18 rows=305,460 width=1,942) (actual time=2,312.388..55,829.607 rows=26,380,529 loops=1)

  • Hash Cond: (pv_manufacturer.value_id = pvs_manufacturer.value_id)
12. 6,582.846 49,174.151 ↓ 86.2 26,336,093 1

Hash Left Join (cost=67,265.59..115,394.00 rows=305,460 width=1,928) (actual time=2,312.327..49,174.151 rows=26,336,093 loops=1)

  • Hash Cond: (pptv.value_id = pv_brand.value_id)
13. 6,326.857 42,587.532 ↓ 86.0 26,280,001 1

Hash Left Join (cost=64,449.14..111,232.73 rows=305,460 width=1,749) (actual time=2,308.530..42,587.532 rows=26,280,001 loops=1)

  • Hash Cond: (pptv.value_id = pv_manufacturer.value_id)
14. 10,759.931 36,256.953 ↓ 86.0 26,280,001 1

Hash Left Join (cost=61,692.66..107,100.97 rows=305,460 width=1,580) (actual time=2,304.767..36,256.953 rows=26,280,001 loops=1)

  • Hash Cond: (pptv.value_id = pv.value_id)
15. 6,533.872 25,459.402 ↓ 85.7 26,186,530 1

Hash Left Join (cost=57,361.50..98,569.74 rows=305,460 width=1,401) (actual time=2,267.059..25,459.402 rows=26,186,530 loops=1)

  • Hash Cond: ((pptv_type.value_id = pv_type.value_id) AND (ppitop.property_id = pv_type.property_id))
16. 5,673.967 18,886.688 ↓ 85.5 26,128,361 1

Hash Left Join (cost=52,883.86..91,796.68 rows=305,460 width=1,230) (actual time=2,228.158..18,886.688 rows=26,128,361 loops=1)

  • Hash Cond: (pptv_type.property_id = ppitop.property_id)
17. 10,755.196 13,212.676 ↓ 85.3 26,045,031 1

Hash Right Join (cost=52,879.55..89,020.17 rows=305,460 width=1,230) (actual time=2,228.071..13,212.676 rows=26,045,031 loops=1)

  • Hash Cond: (pptv_type.product_id = pp_1.product_id)
18. 229.643 229.643 ↑ 1.0 1,245,724 1

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

19. 755.788 2,227.837 ↓ 11.8 1,090,855 1

Hash (cost=51,727.19..51,727.19 rows=92,189 width=1,222) (actual time=2,227.837..2,227.837 rows=1,090,855 loops=1)

  • Buckets: 1048576 (originally 131072) Batches: 2 (originally 1) Memory Usage: 122881kB
20. 449.575 1,472.049 ↓ 11.8 1,090,855 1

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

  • Hash Cond: (pptv.property_id = ppr.property_id)
21. 619.867 1,021.840 ↓ 11.8 1,090,855 1

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

  • Hash Cond: (pptv.product_id = pp_1.product_id)
22. 165.726 165.726 ↑ 1.0 1,245,724 1

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

23. 23.912 236.247 ↓ 1.7 48,101 1

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

  • Buckets: 65536 (originally 32768) Batches: 1 (originally 1) Memory Usage: 7932kB
24. 15.367 212.335 ↓ 1.7 48,101 1

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

  • Hash Cond: (pp_1.product_id = pps.product_id)
25. 10.077 196.958 ↓ 1.7 48,101 1

Hash Left Join (cost=10,837.63..19,073.58 rows=27,823 width=607) (actual time=47.385..196.958 rows=48,101 loops=1)

  • Hash Cond: (pptcs.category_site_id = pcss.category_site_id)
26. 17.844 186.865 ↓ 1.7 46,988 1

Hash Join (cost=10,836.23..18,957.61 rows=27,823 width=95) (actual time=47.353..186.865 rows=46,988 loops=1)

  • Hash Cond: (pptcs.category_site_id = ppcs.category_site_id)
27. 79.775 168.484 ↓ 1.7 51,573 1

Hash Join (cost=10,797.94..18,524.16 rows=31,184 width=60) (actual time=46.809..168.484 rows=51,573 loops=1)

  • Hash Cond: (pptcs.product_id = pp_1.product_id)
28. 42.028 42.028 ↑ 1.0 351,300 1

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

29. 9.324 46.681 ↑ 1.0 32,398 1

Hash (cost=10,380.78..10,380.78 rows=33,373 width=55) (actual time=46.681..46.681 rows=32,398 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3987kB
30. 33.017 37.357 ↑ 1.0 32,398 1

Bitmap Heap Scan on product pp_1 (cost=615.90..10,380.78 rows=33,373 width=55) (actual time=6.118..37.357 rows=32,398 loops=1)

  • Recheck Cond: (product_status_id = ANY ('{4,5,6}'::integer[]))
  • Heap Blocks: exact=7754
31. 4.340 4.340 ↑ 1.0 32,398 1

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

  • Index Cond: (product_status_id = ANY ('{4,5,6}'::integer[]))
32. 0.156 0.537 ↑ 1.0 687 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 54kB
33. 0.381 0.381 ↑ 1.0 687 1

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

  • Filter: (deleted_at IS NULL)
  • Rows Removed by Filter: 83
34. 0.005 0.016 ↑ 1.0 18 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
35. 0.011 0.011 ↑ 1.0 18 1

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

36. 0.006 0.010 ↑ 1.0 11 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.004 0.004 ↑ 1.0 11 1

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

38. 0.205 0.634 ↑ 1.0 835 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 67kB
39. 0.269 0.429 ↑ 1.0 835 1

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

  • Hash Cond: (ppr.measure_id = pm.measure_id)
40. 0.137 0.137 ↑ 1.0 835 1

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

41. 0.009 0.023 ↑ 1.0 36 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
42. 0.014 0.014 ↑ 1.0 36 1

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

43. 0.020 0.045 ↑ 1.0 147 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
44. 0.025 0.025 ↑ 1.0 147 1

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

45. 15.961 38.842 ↓ 1.0 58,633 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 12272kB
46. 14.045 22.881 ↓ 1.0 58,633 1

Hash Left Join (cost=4.83..3,598.73 rows=58,594 width=187) (actual time=0.058..22.881 rows=58,633 loops=1)

  • Hash Cond: (pv_type.value_id = pvs_type.value_id)
47. 8.792 8.792 ↑ 1.0 58,594 1

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

48. 0.022 0.044 ↑ 1.0 126 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
49. 0.022 0.022 ↑ 1.0 126 1

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

50. 16.405 37.620 ↓ 1.0 58,633 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 12043kB
51. 12.402 21.215 ↓ 1.0 58,633 1

Hash Left Join (cost=4.83..3,598.73 rows=58,594 width=183) (actual time=0.056..21.215 rows=58,633 loops=1)

  • Hash Cond: (pv.value_id = pvs.value_id)
52. 8.768 8.768 ↑ 1.0 58,594 1

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

53. 0.025 0.045 ↑ 1.0 126 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
54. 0.020 0.020 ↑ 1.0 126 1

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

55. 0.877 3.722 ↑ 1.0 4,331 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 313kB
56. 2.328 2.845 ↑ 1.0 4,331 1

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

  • Recheck Cond: (property_id = 147)
  • Heap Blocks: exact=1519
57. 0.517 0.517 ↑ 1.0 4,331 1

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

  • Index Cond: (property_id = 147)
58. 0.765 3.773 ↓ 1.0 3,896 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 230kB
59. 0.872 3.008 ↓ 1.0 3,896 1

Hash Left Join (cost=78.76..2,768.64 rows=3,824 width=183) (actual time=0.694..3.008 rows=3,896 loops=1)

  • Hash Cond: (pv_brand.value_id = pvs_brand.value_id)
60. 1.672 2.086 ↓ 1.0 3,875 1

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

  • Recheck Cond: (property_id = 148)
  • Heap Blocks: exact=1359
61. 0.414 0.414 ↓ 1.0 3,875 1

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

  • Index Cond: (property_id = 148)
62. 0.028 0.050 ↑ 1.0 126 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
63. 0.022 0.022 ↑ 1.0 126 1

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

64. 0.028 0.045 ↑ 1.0 126 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
65. 0.017 0.017 ↑ 1.0 126 1

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

66. 6,924.899 6,941.631 ↓ 865.3 40,013,240 1

Sort (cost=5,201.64..5,317.24 rows=46,240 width=8) (actual time=29.663..6,941.631 rows=40,013,240 loops=1)

  • Sort Key: ps.product_id
  • Sort Method: quicksort Memory: 3695kB
67. 16.732 16.732 ↑ 1.0 46,048 1

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

  • Filter: (amount > '0'::numeric)
  • Rows Removed by Filter: 22813
68. 101,400.281 101,400.281 ↓ 2,110.1 265,368,142 1

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 time=0.028..101,400.281 rows=265,368,142 loops=1)

69. 492,670.050 492,670.050 ↓ 14,425.2 600,418,165 1

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 time=0.018..492,670.050 rows=600,418,165 loops=1)

  • Filter: is_active
  • Rows Removed by Filter: 222785910
70. 14.706 104.660 ↑ 1.0 20,153 1

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

  • Sort Key: product_popularity.product_id
  • Sort Method: quicksort Memory: 2180kB
71. 89.954 89.954 ↑ 1.0 20,153 1

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

72. 1,385.215 3,572,232.398 ↑ 1.0 32,397 1

Sort (cost=49,224.08..49,307.52 rows=33,373 width=1,599) (actual time=3,572,144.409..3,572,232.398 rows=32,397 loops=1)

  • Sort Key: p.product_id
  • Sort Method: quicksort Memory: 116470kB
73. 73.981 3,570,847.183 ↑ 1.0 32,397 1

Merge Right Join (cost=3,175.26..46,716.71 rows=33,373 width=1,599) (actual time=3,570,395.595..3,570,847.183 rows=32,397 loops=1)

  • Merge Cond: (ppi.product_id = p.product_id)
74. 333.812 333.812 ↑ 1.0 375,518 1

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

75. 255.111 3,570,439.390 ↑ 1.0 32,397 1

Sort (cost=3,174.84..3,258.27 rows=33,373 width=497) (actual time=3,570,395.503..3,570,439.390 rows=32,397 loops=1)

  • Sort Key: p.product_id
  • Sort Method: quicksort Memory: 106088kB
76. 3,570,184.279 3,570,184.279 ↑ 1.0 32,397 1

CTE Scan on p (cost=0.00..667.46 rows=33,373 width=497) (actual time=108,210.197..3,570,184.279 rows=32,397 loops=1)

Planning time : 13.927 ms
Execution time : 3,578,505.938 ms