PostgreSQL性能優化舉例分析

這篇文章主要講解了“PostgreSQL性能優化舉例分析”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“PostgreSQL性能優化舉例分析”吧！

背景

在空間數據中，通常會有軌跡、點、面的數據，假設有兩張表，一張為面的表，一張為點的表，使用包含 ST_xxxx(c.geom, p.geom) 來進行JOIN（例如以面為單位，聚合統計點的數量）。

例子

測試數據：

Setup

First download some polygons and some points.

Admin 0 - Countries

Populated Places

Load the shapes into your database.

shp2pgsql -s 4326 -D -I ne_10m_admin_0_countries.shp countries | psql performance  
  
shp2pgsql -s 4326 -D -I ne_10m_populated_places.shp places | psql performance

包含大量POINT的空間對象

SELECT count(*)   
  FROM countries   
  WHERE ST_NPoints(geom) > (8192 / 16);

1、使用默認的壓縮格式時，這個空間JOIN查詢，耗時25秒。

SELECT count(*), c.name   
  FROM countries c   
  JOIN places p   
  ON ST_Intersects(c.geom, p.geom)   
  GROUP BY c.name;

使用PERF或oprofile跟蹤其耗時的代碼

發現問題是解壓縮的pglz_decompress 接口造成的。

2、將空間字段改成非壓縮格式，耗時降到4秒。

-- Change the storage type  
ALTER TABLE countries  
  ALTER COLUMN geom  
  SET STORAGE EXTERNAL;  
  
-- Force the column to rewrite  
UPDATE countries  
  SET geom = ST_SetSRID(geom, 4326);  
  
vacuum full countries;   
  
-- Re-run the query    
SELECT count(*), c.name   
  FROM countries c   
  JOIN places p   
  ON ST_Intersects(c.geom, p.geom)   
  GROUP BY c.name;

感謝各位的閱讀，以上就是“PostgreSQL性能優化舉例分析”的內容了，經過本文的學習后，相信大家對PostgreSQL性能優化舉例分析這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！