explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TTrj

Settings
# exclusive inclusive rows x rows loops node
1. 3.162 4,898.617 ↑ 1.0 1 1

Aggregate (cost=65,460.39..65,460.40 rows=1 width=8) (actual time=4,898.617..4,898.617 rows=1 loops=1)

2. 765.131 4,895.455 ↓ 29,631.0 29,631 1

Hash Semi Join (cost=37,072.79..65,460.39 rows=1 width=4) (actual time=3,382.965..4,895.455 rows=29,631 loops=1)

  • Hash Cond: (p.product_no = cp.product_no)
3. 37.846 2,214.886 ↓ 1.3 29,640 1

Hash Left Join (cost=36,961.73..65,290.20 rows=22,524 width=8) (actual time=1,467.310..2,214.886 rows=29,640 loops=1)

  • Hash Cond: (p.brand_code = bp.brand_code)
4. 39.585 2,177.022 ↓ 1.3 29,640 1

Hash Left Join (cost=36,959.64..65,091.03 rows=22,524 width=17) (actual time=1,467.284..2,177.022 rows=29,640 loops=1)

  • Hash Cond: (p.trend_code = tp_2.trend_code)
5. 82.837 2,137.414 ↓ 1.3 29,640 1

Hash Right Join (cost=36,957.60..64,779.28 rows=22,524 width=26) (actual time=1,467.246..2,137.414 rows=29,640 loops=1)

  • Hash Cond: (por.product_no = p.product_no)
6. 587.382 587.382 ↓ 1.0 159,016 1

Seq Scan on te_product_option_revision por (cost=0.00..27,013.79 rows=156,692 width=4) (actual time=0.016..587.382 rows=159,016 loops=1)

  • Filter: ((is_deleted = 'F'::bpchar) AND (shop_no = 1))
  • Rows Removed by Filter: 500350
7. 25.305 1,467.195 ↓ 1.3 29,640 1

