en to zh_Hans translation catalogue (23 entries)

note: Tag: title
en: Performance Tips
zh_Hans: 性能优化技巧

note: Tag: title
en: Small tables of large geometries
zh_Hans: 大几何形状的小表

note: Tag: title
en: Problem description
zh_Hans: 问题描述

note: Tag: para
en: Current PostgreSQL versions (including 9.6) suffer from a query optimizer weakness regarding TOAST tables. TOAST tables are a kind of "extension room" used to store large (in the sense of data size) values that do not fit into normal data pages (like long texts, images or complex geometries with lots of vertices), see <ulink url="http://www.postgresql.org/docs/current/static/storage-toast.html">the PostgreSQL Documentation for TOAST</ulink> for more information).
zh_Hans: 当前版本的PostgreSQL（包括9.6）在TOAST表后面的查询优化器中存在弱点。TOAST 表是一种“扩展室”，用于存储不适合普通数据页面（例如长文本、图像或具有许多顶点的复杂几何图形）的巨大值（就数据大小而言）。 有关更多信息，请参阅 <ulink url="http://www.postgresql.org/docs/current/static/storage-toast.html">Toast的PostgreSQL文档</ulink>。

note: Tag: para
en: The problem appears if you happen to have a table with rather large geometries, but not too many rows of them (like a table containing the boundaries of all European countries in high resolution). Then the table itself is small, but it uses lots of TOAST space. In our example case, the table itself had about 80 rows and used only 3 data pages, but the TOAST table used 8225 pages.
zh_Hans: 如果您碰巧有一个具有相当大的几何形状的表格，但它们的行数不是太多（例如包含所有欧洲国家的高分辨率边界的表格），则会出现问题。 那么表本身很小，但是它使用了大量的TOAST空间。 在我们的示例中，表本身约有 80 行，仅使用 3 个数据页，但 TOAST 表使用 8225 个页面。

note: Tag: para
en: Now issue a query where you use the geometry operator &amp;&amp; to search for a bounding box that matches only very few of those rows. Now the query optimizer sees that the table has only 3 pages and 80 rows. It estimates that a sequential scan on such a small table is much faster than using an index. And so it decides to ignore the GIST index. Usually, this estimation is correct. But in our case, the &amp;&amp; operator has to fetch every geometry from disk to compare the bounding boxes, thus reading all TOAST pages, too.
zh_Hans: 现在，让我们使用几何运算符&amp;&amp;来查询很少匹配的边界框。对于查询优化器，该表似乎只有 3 页和 80 行。 优化程序估计按顺序遍历小表比使用索引更快。然后我们决定忽略 GiST 索引。 通常，这种做法是正确的。 但是，在这种情况下，&amp;&amp;运算符必须从磁盘调用所有几何图形并将其与边界框进行比较，因此还必须调用所有TOAST页面。

note: Tag: para
en: To see whether your suffer from this issue, use the "EXPLAIN ANALYZE" postgresql command. For more information and the technical details, you can read the thread on the PostgreSQL performance mailing list: <ulink url="http://archives.postgresql.org/pgsql-performance/2005-02/msg00030.php">http://archives.postgresql.org/pgsql-performance/2005-02/msg00030.php</ulink>
zh_Hans: 要查看您是否遇到此问题，请使用“EXPLAIN ANALYZE”postgresql 命令。 有关更多信息和技术细节，您可以阅读 PostgreSQL 性能邮件列表上的线程：<ulink url="http://archives.postgresql.org/pgsql-performance/2005-02/msg00030.php">http://archives.postgresql.org/pgsql-performance/2005-02/msg00030.php</ulink>。

note: Tag: para
en: and newer thread on PostGIS <ulink url="https://lists.osgeo.org/pipermail/postgis-devel/2017-June/026209.html">https://lists.osgeo.org/pipermail/postgis-devel/2017-June/026209.html</ulink>
zh_Hans: 另请参阅 PostGIS 中的新线程 <ulink url="https://lists.osgeo.org/pipermail/postgis-devel/2017-June/026209.html">https://lists.osgeo.org/pipermail/postgis-devel/2017-June/026209.html</ulink>。

note: Tag: title
en: Workarounds
zh_Hans: 解决办法

note: Tag: para
en: The PostgreSQL people are trying to solve this issue by making the query estimation TOAST-aware. For now, here are two workarounds:
zh_Hans: PostgreSQL社区正试图通过制作 TOAST 感知查询引用来解决这个问题。目前，有两种解决办法。

note: Tag: para
en: The first workaround is to force the query planner to use the index. Send "SET enable_seqscan TO off;" to the server before issuing the query. This basically forces the query planner to avoid sequential scans whenever possible. So it uses the GIST index as usual. But this flag has to be set on every connection, and it causes the query planner to make misestimations in other cases, so you should "SET enable_seqscan TO on;" after the query.
zh_Hans: 第一个解决方法是强制查询规划器使用索引。发送“SET enable_seqscan TO off;” 在发出查询之前发送到服务器。这基本上迫使查询规划器尽可能避免顺序扫描。所以它像往常一样使用 GIST 索引。 但是必须在每个连接上设置此标志，并且它会导致查询规划器在其他情况下做出错误估计，因此您应该在查询后“SET enable_seqscan TO on;” 。

