우선 나와야할 결과물은 한개의 ny_product_default 테이블을 기준으로 ny_product_market 테이블을 INNER JOIN 하여야 하며
거기에 파생된 4개의 각 테이블에서 '사용자아이디' 와 '제품번호' 를 기준으로 몇개씩 존재하는지 얻어얻는 쿼리 입니다.
같은 쿼리를 두가지로 분석하여도 성능 향상이 이루어 지지 않아 질문해 봅니다.
두 쿼리중 알맞는 쿼리와 지금보다 속도를 올리려면 어떻하여야 하는지,
ORDER BY 의 속도가 현저히 느린게 원인인것도 같은데 좀 알려 주세요..
1. 첫번쨰 쿼리
EXPLAIN ANALYZE
SELECT a.brand_id, a.pro_no, a.pro_name, b.pro_image, b.thumb160_image, c.file_price, c.file_discount, a.is_open, a.is_approve,
ny_intfield_default(d.sell_count) as sell_count,
ny_intfield_default(e.art_count) as art_count,
ny_intfield_default(f.cart_count) as cart_count,
ny_intfield_default(g.artbag_count) as artbag_count
FROM ny_product_default a
INNER JOIN ny_product_market b ON a.brand_id=b.brand_id AND a.pro_no=b.pro_no
LEFT JOIN ( SELECT file_licence, file_price, file_discount, brand_id, pro_no
FROM ny_product_file
WHERE file_id='00001') c ON a.brand_id=c.brand_id AND a.pro_no = c.pro_no
LEFT JOIN ( SELECT brand_id, pro_no, count(*)::int4 as sell_count
FROM ny_order_content
GROUP BY brand_id, pro_no) d ON a.brand_id=d.brand_id AND a.pro_no=d.pro_no
LEFT JOIN ( SELECT brand_id, pro_no, count(*)::int4 as art_count
FROM ny_product_recommend
GROUP BY brand_id, pro_no) e ON a.brand_id=e.brand_id AND a.pro_no=e.pro_no
LEFT JOIN ( SELECT brand_id, pro_no, count(*)::int4 as cart_count
FROM ny_mem_cart
GROUP BY brand_id, pro_no) f ON f.brand_id = a.brand_id AND f.pro_no = a.pro_no
LEFT JOIN ( SELECT brand_id, pro_no, count(*)::int4 as artbag_count
FROM ny_artbag_image
GROUP BY brand_id, pro_no) g ON g.brand_id = a.brand_id AND g.pro_no = a.pro_no
WHERE a.brand_id SIMILAR TO 'ydmaster'
AND a.cate_cate BETWEEN 10010100::int AND 10010200::int
AND array_to_string(pro_keyword, ',') like '%%'
AND a.is_open SIMILAR TO 'Y'
AND a.is_approve SIMILAR TO 'Y'
AND a.pro_mcolor SIMILAR TO '%'
ORDER BY pro_regdate DESC
OFFSET 0
LIMIT 25
------> plan
"Limit (cost=4270.54..4270.55 rows=1 width=360) (actual time=8079.789..8079.841 rows=25 loops=1)"
" -> Sort (cost=4270.54..4270.55 rows=1 width=360) (actual time=8079.783..8079.803 rows=25 loops=1)"
" Sort Key: a.pro_regdate"
" -> Nested Loop Left Join (cost=34.38..4270.53 rows=1 width=360) (actual time=40.930..8067.711 rows=1626 loops=1)"
" Join Filter: ((("inner".brand_id)::text = ("outer".brand_id)::text) AND ("inner".pro_no = "outer".pro_no))"
" -> Nested Loop Left Join (cost=11.45..4232.50 rows=1 width=356) (actual time=33.143..2427.478 rows=1626 loops=1)"
" Join Filter: ((("inner".brand_id)::text = ("outer".brand_id)::text) AND ("inner".pro_no = "outer".pro_no))"
" -> Nested Loop Left Join (cost=9.34..4230.21 rows=1 width=352) (actual time=32.813..2202.563 rows=1626 loops=1)"
" Join Filter: ((("outer".brand_id)::text = ("inner".brand_id)::text) AND ("outer".pro_no = "inner".pro_no))"
" -> Nested Loop Left Join (cost=4.64..4222.04 rows=1 width=348) (actual time=31.423..922.555 rows=1626 loops=1)"
" Join Filter: ((("outer".brand_id)::text = ("inner".brand_id)::text) AND ("outer".pro_no = "inner".pro_no))"
" -> Nested Loop Left Join (cost=0.00..4214.57 rows=1 width=344) (actual time=30.664..363.773 rows=1626 loops=1)"
" -> Nested Loop (cost=0.00..4208.54 rows=1 width=336) (actual time=30.555..235.423 rows=1626 loops=1)"
" -> Seq Scan on ny_product_default a (cost=0.00..4204.01 rows=1 width=246) (actual time=30.330..138.071 rows=1626 loops=1)"
" Filter: (((brand_id)::text ~ '^ydmaster$'::text) AND (cate_cate >= 10010100) AND (cate_cate <= 10010200) AND (array_to_string(pro_keyword, ','::text) ~~ '%%'::text) AND (is_open ~ '^Y$'::text) AND (is_appro (..)"
" -> Index Scan using "product_market_PK" on ny_product_market b (cost=0.00..4.52 rows=1 width=111) (actual time=0.044..0.047 rows=1 loops=1626)"
" Index Cond: ((("outer".brand_id)::text = (b.brand_id)::text) AND ("outer".pro_no = b.pro_no))"
" -> Index Scan using "product_file_PK" on ny_product_file (cost=0.00..6.01 rows=1 width=64) (actual time=0.039..0.068 rows=1 loops=1626)"
" Index Cond: ((("outer".brand_id)::text = (ny_product_file.brand_id)::text) AND ("outer".pro_no = ny_product_file.pro_no))"
" Filter: (file_id = '00001'::bpchar)"
" -> Subquery Scan d (cost=4.64..6.06 rows=94 width=60) (actual time=0.009..0.216 rows=47 loops=1626)"
" -> HashAggregate (cost=4.64..5.12 rows=94 width=56) (actual time=0.006..0.105 rows=47 loops=1626)"
" -> Seq Scan on ny_order_content (cost=0.00..3.94 rows=94 width=56) (actual time=0.012..0.144 rows=83 loops=1)"
" -> Subquery Scan e (cost=4.70..6.44 rows=116 width=60) (actual time=0.006..0.491 rows=116 loops=1626)"
" -> HashAggregate (cost=4.70..5.28 rows=116 width=20) (actual time=0.003..0.226 rows=116 loops=1626)"
" -> Seq Scan on ny_product_recommend (cost=0.00..3.54 rows=154 width=20) (actual time=0.009..0.207 rows=154 loops=1)"
" -> Subquery Scan f (cost=2.10..2.20 rows=6 width=60) (actual time=0.007..0.083 rows=18 loops=1626)"
" -> HashAggregate (cost=2.10..2.14 rows=6 width=56) (actual time=0.004..0.039 rows=18 loops=1626)"
" -> Seq Scan on ny_mem_cart (cost=0.00..2.06 rows=6 width=56) (actual time=0.048..0.101 rows=22 loops=1)"
" -> Subquery Scan g (cost=22.93..30.48 rows=503 width=60) (actual time=0.008..2.167 rows=508 loops=1626)"
" -> HashAggregate (cost=22.93..25.45 rows=503 width=21) (actual time=0.005..1.009 rows=508 loops=1626)"
" -> Seq Scan on ny_artbag_image (cost=0.00..17.39 rows=739 width=21) (actual time=0.040..1.095 rows=739 loops=1)"
"Total runtime: 8081.516 ms"
2. 두번째 쿼리
explain analyze
SELECT a.brand_id, a.pro_no, a.pro_name, a.is_open, a.is_approve, a.pro_image, a.thumb160_image, a.file_price, a.file_discount,
( SELECT count(*)::int4
FROM ny_order_content
WHERE brand_id = a.brand_id AND pro_no = a.pro_no ) as sell_count,
( SELECT count(*)::int4
FROM ny_product_recommend
WHERE brand_id = a.brand_id AND pro_no = a.pro_no) as art_count,
( SELECT count(*)::int4 as cart_count
FROM ny_mem_cart
WHERE brand_id = a.brand_id AND pro_no = a.pro_no) as cart_count,
( SELECT count(*)::int4
FROM ny_artbag_image
WHERE brand_id = a.brand_id AND pro_no = a.pro_no) as artbag_count
FROM ( SELECT a.brand_id, a.pro_no, a.pro_name, a.pro_regdate, b.pro_image, b.thumb160_image, c.file_price, c.file_discount, a.is_open, a.is_approve
FROM ny_product_default a
INNER JOIN ny_product_market b ON a.brand_id=b.brand_id AND a.pro_no=b.pro_no
LEFT JOIN ( SELECT file_licence, file_price, file_discount, brand_id, pro_no
FROM ny_product_file
WHERE file_id='00001') c ON a.brand_id=c.brand_id AND a.pro_no = c.pro_no
WHERE a.brand_id SIMILAR TO 'ydmaster'
AND a.cate_cate BETWEEN 10010100::int AND 10010200::int
AND array_to_string(pro_keyword, ',') like '%%'
AND a.is_open SIMILAR TO 'Y'
AND a.is_approve SIMILAR TO 'Y'
AND a.pro_mcolor SIMILAR TO '%') a
ORDER BY pro_regdate DESC
OFFSET 0
LIMIT 25
-----------> plan
"Limit (cost=4149.23..4149.23 rows=1 width=318) (actual time=3459.043..3459.098 rows=25 loops=1)"
" -> Sort (cost=4149.23..4149.23 rows=1 width=318) (actual time=3459.038..3459.057 rows=25 loops=1)"
" Sort Key: a.pro_regdate"
" -> Nested Loop Left Join (cost=0.00..4149.22 rows=1 width=318) (actual time=28.765..3449.091 rows=1626 loops=1)"
" -> Nested Loop (cost=0.00..4118.51 rows=1 width=310) (actual time=26.317..219.654 rows=1626 loops=1)"
" -> Seq Scan on ny_product_default a (cost=0.00..4112.48 rows=1 width=246) (actual time=26.157..132.648 rows=1626 loops=1)"
" Filter: (((brand_id)::text ~ '^ydmaster$'::text) AND (cate_cate >= 10010100) AND (cate_cate <= 10010200) AND (array_to_string(pro_keyword, ','::text) ~~ '%%'::text) AND (is_open ~ '^Y$'::text) AND (is_approve ~ '^Y$'::text) AND (p (..)"
" -> Index Scan using "product_market_PK" on ny_product_market b (cost=0.00..6.01 rows=1 width=120) (actual time=0.040..0.043 rows=1 loops=1626)"
" Index Cond: ((("outer".brand_id)::text = (b.brand_id)::text) AND ("outer".pro_no = b.pro_no))"
" -> Index Scan using "product_file_PK" on ny_product_file (cost=0.00..6.01 rows=1 width=64) (actual time=0.038..0.065 rows=1 loops=1626)"
" Index Cond: ((("outer".brand_id)::text = (ny_product_file.brand_id)::text) AND ("outer".pro_no = ny_product_file.pro_no))"
" Filter: (file_id = '00001'::bpchar)"
" SubPlan"
" -> Aggregate (cost=15.86..15.86 rows=1 width=0) (actual time=1.382..1.383 rows=1 loops=1626)"
" -> Seq Scan on ny_artbag_image (cost=0.00..15.85 rows=1 width=0) (actual time=1.218..1.376 rows=0 loops=1626)"
" Filter: (((brand_id)::text = ($0)::text) AND (pro_no = $1))"
" -> Aggregate (cost=2.09..2.10 rows=1 width=0) (actual time=0.063..0.064 rows=1 loops=1626)"
" -> Seq Scan on ny_mem_cart (cost=0.00..2.09 rows=1 width=0) (actual time=0.057..0.058 rows=0 loops=1626)"
" Filter: (((brand_id)::text = ($0)::text) AND (pro_no = $1))"
" -> Aggregate (cost=2.30..2.31 rows=1 width=0) (actual time=0.281..0.282 rows=1 loops=1626)"
" -> Seq Scan on ny_product_recommend (cost=0.00..2.30 rows=1 width=0) (actual time=0.274..0.275 rows=0 loops=1626)"
" Filter: (((brand_id)::text = ($0)::text) AND (pro_no = $1))"
" -> Aggregate (cost=4.42..4.42 rows=1 width=0) (actual time=0.164..0.165 rows=1 loops=1626)"
" -> Seq Scan on ny_order_content (cost=0.00..4.41 rows=1 width=0) (actual time=0.155..0.157 rows=0 loops=1626)"
" Filter: (((brand_id)::text = ($0)::text) AND (pro_no = $1))"
"Total runtime: 3459.913 ms"
|