[postgis-devel] performance of force_collection()
strk at refractions.net
strk at refractions.net
Fri Feb 25 00:12:52 PST 2005
Just another note. Your patch is not forcing a copy on DETOAST.
This would usually be the case, but if input is not a copy
editing it is an unsafe operation.
--strk;
On Thu, Feb 24, 2005 at 02:20:39PM -0800, Ron Mayer wrote:
> 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)
>
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel
More information about the postgis-devel
mailing list