PostgreSQL Select Index Optimization

Source: Internet
Author: User

Tags: Postgresq search image key performance query int over post

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

Tags Index: