[postgis-devel] performance of force_collection()
Ron Mayer
rm_postgis at cheapcomplexdevices.com
Thu Feb 24 14:20:39 PST 2005
MapServer seems to enjoy wrapping all it's queries with
"force_collection" and "force_2d" like this:
asbinary(force_collection(force_2d(the_geom)),'NDR')
When my the_geom is already a 2D GeometryCollection, I would
expect these to be no-ops; and execute very quickly. However
they (force_collection in particular) appear to be taking
15-20% of the performance of many of my queries [as shown
in "\d table" and "explain analyze" results below].
I think the issue is that force_collection() is always
deserializing and re-serializing the geometry -- which
can apparently be quite expensive on large geometries. I
believe the following patch avoids the problem; but
aren't totally sure because I don't know if TYPE_SETTYPE()
only works on a LWGEOM's type or if it can work on
a PG_LWGEOM's type as well.
=====================================================================
maps:~/apps/tmp/postgresql-8.0.1/contrib/postgis-cvs/lwgeom> diff -u
lwgeom_functions_basic.c.bak lwgeom_functions_basic.c
--- lwgeom_functions_basic.c.bak 2005-02-24 12:50:37.000000000 -0800
+++ lwgeom_functions_basic.c 2005-02-24 14:06:07.000000000 -0800
@@ -1290,15 +1290,14 @@
int SRID;
BOX2DFLOAT4 *bbox;
+ if ( TYPE_GETTYPE(geom->type) >= MULTIPOINTTYPE ) {
+ TYPE_SETTYPE(geom->type, COLLECTIONTYPE);
+ PG_RETURN_POINTER(geom);
+ }
+
// deserialize into lwgeoms[0]
lwgeom = lwgeom_deserialize(SERIALIZED_FORM(geom));
- // alread a multi*, just make it a collection
- if ( TYPE_GETTYPE(lwgeom->type) >= MULTIPOINTTYPE )
- {
- TYPE_SETTYPE(lwgeom->type, COLLECTIONTYPE);
- }
-
// single geom, make it a collection
else
{
=====================================================================
Or should I be asking the mapserver guys not to put that
check in?
fli=# \d roadtrl_interstate;
Table "usgs.roadtrl_interstate"
Column | Type | Modifiers
----------+----------+-----------
name | text |
type | text |
num | text |
state | text |
the_geom | geometry |
Indexes:
"roadtrl_interstate__type_gist" gist (the_geom)
Check constraints:
"enforce_geotype_the_geom" CHECK (geometrytype(the_geom) =
'GEOMETRYCOLLECTION'::text OR the_geom IS NULL)
"enforce_dims_the_geom" CHECK (ndims(the_geom) = 2)
"enforce_srid_the_geom" CHECK (srid(the_geom) = -1)
fli=# explain analyze SELECT type::text,name::text,asbinary(
the_geom ,'NDR'),OID::text from usgs.roadtrl_interstate;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Seq Scan on roadtrl_interstate (cost=0.00..325.21 rows=7947
width=100) (actual time=0.114..301.396 rows=7947 loops=1)
Total runtime: 320.176 ms
(2 rows)
fli=# explain analyze SELECT
type::text,name::text,asbinary(force_collection(force_2d(the_geom)),'NDR'),OID::text
from usgs.roadtrl_interstate;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Seq Scan on roadtrl_interstate (cost=0.00..364.94 rows=7947
width=100) (actual time=0.164..396.505 rows=7947 loops=1)
Total runtime: 418.538 ms
(2 rows)
fli=# explain analyze SELECT
type::text,name::text,asbinary(force_2d(the_geom),'NDR'),OID::text from
usgs.roadtrl_interstate;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Seq Scan on roadtrl_interstate (cost=0.00..345.07 rows=7947
width=100) (actual time=0.116..306.993 rows=7947 loops=1)
Total runtime: 325.828 ms
(2 rows)
fli=# explain analyze SELECT
type::text,name::text,asbinary(force_collection(the_geom),'NDR'),OID::text
from usgs.roadtrl_interstate;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Seq Scan on roadtrl_interstate (cost=0.00..345.07 rows=7947
width=100) (actual time=0.136..397.267 rows=7947 loops=1)
Total runtime: 419.127 ms
(2 rows)
fli=#
fli=# select (418.5-320.1)/418.5;
?column?
------------------------
0.23512544802867383513
(1 row)
fli=# -- and they really do seem to have no effect.
fli=# SELECT
type::text,name::text,asbinary(force_collection(force_2d(the_geom)),'NDR'),OID::text
from usgs.roadtrl_interstate limit 2;
type | name |
asbinary
$
------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------$
I | Biz I-75 |
\001\007\000\000\000\001\000\000\000\001\002\000\000\000\002\000\000\000\000\000\000`\224\026U\300\000\000\000\240\217=G@\000\000\000\000\243\026U\300\000\000\000`o at G@
$
I | I-29 |
\001\007\000\000\000\001\000\000\000\001\002\000\000\000\006\000\000\000\000\000\000`\012FX\300\000\000\000@\344\277G@\000\000\000\240\257EX\300\000\000\000@\342\306G@\000\000\000\240hEX\300\000\000\000\000I\311G@\000\000\000@\242EX\300\000\000$
(2 rows)
fli=# SELECT type::text,name::text,asbinary(the_geom,'NDR'),OID::text
from usgs.roadtrl_interstate limit 2;
type | name |
asbinary
$
------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------$
I | Biz I-75 |
\001\007\000\000\000\001\000\000\000\001\002\000\000\000\002\000\000\000\000\000\000`\224\026U\300\000\000\000\240\217=G@\000\000\000\000\243\026U\300\000\000\000`o at G@
$
I | I-29 |
\001\007\000\000\000\001\000\000\000\001\002\000\000\000\006\000\000\000\000\000\000`\012FX\300\000\000\000@\344\277G@\000\000\000\240\257EX\300\000\000\000@\342\306G@\000\000\000\240hEX\300\000\000\000\000I\311G@\000\000\000@\242EX\300\000\000$
(2 rows)
More information about the postgis-devel
mailing list