PostgreSQL中的ctid即行號，有點類似oracle中的rowid，今天碰到一個SQL剛好利用到pg中ctid的特點得到了極大的優化。

原始SQL(語句里內容已修改)：

bill@bill=>do language plpgsql $$ bill$# declare bill$# v_cnt int; bill$# begin bill$# select count(*) into v_cnt from t1 ; bill$# if v_cnt>0 bill$# then bill$# create table t2 as select * from t1 limit 100; bill$# else bill$# create table t3 as select * from t1 limit 10; bill$# end if; bill$# end; bill$# $$; DO Time: 62.643 ms

我們先來分析下上面的SQL，其意思大致為：獲取t1表中的記錄數，判斷是否大于0，如果大于0則創建表t2，否則創建表t3。

因此上面的代碼就寫成這樣了，但是我們思考下其真正的需求就是如此嗎？
我們可以換個說法：如果t1表有記錄則創建表t2，沒有則創建表t3。

兩者有區別嗎？其實區別還是很大的，前者可是強調獲取記錄數，我們是不是一定要遍歷整個表得出一個記錄數才知道是否大于0？
真正需求的理解可以讓我們這樣實現，只要從t1表中成功獲取到第一條記錄，就可以停止檢索了，表示該表有記錄了，難道事實不是這樣？

因此我們可以將原先的sql從select count() from t1;改造為：
select count() from t1 where ctid=’(0,1)’;

那么上面的SQL整個就可以寫成：

bill@bill=>do language plpgsql $$ bill$# declare bill$# v_cnt int; bill$# begin bill$# select count(*) into v_cnt from t1 where ctid='(0,1)' ; bill$# if v_cnt>0 bill$# then bill$# create table t2 as select * from t1 limit 100; bill$# else bill$# create table t3 as select * from t1 limit 10; bill$# end if; bill$# end; bill$# $$; DO Time: 2.930 ms

這里時間從62ms縮小到了2.9ms，提升了30倍！
為什么這樣呢？我們使用ctid=’(0,1)'查詢，這樣只需要掃描到第一條記錄就停止了，這一點我們從執行計劃中也可以得到驗證：

bill@bill=>explain analyze select count(*) from t1;QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------Finalize Aggregate (cost=14542.55..14542.56 rows=1 width=8) (actual time=86.535..86.535 rows=1 loops=1)-> Gather (cost=14542.33..14542.54 rows=2 width=8) (actual time=86.396..87.819 rows=3 loops=1)Workers Planned: 2Workers Launched: 2-> Partial Aggregate (cost=13542.33..13542.34 rows=1 width=8) (actual time=82.845..82.846 rows=1 loops=3)-> Parallel Seq Scan on t1 (cost=0.00..12500.67 rows=416667 width=0) (actual time=0.009..47.854 rows=333333 loops=3)Planning Time: 0.087 msExecution Time: 87.880 ms (8 rows)bill@bill=>explain analyze select count(*) from t1 where ctid='(0,1)';QUERY PLAN --------------------------------------------------------------------------------------------------Aggregate (cost=1.31..1.32 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=1)-> Tid Scan on t1 (cost=0.00..1.31 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=1)TID Cond: (ctid = '(0,1)'::tid)Planning Time: 0.080 msExecution Time: 0.039 ms (5 rows)

總結：
這個SQL的優化與其說用到了pg中ctid的特性，最關鍵的我覺得其實還是我們在優化SQL的時候要意識到SQL改寫的等價性：某些SQL語句其實從實際的應用來說換個思路往往會有意想不到的收獲。

總結

以上是生活随笔為你收集整理的PostgreSQL巧用ctid等价改写SQL的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。