Hash (cost=36,676.05..36,676.05 rows=22,524 width=26) (actual time=1,467.195..1,467.195 rows=29,640 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1993kB
8. 167.825 1,441.890 ↓ 1.3 29,640 1

Hash Left Join (cost=12,604.53..36,676.05 rows=22,524 width=26) (actual time=594.187..1,441.890 rows=29,640 loops=1)

  • Hash Cond: (p.manufacturer_code = (tp_1.manufacturer_code)::bpchar)
  • Filter: (((p.product_code)::text ~~ '%투명%'::text) OR ((tp.product_name)::text ~~ '%투명%'::text) OR ((tp_1.manufacturer_name)::text ~~ '%투명%'::text) OR (t.search_keyword ~~ '%투명%'::text) OR (tp.product_tag ~~ '%투명%'::text))
  • Rows Removed by Filter: 103997
9. 330.843 1,274.037 ↓ 1.3 133,637 1

Hash Join (cost=12,602.45..35,084.68 rows=105,953 width=167) (actual time=594.094..1,274.037 rows=133,637 loops=1)

  • Hash Cond: (tp.product_no = p.product_no)
10. 349.570 349.570 ↓ 1.1 133,638 1

Seq Scan on te_product_in_shop tp (cost=0.00..14,857.82 rows=120,984 width=129) (actual time=0.018..349.570 rows=133,638 loops=1)

  • Filter: ((shop_no = 1) AND (is_display = 'T'::bpchar) AND (is_deleted = 'F'::bpchar))
  • Rows Removed by Filter: 26583
11. 240.698 593.624 ↓ 1.0 140,898 1

Hash (cost=9,477.55..9,477.55 rows=140,312 width=49) (actual time=593.624..593.624 rows=140,898 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 6610kB
12. 352.926 352.926 ↓ 1.0 140,898 1

Seq Scan on product p (cost=0.00..9,477.55 rows=140,312 width=49) (actual time=0.011..352.926 rows=140,898 loops=1)

  • Filter: (is_deleted = 'F'::bpchar)
  • Rows Removed by Filter: 19323
13. 0.004 0.028 ↓ 2.0 2 1

Hash (cost=2.07..2.07 rows=1 width=284) (actual time=0.028..0.028 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.008 0.024 ↓ 2.0 2 1

Nested Loop (cost=0.00..2.07 rows=1 width=284) (actual time=0.021..0.024 rows=2 loops=1)

  • Join Filter: ((t.m_code)::text = (tp_1.manufacturer_code)::text)
  • Rows Removed by Join Filter: 1
15. 0.008 0.008 ↓ 2.0 2 1

Seq Scan on te_manufacturer_in_shop tp_1 (cost=0.00..1.02 rows=1 width=252) (actual time=0.007..0.008 rows=2 loops=1)

  • Filter: (shop_no = 1)
16. 0.008 0.008 ↑ 1.0 2 2

Seq Scan on te_manufacture t (cost=0.00..1.02 rows=2 width=66) (actual time=0.004..0.004 rows=2 loops=2)

17. 0.008 0.023 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.001 0.015 ↑ 1.0 1 1

Nested Loop (cost=0.00..2.04 rows=1 width=36) (actual time=0.015..0.015 rows=1 loops=1)

  • Join Filter: (t_1.trend_code = tp_2.trend_code)
19. 0.007 0.007 ↑ 1.0 1 1

Seq Scan on te_trend t_1 (cost=0.00..1.01 rows=1 width=36) (actual time=0.006..0.007 rows=1 loops=1)

20. 0.007 0.007 ↑ 1.0 1 1

Seq Scan on te_trend_in_shop tp_2 (cost=0.00..1.01 rows=1 width=36) (actual time=0.007..0.007 rows=1 loops=1)

  • Filter: (shop_no = 1)
21. 0.003 0.018 ↓ 2.0 2 1

Hash (cost=2.07..2.07 rows=1 width=36) (actual time=0.018..0.018 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
22. 0.003 0.015 ↓ 2.0 2 1

Nested Loop (cost=0.00..2.07 rows=1 width=36) (actual time=0.012..0.015 rows=2 loops=1)

  • Join Filter: (b.brand_code = bp.brand_code)
  • Rows Removed by Join Filter: 1
23. 0.006 0.006 ↓ 2.0 2 1

Seq Scan on te_brand_in_shop bp (cost=0.00..1.02 rows=1 width=36) (actual time=0.006..0.006 rows=2 loops=1)

  • Filter: (shop_no = 1)
24. 0.006 0.006 ↑ 1.0 2 2

Seq Scan on te_brand b (cost=0.00..1.02 rows=2 width=36) (actual time=0.003..0.003 rows=2 loops=2)

25. 996.032 1,915.438 ↓ 1,427,684.0 1,427,684 1

Hash (cost=111.05..111.05 rows=1 width=4) (actual time=1,915.438..1,915.438 rows=1,427,684 loops=1)

  • Buckets: 262144 (originally 1024) Batches: 16 (originally 1) Memory Usage: 6145kB
26. 324.779 919.406 ↓ 1,427,684.0 1,427,684 1

Nested Loop (cost=5.09..111.05 rows=1 width=4) (actual time=0.282..919.406 rows=1,427,684 loops=1)

27. 2.157 27.171 ↓ 2,208.0 2,208 1

Nested Loop (cost=4.66..54.85 rows=1 width=8) (actual time=0.231..27.171 rows=2,208 loops=1)

28. 18.198 18.381 ↓ 2,211.0 2,211 1

Bitmap Heap Scan on te_category_in_shop e (cost=4.38..38.52 rows=1 width=4) (actual time=0.203..18.381 rows=2,211 loops=1)

  • Recheck Cond: (shop_no = 1)
  • Filter: ((is_display <> 'F'::bpchar) AND (is_display = 'T'::bpchar) AND (access_scope = 'F'::bpchar))
  • Rows Removed by Filter: 330
  • Heap Blocks: exact=78
29. 0.183 0.183 ↓ 195.5 2,541 1

Bitmap Index Scan on pk_category_in_shop (cost=0.00..4.38 rows=13 width=0) (actual time=0.183..0.183 rows=2,541 loops=1)

  • Index Cond: (shop_no = 1)
30. 6.633 6.633 ↑ 1.0 1 2,211

Index Scan using pk_category on category c (cost=0.28..8.31 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=2,211)

  • Index Cond: (category_no = e.category_no)
  • Filter: ((depth >= 1) AND (depth <= 4) AND (display_type = ANY ('{P,A}'::bpchar[])) AND (parent_cate_no <> '-3'::integer))
  • Rows Removed by Filter: 0
31. 567.456 567.456 ↑ 1.6 647 2,208

Index Only Scan using pk_category_product on category_product cp (cost=0.43..45.61 rows=1,059 width=8) (actual time=0.025..0.257 rows=647 loops=2,208)

  • Index Cond: ((shop_no = 1) AND (category_no = c.category_no))
  • Heap Fetches: 1855
Planning time : 8.473 ms
Execution time : 4,898.949 ms