There is only one way: measure. Try to obtain a query plan, and try to read it. Try to isolate a query from the logfile, edit it to an executable (non-parameterised) form, and submit it manually (in psql). Try to tune it, and see where it hurts.
Geometry joins can be costly in term of CPU, if many (big) polygons have to be joined, and if their bounding boxes have a big chance to overlap. In the extreme case, preselect on other criteria (eg zipcode, if available) or maintain cache tables of matching records.
Use statistics and autovacuum. ESRI is still tied to PostgreSQL-8.3-something, where these were not run by default.
It is OK to add PK/FK constraints or additional indexes to your schema. The DBMS will pick them up if appropriate. ESRI will ignore such non-GIS stuff. (ESRI only uses its own meta-catalogs, ignoring the system catalogs.)
Don't upgrade above 8.4.something. ESRI still relies on floating-point datetime / timestamps , which were the default some time ago. (Also they fail to deliver 64bit binaries.)
When I had to deal with spatial data, I tended to precalulate the values and store them. Yes that makes for a big table but it is much faster to query when you only do the complex calculation once on data entry. Data entry does take longer though. I was in a situation where all my spacial data came from a monthly load, so precalculating wasn't too bad.
Ensure that your geodatabase is properly indexed. Indexes can significantly speed up query performance, especially for large datasets. Focus on creating indexes for fields that are frequently used in joins and queries.
Regularly tune your database settings. This includes adjusting memory allocation, query optimization settings, and other database-specific parameters. ESRI provides guidelines for tuning databases for better performance.
Instead of relying solely on SQL joins, use ArcGIS relationship classes. These are designed to handle relationships more efficiently within the geodatabase environment.
Ensure that spatial indexes are properly configured. Spatial indexes can greatly improve the performance of spatial queries by reducing the amount of data that needs to be scanned.
发布评论
评论（3）
只有一种方法：测量。尝试获取一个查询计划，并尝试读取它。尝试将查询与日志文件隔离，将其编辑为可执行（非参数化）形式，然后手动提交（在 psql 中）。尝试调整它，看看哪里痛。
如果必须连接许多（大）多边形，并且它们的边界框有很大机会重叠，则几何连接可能会占用 CPU 资源。在极端情况下，预选择其他条件（例如邮政编码，如果可用）或维护匹配记录的缓存表。
使用统计和自动清理。 ESRI 仍然与 PostgreSQL-8.3-something 相关，默认情况下它们不运行。
可以向您的模式添加 PK/FK 约束或附加索引。如果合适的话，DBMS 将选取它们。 ESRI 将忽略此类非 GIS 内容。 （ESRI 仅使用自己的元目录，忽略系统目录。）
不要升级到 8.4.something 以上。 ESRI 仍然依赖浮点日期时间/时间戳，这是不久前的默认值。 （而且他们也无法提供 64 位二进制文件。）
There is only one way: measure. Try to obtain a query plan, and try to read it. Try to isolate a query from the logfile, edit it to an executable (non-parameterised) form, and submit it manually (in psql). Try to tune it, and see where it hurts.
Geometry joins can be costly in term of CPU, if many (big) polygons have to be joined, and if their bounding boxes have a big chance to overlap. In the extreme case, preselect on other criteria (eg zipcode, if available) or maintain cache tables of matching records.
Use statistics and autovacuum. ESRI is still tied to PostgreSQL-8.3-something, where these were not run by default.
It is OK to add PK/FK constraints or additional indexes to your schema. The DBMS will pick them up if appropriate. ESRI will ignore such non-GIS stuff. (ESRI only uses its own meta-catalogs, ignoring the system catalogs.)
Don't upgrade above 8.4.something. ESRI still relies on floating-point datetime / timestamps , which were the default some time ago. (Also they fail to deliver 64bit binaries.)
当我必须处理空间数据时，我倾向于预先计算值并存储它们。是的，这会产生一个大表，但是当您只在数据输入时进行一次复杂的计算时，查询速度要快得多。但数据输入确实需要更长的时间。我的情况是，我的所有空间数据都来自每月的负载，所以预先计算还不错。
When I had to deal with spatial data, I tended to precalulate the values and store them. Yes that makes for a big table but it is much faster to query when you only do the complex calculation once on data entry. Data entry does take longer though. I was in a situation where all my spacial data came from a monthly load, so precalculating wasn't too bad.
确保您的地理数据库已正确建立索引。索引可以显着提高查询性能，尤其是对于大型数据集。重点为连接和查询中经常使用的字段创建索引。
定期调整您的数据库设置。这包括调整内存分配、查询优化设置和其他特定于数据库的参数。 ESRI 提供了调整数据库以获得更好性能的指南。
不要仅仅依赖 SQL 连接，而是使用 ArcGIS 关系类。这些旨在更有效地处理地理数据库环境中的关系。
确保空间索引配置正确。空间索引可以通过减少需要扫描的数据量来极大地提高空间查询的性能。
Ensure that your geodatabase is properly indexed. Indexes can significantly speed up query performance, especially for large datasets. Focus on creating indexes for fields that are frequently used in joins and queries.
Regularly tune your database settings. This includes adjusting memory allocation, query optimization settings, and other database-specific parameters. ESRI provides guidelines for tuning databases for better performance.
Instead of relying solely on SQL joins, use ArcGIS relationship classes. These are designed to handle relationships more efficiently within the geodatabase environment.
Ensure that spatial indexes are properly configured. Spatial indexes can greatly improve the performance of spatial queries by reducing the amount of data that needs to be scanned.