database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
ㆍPostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
PostgreSQL Q&A 9776 게시물 읽기
No. 9776
row_number 사용시 성능 개선 방법
작성자
비투즈(btos0207)
작성일
2017-03-23 21:46:42
조회수
1,769

안녕하세요 형님 누님 고수님들

row_number (partition by order by) 관련하여 질문이 있습니다.

테스트 테이블

chsdb=# \d partition_by_test
  Table "public.partition_by_test"
 Column |     Type      | Modifiers
--------+---------------+-----------
 a      | numeric       |
 b      | character(10) |
 c      | character(20) |

select * from

(select *,ROW_NUMBER() OVER(PARTITION BY b order by a) as d 

from partition_by_test) a

where a.d=1;
 

b컬럼 기준 a로 정렬하여 순위가 1인 행에 관련하여 출력하는게 목적입니다.

그런데 partition_by_test 테이블 건수가 많다보니까 메모리 사용량이 높습니다.

(partition by , order by 등 sort에서 많이 사용되는듯 합니다)

explain select * from (select *,ROW_NUMBER() OVER(PARTITION BY b order by a) as d
 from partition_by_test) a where a.d=1;
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Gather Motion 8:1  (slice2; segments: 8)  (cost=1826.03..2170.03 rows=18 width=168)
   ->  Subquery Scan a  (cost=1826.03..2170.03 rows=3 width=168)
         Filter: d = 1
         ->  Window  (cost=1826.03..1955.03 rows=2150 width=160)
               Partition By: partition_by_test.b
               Order By: partition_by_test.a
               ->  Sort  (cost=1826.03..1869.03 rows=2150 width=160)
                     Sort Key: partition_by_test.b, partition_by_test.a
                     ->  Redistribute Motion 8:8  (slice1; segments: 8)  (cost=0.00..616.00 rows=2150 width=160)
                           Hash Key: partition_by_test.b
                           ->  Seq Scan on partition_by_test  (cost=0.00..272.00 rows=2150 width=160)
 Optimizer status: legacy query optimizer

 

partition order by 부문에서

모든 행에 순위를 매기지 말고 상위 행에 대해서만 뽑으면 리소스 사용량이 줄을 수 있을 듯한데

이런 방도로 튜닝이나 아니면 다른 방도가 있을까요?

 

조언 부탁드립니다.

감사합니다.

이 글에 대한 댓글이 총 4건 있습니다.

통계 쿼리를 빠르게 하려면 통계 테이블을 별도로 생성하던가 하는게 좋습니다만 우선 order by를 수행하는 것은 별수 없고

explain (analyze,buffers) select a,b,c from

(

select a,b,c,rank() over w as rank

from partition_by_test

window w as (order by a) limit 10 ) sub

where rank=1;

만약 b라는 컬럼이 딱 10개면 10개 10개 이상으로 group by 안된다면

위와 같이 했을 경우 속도상 빨라지겠지만 b라는 컬럼의 중복데이터가 많이 없다면 도움이 안될듯합니다.

김주왕(kimjuking)님이 2017-03-24 17:53:14에 작성한 댓글입니다.

인덱스를 b,a 로 만들고,

그냥 select * from partition_by_test order by b,a limit 1 

으로 풀편 되지 않나요?

김상기(ioseph)님이 2017-03-25 23:23:20에 작성한 댓글입니다.

partition by가 있어 안되지 않을까요?

김주왕(kimjuking)님이 2017-03-27 08:29:15에 작성한 댓글입니다.

그렇겠네요. b 칼럼 기준 min(a) 겠네요.

이것도 select b,min(a) from table group by b 하면 나오지 않을까요?

여기서 c 값까지 보려면 좀 꼬이긴 하겠네요.

메모리 사용이 많을 수 밖에 없는 쿼리가 되겠네요.

 

김상기(ioseph)님이 2017-03-27 09:44:44에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
9779postgreSQl vacuum 관련 [3]
검콤이
2017-03-31
1676
9778Oracle의 synonym 과 같은 역할을 하는게 있나요?? [1]
전산천재
2017-03-28
1731
9777SQL 기초 질문... [5]
jusuni
2017-03-24
1545
9776row_number 사용시 성능 개선 방법 [4]
비투즈
2017-03-23
1769
9775pg_catalog."C" 에 대한 질문 드립니다 [1]
황성근
2017-03-21
1768
9774client의 ip address 를 가져올수 있는 방법이 있나요? [1]
김경태
2017-03-05
1817
9773configure: error: library 'xml2' (version >= 2.6.23) is required for XML support [3]
전상도
2017-03-05
1922
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2018 DSN, All rights reserved.
작업시간: 0.073초, 이곳 서비스는
	PostgreSQL v10.4로 자료를 관리합니다