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

This article mainly explains the "PostgreSQL performance optimization example analysis", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "PostgreSQL performance optimization example analysis" bar!

Background

In spatial data, there are usually tracks, points, and surfaces. Suppose there are two tables, one is a face table and the other is a point table. Use ST_xxxx (c.geom, p.geom) to JOIN (for example, aggregate the number of statistical points in terms of faces).

Examples

Test data:

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

Spatial objects that contain a large number of POINT

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

1. When using the default compression format, this spatial JOIN query takes 25 seconds.

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

Use PERF or oprofile to track its time-consuming code

The problem was found to be caused by the unzipped pglz_decompress interface.

2. Change the space field to an uncompressed format, reducing the time to 4 seconds.

-- 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 Thank you for your reading, the above is the content of "PostgreSQL performance optimization example analysis", after the study of this article, I believe you have a deeper understanding of the PostgreSQL performance optimization example analysis of this problem, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.