Is ST_GeomFromText better than providing straight geometry?
I recently worked with postgis and in my query, if I use ST_GeomFromText, it is faster than running a subquery to get geometry.
I thought ST_GeomFromText would be more expensive, but after running many tests every time I get the result faster, my question is Is there any explanation behind this? because for me getting the geometry directly in a subquery is better than getting the geometry as text and then adding it as geomechanical text.
Thanks Sara
source to share
Your problem is that the problems are different. ST_GeomFromText will be an immutable function, so the output only depends on the input. This means that the scheduler can execute it once at the beginning of the request. Running a subquery means you have to look for geometry, which means disk access, etc. First, you have a bit of cpu activity done once for a query, and in the second you have a disk lookup.
So the answer depends to some extent on what you do with it. In general, you can assume that the optimizer will handle things like type conversions on the input, where they don't depend on settings, pretty well.
Think of it this way.
SELECT * FROM mytable WHERE my_geom = ST_GeomFromText(....);
This translates to something like the following pseudocode:
private_geom = ST_GeomFromText(....);
SELECT * FROM mytable WHERE my_geom = private_geom;
This request will then be planned and executed.
Obviously you don't want to add round trips just to avoid searching in queries, but where you know the geometry, you can specify it via ST_GeomFromText(....)
in your query.
source to share