Shulou(Shulou.com)05/31 Report--

This article shows you how to improve the performance of PostgreSQL in Instagram, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

1. Local index

If we often need to filter data by a fixed feature that exists only in a small number of branches, in this case, the local index is very effective.

For example, when Instagram searches for tags, we need to find tags with a lot of photos. We usually use technologies such as ElasticSearch for advanced search, but here we only rely on the query ability of the database. Let's take a look. Query by tag and sort by the number of photos. How does Postgres do it:

EXPLAIN ANALYZE SELECT id from tags WHERE name LIKE 'snow%' ORDER BY media_count DESC LIMIT 10 QUERY PLAN-Limit (cost=1780.73..1780.75 rows=10 width=32) (actual time=215.211..215.228 rows=10 loops=1)-> Sort (cost=1780.73..1819.36 rows=15455 width=32) (actual time=215.209..215.215 rows=10 loops=1) Sort Key: media_count Sort Method: top-N heapsort Memory: 25kB-> Index Scan using tags_search on tags_tag (cost=0.00..1446.75 rows=15455 width=32) (actual time=0.020..162.708 rows=64572 loops=1) Index Cond: ((name):: text ~ > = ~ 'snow'::text) AND ((name):: text ~ = 100

Then query and take a look at the new query plan:

EXPLAIN ANALYZE SELECT * from tags WHERE name LIKE 'snow%' AND media_count > = 100 ORDER BY media_count DESC LIMIT 10 QUERY PLAN Limit (cost=224.73..224.75 rows=10 width=32) (actual time=3.088..3.105 rows=10 loops=1)-> Sort (cost=224.73..225.15 rows=169 width=32) (actual time=3.086..3.090 rows=10 loops=1) Sort Key: media_count Sort Method: top-N heapsort Memory: 25kB-> Index Scan using tags_tag_name_idx on tags_tag (cost=0.00..221.07 rows=169 width=32) (actual time=0.021..2.360 Rows=924 loops=1) Index Cond: ((name):: text ~ > = ~ 'snow'::text) AND ((name):: text ~

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.