|
안녕하세요
postgres에서 limit과 order by를 함께 사용할 경우 explain 변화하는 것에 대하여 궁금해서 글을 올립니다.
order by만 사용할 경우
Sort (cost=402354.82..402487.52 rows=53083 width=202) (actual time=2979.700..2979.879 rows=289 loops=1)
Sort Key: af.ctime
Sort Method: quicksort Memory: 98kB
-> Merge Join (cost=357400.19..398188.87 rows=53083 width=202) (actual time=2579.980..2978.984 rows=289 loops=1)
Merge Cond: (af.f_seq = testdb.p_seq)
-> Nested Loop (cost=0.00..77544.95 rows=32400 width=66) (actual time=0.045..368.099 rows=30731 loops=1)
-> Index Scan using test2_f_seq on test2 af (cost=0.00..13156.55 rows=32400 width=45) (actual time=0.018..43.278 rows=30770 loops=1)
-> Index Scan using test_pk on test mf (cost=0.00..1.98 rows=1 width=21) (actual time=0.006..0.007 rows=1 loops=30770)
Index Cond: (f_seq = af.f_seq)
-> Sort (cost=357400.19..357532.89 rows=53083 width=152) (actual time=2579.415..2579.716 rows=423 loops=1)
Sort Key: testdb.p_seq
Sort Method: quicksort Memory: 78kB
-> Hash Left Join (cost=306023.52..353234.24 rows=53083 width=152) (actual time=2349.350..2578.389 rows=423 loops=1)
이와 같이 merge 로 잘풀립니다.
그러나
order by 뒤에 limit을 추가하면
Limit (cost=306027.65..306517.42 rows=1 width=202) (actual time=3303.627..3303.628 rows=1 loops=1)
-> Nested Loop (cost=306027.65..26304090.97 rows=53083 width=202) (actual time=3303.624..3303.624 rows=1 loops=1)
Join Filter: (af.f_seq = testdb.p_seq)
Rows Removed by Join Filter: 523254
-> Nested Loop (cost=0.00..150922.10 rows=32400 width=66) (actual time=0.031..16.372 rows=1238 loops=1)
-> Index Scan using test2_ctime on test2 af (cost=0.00..31217.30 rows=32400 width=45) (actual time=0.016..1.928 rows=1238 loops=1)
-> Index Scan using test_pk on test mf (cost=0.00..3.68 rows=1 width=21) (actual time=0.007..0.008 rows=1 loops=1238)
Index Cond: (f_seq = af.f_seq)
-> Materialize (cost=306027.65..353503.79 rows=53083 width=152) (actual time=1.912..2.332 rows=423 loops=1238)
-> Hash Left Join (cost=306027.65..353238.37 rows=53083 width=152) (actual time=2366.365..2596.600 rows=423 loops=1)
Hash Cond: (testdb.p_seq = mf2.f_seq)
-> Hash Left Join (cost=229522.95..266379.71 rows=53083 width=118) (actual time=1838.027..2025.391 rows=423 loops=1)
이와 같이 nested loop로 변화하면서 속도가 느려집니다.
order by 뒤에 limit을 하면 sort된 것을 limit으로 짜르는 구조가 아닌건가지 알고 싶고
이와 비슷한 경험이 있으시다면 해결방법이 있다면 알려주시면 감사하겠습니다. |
