explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LRJd

Settings
# exclusive inclusive rows x rows loops node
1. 1.068 2,562.528 ↓ 3.9 600 1

Hash Right Join (cost=29,957.34..29,966.06 rows=152 width=172) (actual time=2,555.727..2,562.528 rows=600 loops=1)

  • Hash Cond: (ft.product_id = t.product_id)
2.          

CTE t

3. 0.673 0.673 ↓ 6.0 600 1

Result (cost=0.00..0.51 rows=100 width=0) (actual time=0.013..0.673 rows=600 loops=1)

4.          

CTE c

5. 0.224 0.224 ↓ 6.0 600 1

Result (cost=0.00..0.51 rows=100 width=0) (actual time=0.007..0.224 rows=600 loops=1)

6.          

CTE r

7. 1.364 15.839 ↓ 2.3 231 1

HashAggregate (cost=787.26..788.76 rows=100 width=8) (actual time=15.681..15.839 rows=231 loops=1)

  • Group Key: t_1.product_id
8. 2.116 14.475 ↓ 6.9 1,749 1

Nested Loop (cost=3.54..785.35 rows=254 width=8) (actual time=0.167..14.475 rows=1,749 loops=1)

9. 4.270 7.195 ↓ 7.2 2,582 1

Hash Join (cost=3.25..589.04 rows=360 width=8) (actual time=0.147..7.195 rows=2,582 loops=1)

  • Hash Cond: (p2r.product_id = t_1.product_id)
10. 2.795 2.795 ↑ 1.0 30,341 1

Seq Scan on product_to_review p2r (cost=0.00..468.41 rows=30,341 width=8) (actual time=0.010..2.795 rows=30,341 loops=1)

11. 0.061 0.130 ↓ 6.0 600 1

