PostgreSQL Select Index Optimization

After using gin () to create a full-text index, the query is still very efficient when the result set is large, although there is a walk index.

Update_time is not null and plainto_tsquery (' driver ') @@ keyword_participle  
ORDER by Avg_mon_search DESC  
LIMIT OFFSET 0;

Background: The keyword table has 80 million rows of data, a gin (keyword_participle) index is established, and the BTREE index of the other sort fields

Analysis: When the query is a word, although there is a full-text index, but because the returned result set is large, there are more than 200,000 rows of data, and return after the need to sort again, resulting in a severe degradation of performance,

Processing method: Limit the number of result sets returned by the full-text index, the result set is smaller, also reduce the time of ordering, and the full-text index word return so much data, users just look at the previous part, in this way to let users refine the search term, know to find their own desired results.

Selectkeyword,avg_mon_search,competition,impressions,ctr,position,suggest_bid,click,update_time, COUNT (*) over () As Res_count from  
  
As Tmporder by Avg_mon_search DESC  LIMIT 0;

　　

PostgreSQL Select Index Optimization