我在PostgreSQL中有一个具有以下结构的数据库:
Column | Type | Collation | Nullable | Default
-------------+-----------------------+-----------+----------+------------------------------------------------
vessel_hash | integer | | not null | nextval('samplecol_vessel_hash_seq'::regclass)
status | character varying(50) | | |
station | character varying(50) | | |
speed | character varying(10) | | |
longitude | numeric(12,8) | | |
latitude | numeric(12,8) | | |
course | character varying(50) | | |
heading | character varying(50) | | |
timestamp | character varying(50) | | |
the_geom | geometry | | |
Check constraints:
"enforce_dims_the_geom" CHECK (st_ndims(the_geom) = 2)
"enforce_geotype_geom" CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL)
"enforce_srid_the_geom" CHECK (st_srid(the_geom) = 4326)
数据库包含 ~146.000.000 条记录,包含数据的表的大小为:
public | samplecol | table | postgres | 31 GB |
我尝试使用以下命令在几何字段the_geom
上创建 GIST 索引:
create index samplecol_the_geom_gist on samplecol using gist (the_geom);
但需要太长时间。它已经运行了 2 小时。
基于这个问题 300GB Postgis 表的索引速度慢 提问,在创建索引之前,我在 psql 控制台中执行:
ALTER SYSTEM SET maintenance_work_mem = '1GB';
ALTER SYSTEM
SELCT pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
但是索引创建需要很长时间。有谁知道为什么?如何解决这个问题?
恐怕你得坐下来。
除了高maintenance_work_mem
,没有真正的调谐选项。 增加max_wal_size
会有所帮助,因为您将获得更少的检查点。
如果您不能长时间使用ACCESS EXCLUSIVE
锁,请尝试CREATE INDEX CONCURRENTLY
,这会更慢,但不会阻止并发数据库活动。