PostgreSQL Select 索引最佳化

標籤：postgresq   搜尋   圖片   key   效能   查詢   int   over   post   

使用 gin() 建立全文索引後，雖然有走索引，但是當結果集很大時，查詢效率還是很底下，

SELECT keyword,avg_mon_search,competition,impressions,ctr,position,suggest_bid,click,update_time 
FROM keyword 
WHERE 
update_time is not null and plainto_tsquery(‘driver‘) @@ keyword_participle  
ORDER BY avg_mon_search DESC  
LIMIT 500 OFFSET 0;

 

背景： keyword 表中有八千萬行資料，建立了 gin( keyword_participle ) 索引，以及其他排序欄位的 BTREE 索引

分析：當查詢當個單詞時，雖然有走全文索引，但是由於返回的結果集很大，有二十多萬行資料，而且返回後需要再次進行排序，導致效能嚴重下降，

 

處理方法：限制全文索引返回的結果集行數，結果集變小了，也就減少了排序消耗的時間，況且全文索引分詞返回的這麼多資料，使用者只是查看前面一部分，通過這種方式讓使用者完善搜尋字詞，知道找到自己想要的結果。

SELECTkeyword,avg_mon_search,competition,impressions,ctr,position,suggest_bid,click,update_time, count(*) over() as res_count FROM  (SELECT keyword,avg_mon_search,competition,impressions,ctr,position,suggest_bid,click,update_time 
  FROM keyword WHERE update_time is not null AND avg_mon_search > 0 AND plainto_tsquery(‘english_nostop‘, ‘driver‘) @@ keyword_participle limit 20000 
 ) AS tmpORDER BY avg_mon_search DESC  LIMIT 500 OFFSET 0;

　　

 

PostgreSQL Select 索引最佳化