POSTGRESQL SQL 語句案例，一場由LIMIT 1 引發的“奇怪異像”

最近一段工作很少最佳化SQL ，實際

上7-8年前的確有一段瘋狂最佳化的“美好時光”。 最近一個同事提出一個問題，他的一個POSTGRESQL 的SQL 在執行中因為客戶的需要，將語句新增limit 1 ，但是在新增完畢後，整體語句執行時間超過原有的語句少則9倍，多則20多倍，從不到2秒，變成了23秒。

下面是語句的修改版，不少部分已經改名了。

SELECT

bizzz.* 

FROM

(

SELECT

1 AS "bizzzType",

os.NAME "deFromName",

gb.title "ticketName",

gb.market_price "marketPrice",

gb.price price,

gb.discount_rate * 100 "discountRate",

gb.income_money "ticketIncomeMoney",

gb.div_ticket_name "divTicketName",

btpg.ticket_count "ticketCount",

btpg.pay_money "payMoney",

( gb.market_price - gb.price ) * btpg.ticket_count "discMoney",

CASE 

WHEN btpg.ticket_sale * btpg.ticket_count > btpg.pay_money 

THEN

btpg.pay_money 

ELSE btpg.ticket_sale * btpg.ticket_count 

END "incomeMoney",

shop.ID AS "shopId",

shop.NAME AS "shopName",

btpg.no_give_change "noGiveChange",

btpg.bs_id "bsId",

bb.code "bsCode",

btpg.ticket_code "ticketCode",

btpg.create_time "useTime",

bb.settle_bizzz_date "settlebizzzDate",

COALESCE ( btpg.income_overchange, 0 ) "incomeOverChange" ,

pw_detail_id as "ticketRowId"

FROM

(SELECT

bs_id,

ticket_count,

ticket_sale,

ticket_value,

pay_money,

payc_id,

ticket_id,

create_shop_id,

pw_detail_id,

no_give_change,

create_time,

REPLACE ( REPLACE ( REPLACE ( REPLACE ( ticket_code_serials, '["', '' ), '"]', '' ), '"', '' ), ',', ';' ) AS ticket_code,

income_overchange 

FROM  bizzz_ts_pw_gb 

WHERE create_shop_id IN ( SELECT ID FROM dbi_shop WHERE center_id = 83726 AND manage_type_id IN ( 301, 302, 304 ) ) 

AND modify_time >= '2023-03-02 00:00:00' 

AND modify_time <'2023-03-03 00:00:00' 

AND delflg = 0 

AND settle_state = 1 

) btpg

INNER JOIN (SELECT ID,code,settle_bizzz_date  FROM

bizzz_bs 

WHERE

create_shop_id IN ( SELECT ID FROM dbi_shop WHERE center_id = 83726 AND manage_type_id IN ( 301, 302, 304 ) ) AND settle_time >='2023-03-02 00:00:00'  AND settle_time < '2023-03-03 00:00:00'AND STATE = 1 

AND delflg = 0 ) bb ON btpg.bs_id = bb.

ID

INNER JOIN ( SELECT ID FROM arch_pay c WHERE payc_type_id = 509 ) ap ON btpg.payc_id = ap.ID

LEFT JOIN ( SELECT deal_id, belong_shop_id, title, market_price, price, discount_rate, income_money, synchron_time, div_ticket_name 

FROM o2o_wuu_gb_ticket 

WHERE create_shop_id = 83726 ) gb ON btpg.ticket_id = gb.deal_id AND btpg.create_shop_id = gb.belong_shop_id

INNER JOIN ( SELECT deal_id, belong_shop_id, MAX ( synchron_time ) synchron_time 

FROM o2o_wuu_gb_ticket 

WHERE create_shop_id = 83726 GROUP BY belong_shop_id, deal_id ) gbTicketFilter ON gb.deal_id = gbTicketFilter.deal_id 

AND gb.belong_shop_id = gbTicketFilter.belong_shop_id AND gb.synchron_time = gbTicketFilter.synchron_time

INNER JOIN ( SELECT payc_id, NAME FROM arch_o2o_seller ) os ON os.payc_id = btpg.payc_id

LEFT JOIN dbi_shop shop ON shop.ID = btpg.create_shop_id 

) bizzz 

ORDER BY

"shopName","ticketName","marketPrice","ticketRowId";

 limit 1;

首先比對執行計劃,雖然僅僅是一個limit 的新增但是整體的執行計劃都改變了，

改變發現1 

在沒有新增LIMIT 1的情況下，整體的語句的查詢中是沒有 大量的seq scan ,基本上整體整體的語句中對於資料處理都是在index scan.

改變發現2 

在新增LIMIT 1 後，整體的語句的執行順序，與原先的順序不同了，不新增limit  1  ，首先處理了語句中最大的表，由於最大的表的資料過濾的條件多，所以對於排除資料起到了相關的提前過濾的作用。

而新增了limit 1後，整體的語句處理的順序和語句撰寫的從上到下的語句關聯的順序基本一致，導致處理從小表開始進行預先處理。最終導致小表驅動大表的情況。

改變發現3

在不新增LIMIT 1 的情況下，整體上層的 語句中的計算部分使用中由於，執行順序的問題，讓大表的資料過濾後，在被上層使用，減少了計算的資料量，而反觀新增了LIMIT 1 後的語句，計算中過濾的行較多。導致計算成本升高。

sort  的部分中的語句執行的整體計劃的順序，與加入了limit 的整體計劃的順序是錯位的。

SORT 沒有limit 的部分，中的驅動表與驅動表之間是透過index 關聯的方式進行的處理。

limit   而在末尾加入了limit 後，整體的執行計劃，驅動表和被驅動表的位置互換了，通知對於驅動表的執行的方式變為了掃描方式。

由於語句是否的複雜，如果要非常的明白的分析出來，則需要很長的時間，實際上抓住了兩點

1 複雜的語句如果使用了limit 1，需要注意在POSTGRESQL 13中的語句是否還能透過最佳化器，優先判斷對資料進行大表過濾，由於使用LIMIT 1 這樣的語句，導致語句最佳化和執行系統對於提取的資料的有序性判斷過於複雜，導致最佳化器，按照語句的撰寫方式進行了資料的執行，保證提取資料的優秀和有效性，同時多個不同表的欄位最終進行排序，加重了執行分析器的負擔，導致執行分析器躺平，做了保守的執行計劃的操作。

2  業務邏輯是否需要對於LIMIT 1 的語句進行排序的分析，這點非常有必要，在語句的執行中大部分語句的撰寫尤其類似這樣OLAP 很重的語句一般都帶有排序，但是如果只是在結果中取一個結果  limit 1 則是否有必要進行排序這點非常有必要進行確認。一般根據語句的邏輯，是沒有必要進行排序在LIMIT 1，因為你是隨機取和順序是無關的。

這個語句實際上最後最佳化的手段就是去掉ORDER BY ，最終去掉後比原先的同樣的條件，執行的效率提高了  62倍，在 150毫秒左右就將結果計算出來,同時還有一個因素是如果你在撰寫語句的時候帶有LIMIT 1 則POSTGRESQL 的最佳化器會優先選擇計算成本中，第一個啟動成本較低的執行計劃而不是整體成本較低的執行計劃，所以建議在一些語句中，考慮業務的需求的情況下，分析是否有必要進行排序，慎用在複雜語句中的排序導致的執行效率低下的問題。

另外這裡還有一個在使用LIMIT 1 後導致的PG執行計劃的傾向性的問題，你造嗎 ！