当前的PostgreSQL版本(包括9.6)在Toast表方面存在查询优化器的弱点。Toast表是一种用于存储不适合正常数据页(如长文本、图像或具有许多顶点的复杂几何图形)的大值(从数据大小的意义上)的“扩展空间”,请参见 Toast的PostgreSQL文档 了解更多信息)。
如果您碰巧有一个包含相当大的几何图形的表,但它们的行数不是太多(就像一个包含所有欧洲国家边界的高分辨率表),那么问题就会出现。然后数据表本身很小,但它使用了大量的烤面包空间。在我们的示例中,表本身有大约80行,只使用了3个数据页,但toast表使用了8225页。
现在发出一个使用几何运算符的查询 & & 以搜索仅与这些行中的极少数行匹配的边界框。现在,查询优化器看到该表只有3页和80行。它估计,在这样一个小表上进行顺序扫描比使用索引快得多。因此,它决定忽略GIST指数。通常情况下,这种估计是正确的。但在我们的案例中, & & 操作员必须从磁盘获取每个几何图形来比较边界框,从而也读取所有吐司页面。
要查看您是否存在此问题,请使用“EXPLAIN ANALYE”PostgreSQL命令。有关更多信息和技术细节,您可以阅读PostgreSQL性能邮件列表上的线程: http://archives.postgresql.org/pgsql-performance/2005-02/msg00030.php
和更新的关于PostGIS的主题 https://lists.osgeo.org/pipermail/postgis-devel/2017-June/026209.html
PostgreSQL人员正试图通过使查询估计具有Toast感知来解决这个问题。目前,这里有两个变通办法:
第一个解决方法是强制查询规划器使用索引。在发出查询之前,将“set Enable_seqcan to off;”发送到服务器。这基本上迫使查询规划器尽可能避免顺序扫描。因此,它照常使用GIST指数。但是,必须在每个连接上设置该标志,并且它会导致查询规划器在其他情况下做出错误估计,因此您应该在查询之后将ENABLE_seqcan设置为ON;。
第二个解决方法是使顺序扫描的速度与查询规划者所认为的一样快。这可以通过创建一个“缓存”BBox的附加列并与之匹配来实现。在我们的示例中,命令如下所示:
SELECT AddGeometryColumn('myschema','mytable','bbox','4326','GEOMETRY','2'); UPDATE mytable SET bbox = ST_Envelope(ST_Force2D(geom));
现在更改您的查询以使用 & & 针对BBox而不是geom_Column的运算符,如:
SELECT geom_column FROM mytable WHERE bbox && ST_SetSRID('BOX3D(0 0,1 1)'::box3d,4326);
当然,如果您更改或添加行到MyTable,您必须保持BBox“同步”。执行此操作的最透明方式是触发器,但您也可以修改应用程序以使BBox列保持最新,或者在每次修改后运行上面的更新查询。
对于大多数是只读的表,并且大多数查询使用单个索引,PostgreSQL提供了CLUSTER命令。该命令以与索引条件相同的顺序对所有数据行进行物理重新排序,从而产生两个性能优势:首先,对于索引范围扫描,大大减少了数据表上的查找次数。其次,如果您的工作集集中在索引上的一些小间隔上,您将拥有更高效的缓存,因为数据行分布在较少的数据页上。(现在请阅读PostgreSQL手册中的集群命令文档。)
但是,目前PostgreSQL不允许在PostGIS GIST索引上进行群集,因为GIST索引只是忽略空值,因此您会收到如下错误消息:
lwgeom=# CLUSTER my_geom_index ON my_table; ERROR: cannot cluster when index access method does not handle null values HINT: You may be able to work around this by marking column "geom" NOT NULL.
正如提示消息告诉您的那样,可以通过向表添加“非空”约束来解决此缺陷:
lwgeom=# ALTER TABLE my_table ALTER COLUMN geom SET not null; ALTER TABLE
当然,如果您的几何图形列中实际上需要空值,这将不起作用。此外,您必须使用上面的方法来添加约束,使用类似“ALTER TABLE Blubb ADD CHECK(GEOMETRY IS NOT NULL);”这样的检查约束将不起作用。
有时,您的表中恰好有3D或4D数据,但始终使用仅输出2D几何图形的兼容OpenGIS的ST_AsText()或ST_AsBinary()函数来访问它。它们通过在内部调用ST_Force2D()函数来实现这一点,这为大型几何图形带来了巨大的开销。为了避免这种开销,可能可以一劳永逸地预先丢弃这些额外的维度:
UPDATE mytable SET geom = ST_Force2D(geom); VACUUM FULL ANALYZE mytable;
请注意,如果使用AddGeometryColumn()添加几何图形列,则几何图形尺寸将受到约束。要绕过它,您需要删除该约束。请记住更新GEOMETRY_COLUMNS表中的条目,然后重新创建约束。
对于大型表,明智的做法是通过WHERE子句和主键或其他可行的条件将更新限制到表的一部分,并在更新之间运行简单的“Vacuum;”,从而将此更新分成较小的部分。这极大地减少了对临时磁盘空间的需求。此外,如果您有混合的尺寸几何图形,则通过“WHERE DIMENSION(Geom)”限制更新 > 2“跳过重写已在2D中的几何图形。