note: Tag: para
en: The second workaround is to make the sequential scan as fast as the query planner thinks. This can be achieved by creating an additional column that "caches" the bbox, and matching against this. In our example, the commands are like:
zh_Hans: 第二个解决方法是使顺序扫描与查询规划器认为的一样快。这可以通过创建一个“缓存”bbox的附加列并与之匹配来实现。在我们的示例中，命令如下：

note: Tag: para
en: Now change your query to use the &amp;&amp; operator against bbox instead of geom_column, like:
zh_Hans: 现在更改查询以对bbox使用&amp;&amp;运算符 而不是geom_column，例如：

note: Tag: para
en: Of course, if you change or add rows to mytable, you have to keep the bbox "in sync". The most transparent way to do this would be triggers, but you also can modify your application to keep the bbox column current or run the UPDATE query above after every modification.
zh_Hans: 当然，如果您更改或添加行到mytable，则必须保持 bbox“同步”。最透明的方法是使用触发器，但您也可以修改应用程序以保持 bbox 列最新或在每次修改后运行上面的 UPDATE 查询。

note: Tag: title
en: CLUSTERing on geometry indices
zh_Hans: 几何索引聚类

note: Tag: para
en: For tables that are mostly read-only, and where a single index is used for the majority of queries, PostgreSQL offers the CLUSTER command. This command physically reorders all the data rows in the same order as the index criteria, yielding two performance advantages: First, for index range scans, the number of seeks on the data table is drastically reduced. Second, if your working set concentrates to some small intervals on the indices, you have a more efficient caching because the data rows are spread along fewer data pages. (Feel invited to read the CLUSTER command documentation from the PostgreSQL manual at this point.)
zh_Hans: 对于大多数只读的表，以及大多数查询使用单个索引的情况，PostgreSQL 提供了 CLUSTER 命令。该命令按照与索引标准相同的顺序对所有数据行进行物理重新排序，从而产生两个性能优势：首先，对于索引范围扫描，数据表上的查找次数大大减少。其次，如果您的工作集集中在索引上的一些小间隔，则您的缓存会更有效，因为数据行分布在更少的数据页上。（此时请阅读 PostgreSQL 手册中的 CLUSTER 命令文档。）

note: Tag: para
en: However, currently PostgreSQL does not allow clustering on PostGIS GIST indices because GIST indices simply ignores NULL values, you get an error message like:
zh_Hans: 但是，由于 GiST 索引只是忽略 NULL 值，因此当前无法在 PostGIS 中对 GiST 索引进行聚类，并且您会收到以下错误消息：

note: Tag: para
en: As the HINT message tells you, one can work around this deficiency by adding a "not null" constraint to the table:
zh_Hans: 正如提示消息所说，向表添加“非空”限制将暂时解决此缺陷。 例如：

note: Tag: para
en: Of course, this will not work if you in fact need NULL values in your geometry column. Additionally, you must use the above method to add the constraint, using a CHECK constraint like "ALTER TABLE blubb ADD CHECK (geometry is not null);" will not work.
zh_Hans: 当然，如果您实际上需要几何列中的 NULL 值，则这将不起作用。 此外，您必须使用上述方法添加约束，使用 CHECK 约束，例如“ALTER TABLE blubb ADD CHECK (geometry is not null);” 不管用。

note: Tag: title
en: Avoiding dimension conversion
zh_Hans: 避免维度变换

note: Tag: para
en: Sometimes, you happen to have 3D or 4D data in your table, but always access it using OpenGIS compliant ST_AsText() or ST_AsBinary() functions that only output 2D geometries. They do this by internally calling the ST_Force2D() function, which introduces a significant overhead for large geometries. To avoid this overhead, it may be feasible to pre-drop those additional dimensions once and forever:
zh_Hans: 有时，您的表中碰巧有3D 或 4D 数据，但始终使用仅输出 2D 几何图形的符合OpenGIS 的 ST_AsText() 或 ST_AsBinary() 函数来访问它。他们通过内部调用 ST_Force2D() 函数来实现这一点，这会给大型几何图形带来巨大的开销。 为了避免这种开销，一次性预先删除这些额外的维度可能是可行的：

note: Tag: para
en: Note that if you added your geometry column using AddGeometryColumn() there'll be a constraint on geometry dimension. To bypass it you will need to drop the constraint. Remember to update the entry in the geometry_columns table and recreate the constraint afterwards.
zh_Hans: 请注意，如果您使用 AddGeometryColumn() 添加几何列，则几何维度将会受到限制。要绕过它，您需要删除约束。请记住更新geometry_columns 表中的条目并随后重新创建约束。

note: Tag: para
en: In case of large tables, it may be wise to divide this UPDATE into smaller portions by constraining the UPDATE to a part of the table via a WHERE clause and your primary key or another feasible criteria, and running a simple "VACUUM;" between your UPDATEs. This drastically reduces the need for temporary disk space. Additionally, if you have mixed dimension geometries, restricting the UPDATE by "WHERE dimension(geom)&gt;2" skips re-writing of geometries that already are in 2D.
zh_Hans: 对于大型表，明智的做法是通过 WHERE 子句和主键或其他可行标准将 UPDATE 限制为表的一部分，然后运行简单的“VACUUM;”，从而将此 UPDATE 划分为更小的部分。 在您的更新之间。 这大大减少了对临时磁盘空间的需求。此外，如果您有混合维度几何图形，则通过“WHEREdimension(geom)&gt;2”限制 UPDATE 会跳过对已经处于 2D 中的几何图形的重写。
