우선 나와야할 결과물은 한개의 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"