300GB Postgis表的索引编制缓慢 [英] Slow indexing of 300GB Postgis table

问题描述

我正在将300GB的轮廓线数据加载到postgis表中.为了加快该过程，我读到最快是首先加载数据，然后创建索引是最快的.加载数据仅花费了大约2天的时间，但是现在我已经在等待索引大约30天了，它仍然没有准备好.

I am loading about 300GB of contour line data in to an postgis table. To speed up the process i read that it is fastest to first load the data, and then create an index. Loading the data only took about 2 days, but now I have been waiting for the index for about 30 days, and it is still not ready.

查询是:

create index idx_contour_geom on contour.contour using gist(geom);

我在pgadmin4中运行它，此后的progran的内存消耗从500MB到100GB ++不等.

I ran it in pgadmin4, and the memory consumption of the progran has varied from 500MB to 100GB++ since.

使用这么长时间索引这样的数据库是否正常?

Is it normal to use this long time to index such a database?

有关如何加快该过程的任何提示吗?

Any tips on how to speed up the process?

数据是从1x1度(纬度/经度)像元(约30.000个像元)加载的，因此没有行的边界框大于1x1度，则其中大多数应该小得多.它们在EPSG:4326投影中，唯一的属性是高度和几何形状(几何).

The data is loaded from 1x1 degree (lat/lon) cells (about 30.000 cells) so no line has a bounding box larger than 1x1 degree, most of then should be much smaller. They are in EPSG:4326 projection and the only attributes are height and the geometry (geom).

推荐答案

我将maintenance_work_mem更改为1GB，并停止了对磁盘的所有其他写入操作(很多插入操作附加了ANALYZE，这占用了大量资源).我现在跑了23分钟.

I changed the maintenance_work_mem to 1GB and stopped all other writing to disk (a lot of insert opperations had ANALYZE appended, which took a lot of resources). I now ran in 23min.

这篇关于300GB Postgis表的索引编制缓慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！