How to speed up order by?

HyeontaeJu

I use muliple join, and i wanna order by specific column.
but, if i use order by, the speed is very low.. How to solve this problem?

Nimmi_gupta

Can you share your query explain plan with and without order by? With respect of timing how slow if you use order by?
ex:
explain

HyeontaeJu

Sorry, I forgot upload query explain! haha
This is query explain with order by and without,,

without order by

QUERY PLAN DESCRIPTION:

explain SELECT a.image_link , a.product_id , b.title , b.mall_name , b.brand , b.category_name1 , b.category_name2 , b.category_name3 , b.category_name4 FROM newbiz_product_dev.products_image a LEFT OUTER JOIN newbiz_product_dev.products b ON a.product_id = b.product_id WHERE a.image_status = 'SUCCESS' ORDER BY a.update_time desc LIMIT 50 OFFSET 0

Access Path:
+-SELECT LIMIT 50 [Cost: 11M, Rows: 50] (PATH ID: 0)
| Output Only: 50 tuples
| +---> SORT [TOPK] [Cost: 11M, Rows: 4M] (PATH ID: 1)
| | Order: a.update_time DESC
| | Output Only: 50 tuples
| | +---> JOIN MERGEJOIN(inputs presorted) [LeftOuter] [Cost: 5M, Rows: 4M] (PATH ID: 2)
| | | Join Cond: (a.product_id = b.product_id)
| | | Materialize at Output: a.image_link, a.update_time
| | | Runtime Filter: (SIP1(TopK): a.update_time)
| | | +-- Outer -> STORAGE ACCESS for a [Cost: 57K, Rows: 4M] (PATH ID: 3)
| | | | Projection: newbiz_product_dev.products_image_super
| | | | Materialize: a.product_id
| | | | Filter: (a.image_status = 'SUCCESS')
| | | +-- Inner -> STORAGE ACCESS for b [Cost: 4M, Rows: 44M] (PATH ID: 4)
| | | | Projection: newbiz_product_dev.products_super
| | | | Materialize: b.product_id, b.mall_name, b.title, b.category_name1, b.category_name2, b.category_name3, b.category_name4, b.brand

with order by

QUERY PLAN DESCRIPTION:

explain SELECT a.image_link , a.product_id , b.title , b.mall_name , b.brand , b.category_name1 , b.category_name2 , b.category_name3 , b.category_name4 FROM newbiz_product_dev.products_image a LEFT OUTER JOIN newbiz_product_dev.products b ON a.product_id = b.product_id WHERE a.image_status = 'SUCCESS' LIMIT 50 OFFSET 0

Access Path:
+-SELECT LIMIT 50 [Cost: 5M, Rows: 50] (PATH ID: 0)
| Output Only: 50 tuples
| +---> JOIN MERGEJOIN(inputs presorted) [LeftOuter] [Cost: 5M, Rows: 4M] (PATH ID: 1)
| | Join Cond: (a.product_id = b.product_id)
| | Materialize at Output: a.image_link
| | Output Only: 50 tuples
| | +-- Outer -> STORAGE ACCESS for a [Cost: 57K, Rows: 4M] (PATH ID: 2)
| | | Projection: newbiz_product_dev.products_image_super
| | | Materialize: a.product_id
| | | Filter: (a.image_status = 'SUCCESS')
| | +-- Inner -> STORAGE ACCESS for b [Cost: 4M, Rows: 44M] (PATH ID: 3)
| | | Projection: newbiz_product_dev.products_super
| | | Materialize: b.product_id, b.mall_name, b.title, b.category_name1, b.category_name2, b.category_name3, b.category_name4, b.brand

HyeontaeJu

and this is graph
with order by

without order by

Nimmi_gupta

Thanks for providing all the detail information. Explain plan for with and without order by query looks good and are same except order by has to take extra steps[ Runtime Filter: (SIP1(TopK): a.update_time)] for the sorting and that is expected. Can you confirm about the timing how long it's taking? Turn on the timing and run the query with and without order by and capture the time (if possible).