Hash (cost=2.00..2.00 rows=100 width=4) (actual time=0.130..0.130 rows=600 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
12. 0.069 0.069 ↓ 6.0 600 1

CTE Scan on t t_1 (cost=0.00..2.00 rows=100 width=4) (actual time=0.001..0.069 rows=600 loops=1)

13. 5.164 5.164 ↑ 1.0 1 2,582

Index Scan using review_pkey on review r_1 (cost=0.29..0.54 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=2,582)

  • Index Cond: (review_id = p2r.review_id)
  • Filter: (review_status_id = 5)
  • Rows Removed by Filter: 0
14.          

CTE ft

15. 0.918 4.296 ↓ 18.7 5,665 1

Nested Loop (cost=6.00..8,138.77 rows=303 width=171) (actual time=0.089..4.296 rows=5,665 loops=1)

16. 0.102 0.102 ↓ 3.7 52 1

Index Scan using fki_value_property_id_fkey on value v (cost=0.29..55.61 rows=14 width=171) (actual time=0.042..0.102 rows=52 loops=1)

  • Index Cond: (property_id = 2088)
17. 2.600 3.276 ↑ 1.5 109 52

Bitmap Heap Scan on product_to_value p2v (cost=5.71..575.71 rows=166 width=8) (actual time=0.017..0.063 rows=109 loops=52)

  • Recheck Cond: (value_id = v.value_id)
  • Heap Blocks: exact=1247
18. 0.676 0.676 ↑ 1.5 109 52

Bitmap Index Scan on fki_product_to_value_value_id_fkey (cost=0.00..5.67 rows=166 width=0) (actual time=0.013..0.013 rows=109 loops=52)

  • Index Cond: (value_id = v.value_id)
19.          

CTE product_to_category_site

20. 0.270 7.782 ↓ 6.0 600 1

Unique (cost=831.56..832.21 rows=100 width=9) (actual time=7.313..7.782 rows=600 loops=1)

21. 1.033 7.512 ↓ 10.2 1,322 1

Sort (cost=831.56..831.89 rows=130 width=9) (actual time=7.313..7.512 rows=1,322 loops=1)

  • Sort Key: t_2.product_id, ptcs.is_primary DESC
  • Sort Method: quicksort Memory: 110kB
22. 1.227 6.479 ↓ 10.2 1,322 1

Nested Loop (cost=0.42..827.00 rows=130 width=9) (actual time=0.034..6.479 rows=1,322 loops=1)

23. 1.052 1.052 ↓ 6.0 600 1

CTE Scan on t t_2 (cost=0.00..2.00 rows=100 width=4) (actual time=0.015..1.052 rows=600 loops=1)

24. 4.200 4.200 ↓ 2.0 2 600

Index Scan using product_to_category_site_product_id_category_site_id_key on product_to_category_site ptcs (cost=0.42..8.24 rows=1 width=9) (actual time=0.005..0.007 rows=2 loops=600)

  • Index Cond: (product_id = t_2.product_id)
25.          

CTE categories

26. 0.929 17.187 ↓ 1.2 1,221 1

Sort (cost=477.19..479.67 rows=993 width=38) (actual time=16.972..17.187 rows=1,221 loops=1)

  • Sort Key: cs2.ns_level
  • Sort Method: quicksort Memory: 164kB
27. 2.681 16.258 ↓ 1.2 1,221 1

Nested Loop (cost=3.53..427.76 rows=993 width=38) (actual time=8.258..16.258 rows=1,221 loops=1)

28. 0.431 8.777 ↓ 6.0 600 1

Hash Join (cost=3.25..84.35 rows=100 width=42) (actual time=8.226..8.777 rows=600 loops=1)

  • Hash Cond: (cs.category_site_id = ptcs_1.category_site_id)
29. 0.191 0.191 ↑ 1.0 807 1

Seq Scan on category_site cs (cost=0.00..77.07 rows=807 width=38) (actual time=0.005..0.191 rows=807 loops=1)

30. 0.167 8.155 ↓ 6.0 600 1

Hash (cost=2.00..2.00 rows=100 width=8) (actual time=8.155..8.155 rows=600 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 32kB
31. 7.988 7.988 ↓ 6.0 600 1

CTE Scan on product_to_category_site ptcs_1 (cost=0.00..2.00 rows=100 width=8) (actual time=7.315..7.988 rows=600 loops=1)

32. 4.800 4.800 ↑ 5.0 2 600

Index Scan using category_site_pkey on category_site cs2 (cost=0.28..3.33 rows=10 width=38) (actual time=0.006..0.008 rows=2 loops=600)

  • Index Cond: (category_site_id = ANY ((cs.ns_path || cs.category_site_id)))
  • Filter: (ns_level <> 0)
  • Rows Removed by Filter: 1
33.          

CTE cat

34. 1.857 19.437 ↓ 3.0 600 1

HashAggregate (cost=24.83..27.33 rows=200 width=36) (actual time=19.198..19.437 rows=600 loops=1)

  • Group Key: categories.product_id
35. 17.580 17.580 ↓ 1.2 1,221 1

CTE Scan on categories (cost=0.00..19.86 rows=993 width=36) (actual time=16.975..17.580 rows=1,221 loops=1)

36.          

CTE cf

37. 64.175 98.513 ↓ 36.0 360,000 1

Nested Loop (cost=3.25..211.60 rows=10,000 width=8) (actual time=0.594..98.513 rows=360,000 loops=1)

38. 0.138 0.138 ↓ 6.0 600 1

CTE Scan on t t_3 (cost=0.00..2.00 rows=100 width=4) (actual time=0.000..0.138 rows=600 loops=1)

39. 33.229 34.200 ↓ 6.0 600 600

Materialize (cost=3.25..84.85 rows=100 width=4) (actual time=0.001..0.057 rows=600 loops=600)

40. 0.291 0.971 ↓ 6.0 600 1

Hash Join (cost=3.25..84.35 rows=100 width=4) (actual time=0.591..0.971 rows=600 loops=1)

  • Hash Cond: (pcs.category_site_id = c.category_id)
41. 0.126 0.126 ↑ 1.0 807 1

Seq Scan on category_site pcs (cost=0.00..77.07 rows=807 width=8) (actual time=0.004..0.126 rows=807 loops=1)

42. 0.120 0.554 ↓ 6.0 600 1

Hash (cost=2.00..2.00 rows=100 width=4) (actual time=0.554..0.554 rows=600 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
43. 0.434 0.434 ↓ 6.0 600 1

CTE Scan on c (cost=0.00..2.00 rows=100 width=4) (actual time=0.008..0.434 rows=600 loops=1)

44.          

CTE tp

45. 1,350.164 2,403.957 ↓ 3.0 600 1

HashAggregate (cost=6,021.66..6,024.16 rows=200 width=8) (actual time=2,398.635..2,403.957 rows=600 loops=1)

  • Group Key: cf.product_id
46. 477.351 1,053.793 ↓ 13.3 2,364,000 1

Merge Join (cost=2,413.28..5,132.06 rows=177,919 width=8) (actual time=298.877..1,053.793 rows=2,364,000 loops=1)

  • Merge Cond: (cf.template_id = ptt.template_id)
47. 120.760 337.796 ↓ 36.0 360,000 1

Sort (cost=864.39..889.39 rows=10,000 width=8) (actual time=288.840..337.796 rows=360,000 loops=1)

  • Sort Key: cf.template_id
  • Sort Method: quicksort Memory: 29164kB
48. 217.036 217.036 ↓ 36.0 360,000 1

CTE Scan on cf (cost=0.00..200.00 rows=10,000 width=8) (actual time=0.595..217.036 rows=360,000 loops=1)

49. 235.184 238.646 ↓ 138.5 2,379,914 1

Sort (cost=1,548.89..1,591.86 rows=17,187 width=8) (actual time=8.135..238.646 rows=2,379,914 loops=1)

  • Sort Key: ptt.template_id
  • Sort Method: quicksort Memory: 1574kB
50. 3.462 3.462 ↑ 1.0 17,187 1

Seq Scan on property_to_template ptt (cost=0.00..339.87 rows=17,187 width=8) (actual time=0.012..3.462 rows=17,187 loops=1)

51.          

CTE min_delivery

52. 4.266 46.551 ↑ 3.0 980 1

HashAggregate (cost=13,318.50..13,348.20 rows=2,970 width=12) (actual time=46.352..46.551 rows=980 loops=1)

  • Group Key: pmdd.product_id, tz.shop_id
53. 3.284 42.285 ↓ 3.9 11,452 1

Hash Join (cost=10.97..13,296.22 rows=2,970 width=12) (actual time=0.167..42.285 rows=11,452 loops=1)

  • Hash Cond: (td.zone_id = tz.zone_id)
54. 3.502 38.959 ↓ 3.9 11,452 1

Hash Join (cost=7.30..13,251.72 rows=2,970 width=12) (actual time=0.100..38.959 rows=11,452 loops=1)

  • Hash Cond: (pmdd.delivery_id = td.delivery_id)
55. 2.230 35.430 ↓ 3.9 11,452 1

Nested Loop (cost=4.73..13,208.31 rows=2,970 width=12) (actual time=0.063..35.430 rows=11,452 loops=1)

56. 0.200 0.200 ↓ 6.0 600 1

CTE Scan on t t_4 (cost=0.00..2.00 rows=100 width=4) (actual time=0.002..0.200 rows=600 loops=1)

57. 29.400 33.000 ↑ 1.6 19 600

Bitmap Heap Scan on product_min_delivery_date pmdd (cost=4.73..131.76 rows=30 width=12) (actual time=0.017..0.055 rows=19 loops=600)

  • Recheck Cond: (product_id = t_4.product_id)
  • Filter: (date >= ('now'::cstring)::date)
  • Rows Removed by Filter: 9
  • Heap Blocks: exact=15733
58. 3.600 3.600 ↑ 1.4 28 600

Bitmap Index Scan on product_product_min_delivery_date_product_id_delivery_id_unique (cost=0.00..4.72 rows=39 width=0) (actual time=0.006..0.006 rows=28 loops=600)

  • Index Cond: (product_id = t_4.product_id)
59. 0.014 0.027 ↑ 1.0 70 1

Hash (cost=1.70..1.70 rows=70 width=8) (actual time=0.027..0.027 rows=70 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
60. 0.013 0.013 ↑ 1.0 70 1

Seq Scan on delivery td (cost=0.00..1.70 rows=70 width=8) (actual time=0.007..0.013 rows=70 loops=1)

61. 0.011 0.042 ↓ 1.0 75 1

Hash (cost=2.74..2.74 rows=74 width=8) (actual time=0.042..0.042 rows=75 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
62. 0.031 0.031 ↓ 1.0 75 1

Seq Scan on zone tz (cost=0.00..2.74 rows=74 width=8) (actual time=0.015..0.031 rows=75 loops=1)

63.          

CTE ag

64. 1.598 48.548 ↓ 1.6 324 1

HashAggregate (cost=74.25..76.75 rows=200 width=12) (actual time=48.327..48.548 rows=324 loops=1)

  • Group Key: min_delivery.product_id
65. 46.950 46.950 ↑ 3.0 980 1

CTE Scan on min_delivery (cost=0.00..59.40 rows=2,970 width=12) (actual time=46.354..46.950 rows=980 loops=1)

66. 5.938 5.938 ↓ 18.7 5,665 1

CTE Scan on ft (cost=0.00..6.06 rows=303 width=36) (actual time=0.095..5.938 rows=5,665 loops=1)

67. 22.132 2,555.522 ↓ 6.0 600 1

Hash (cost=27.62..27.62 rows=100 width=140) (actual time=2,555.522..2,555.522 rows=600 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 34556kB
68. 0.333 2,533.390 ↓ 6.0 600 1

Hash Left Join (cost=20.50..27.62 rows=100 width=140) (actual time=2,532.200..2,533.390 rows=600 loops=1)

  • Hash Cond: (t.product_id = r.product_id)
69. 0.453 2,517.079 ↓ 6.0 600 1

Hash Right Join (cost=17.25..23.00 rows=100 width=100) (actual time=2,516.211..2,517.079 rows=600 loops=1)

  • Hash Cond: (ag.product_id = t.product_id)
70. 48.762 48.762 ↓ 1.6 324 1

CTE Scan on ag (cost=0.00..4.00 rows=200 width=36) (actual time=48.331..48.762 rows=324 loops=1)

71. 21.543 2,467.864 ↓ 6.0 600 1

Hash (cost=16.00..16.00 rows=100 width=68) (actual time=2,467.864..2,467.864 rows=600 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 34529kB
72. 0.697 2,446.321 ↓ 6.0 600 1

Hash Right Join (cost=10.25..16.00 rows=100 width=68) (actual time=2,419.144..2,446.321 rows=600 loops=1)

  • Hash Cond: (tp.product_id = t.product_id)
73. 2,425.208 2,425.208 ↓ 3.0 600 1

CTE Scan on tp (cost=0.00..4.00 rows=200 width=36) (actual time=2,398.703..2,425.208 rows=600 loops=1)

74. 0.179 20.416 ↓ 6.0 600 1

Hash (cost=9.00..9.00 rows=100 width=36) (actual time=20.416..20.416 rows=600 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 73kB
75. 0.295 20.237 ↓ 6.0 600 1

Hash Join (cost=3.25..9.00 rows=100 width=36) (actual time=19.405..20.237 rows=600 loops=1)

  • Hash Cond: (cat.product_id = t.product_id)
76. 19.749 19.749 ↓ 3.0 600 1

CTE Scan on cat (cost=0.00..4.00 rows=200 width=36) (actual time=19.200..19.749 rows=600 loops=1)

77. 0.101 0.193 ↓ 6.0 600 1

Hash (cost=2.00..2.00 rows=100 width=4) (actual time=0.193..0.193 rows=600 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
78. 0.092 0.092 ↓ 6.0 600 1

CTE Scan on t (cost=0.00..2.00 rows=100 width=4) (actual time=0.000..0.092 rows=600 loops=1)

79. 0.046 15.978 ↓ 2.3 231 1

Hash (cost=2.00..2.00 rows=100 width=44) (actual time=15.978..15.978 rows=231 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
80. 15.932 15.932 ↓ 2.3 231 1

CTE Scan on r (cost=0.00..2.00 rows=100 width=44) (actual time=15.687..15.932 rows=231 loops=1)

Planning time : 4.195 ms
Execution time : 2,577.908 ms