ST_GeomFromText是否比提供直接几何更好? [英] Is ST_GeomFromText better than providing direct geometry?

问题描述

我最近一直在使用postgis，在我的查询中，如果我使用ST_GeomFromText，它的执行速度要比运行子查询来获取geom的执行速度快.

我以为ST_GeomFromText会更昂贵，但是每次我更快地获得结果后运行许多测试之后，我的问题这背后有什么解释吗? 因为对我来说，直接在子查询中获取geom优于将geom作为文本然后添加为GeomFromText.

谢谢， 萨拉

解决方案

您的问题是问题有所不同. ST_GeomFromText将是一个不可变的函数，因此输出仅取决于输入.这意味着计划者可以在查询开始时执行一次.运行子查询意味着您必须查找几何结构，这意味着需要进行磁盘访问等.首先，您需要执行一些CPU活动，对查询执行一次，然后进行磁盘查找.

所以答案在某种程度上取决于您使用它的方式.通常，您可以假定优化器可以很好地处理输入上的类型转换之类的事情，而这些转换并不依赖于设置.

这样想吧.

SELECT * FROM mytable WHERE my_geom = ST_GeomFromText(....);

这将转换为以下伪代码:

 private_geom = ST_GeomFromText(....);
 SELECT * FROM mytable WHERE my_geom = private_geom;

然后计划并执行该查询.

很显然，您不想为了避免在查询中进行查找而添加往返行程，但是在知道几何形状的地方，也可以通过查询中的ST_GeomFromText(....)指定它.

I have been working with postgis recently,and in my query if I use ST_GeomFromText it execute faster than running a sub-query to get geom.

I thought ST_GeomFromText will be more expensive but after running many tests every time I got the result faster, my question Is there any explanation behind this? because for me getting the geom directly in sub-query is better than getting geom as text then added as GeomFromText.

Thanks, Sara

解决方案

Your issue is that the issues are different. ST_GeomFromText is going to be an immutable function, so the output depends on the input only. This means the planner can execute it once at the beginning of the query. Running a subquery means you are having to look up the geometry, which means disk access, etc. In the first, you have a little bit of CPU activity, executed once for the query, and on the second you have disk lookups.

So the answer to some extent depends on what you are doing with it. In general, you can assume that the optimizer will handle things like type conversions on input, where those are not dependent on settings, quite well.

Think about it this way.

SELECT * FROM mytable WHERE my_geom = ST_GeomFromText(....);

This gets converted into something like the following pseudocode:

 private_geom = ST_GeomFromText(....);
 SELECT * FROM mytable WHERE my_geom = private_geom;

Then that query gets planned and executed.

Obviously you don't want to be adding round trips just to avoid in-query lookups, but where you know the geometry, you might as well specify it via ST_GeomFromText(....) in your query.

这篇关于ST_GeomFromText是否比提供直接几何更好?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！