[postgis-users] GeometryCollection vs LineString speed
strk at refractions.net
strk at refractions.net
Wed Feb 23 01:05:38 PST 2005
What postgis version ?
--strk;
On Wed, Feb 23, 2005 at 01:08:46AM -0800, Ron Mayer wrote:
> Should I expect "&&" to be much faster on GeometryCollections
> than on LineStrings?
>
> I have two tables that differ only in whether the geometry is a
> linestring or a geometrycollection [\d tables shown below].
>
> Both have an index on the geometry column.
> Both are clustered on the geometry column.
>
> When I do queries, I was somewhat surprised to find the one with
> GEOMETRYCOLLECTION to be about twice as fast as LINESTRING.
>
> TIME | what..
> -----------+-----------------------------------
> 3.71 sec | seq scan using LINESTRING
> 1.51 sec | seq scan using GEOMETRYCOLLECTION
> .59 sec | index scan using LINESTRING
> .37 sec | index scan using GEOMETRYCOLLECTION
>
> Does this suggest that I should be using GEOMETRYCOLLECTION for
> roads on big maps? Or that I add another column for the box3d
> itself?
>
>
> Explain analyze output and table definitions shown below...
>
> Thanks,
> Ron
>
>
> fli=# explain analyze SELECT 1 from rtgr.lines WHERE the_geom &&
> setSRID('BOX3D(-84.45189 30.3162978571429,-84.10464
> 30.5544121428571)'::BOX3D, find_srid('','rtgr.line$
>
> QUERY PLAN $
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------$
> Index Scan using rtgr_lines__gist on lines (cost=0.00..23124.39
> rows=7146 width=0) (actual time=0.096..554.138 rows=17997 loops=1)
> Index Cond: (the_geom &&
> '01030000000100000005000000EDD808C4EB1C55C0403978E5F8503E40EDD808C4EB1C55C0971346F4ED8D3E409F76F86BB20655C0971346F4ED8D3E409F76F86BB20655C0$
> Total runtime: 593.868 ms
> (3 rows)
>
>
> fli=# explain analyze SELECT 1 from rtgr.lines2 WHERE the_geom &&
> setSRID('BOX3D(-84.45189 30.3162978571429,-84.10464
> 30.5544121428571)'::BOX3D, find_srid('','rtgr.lin$
>
> QUERY PLAN $
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------$
> Index Scan using rtgr_lines2__gist on lines2 (cost=0.00..25437.85
> rows=7615 width=0) (actual time=0.073..337.110 rows=17997 loops=1)
> Index Cond: (the_geom &&
> '01030000000100000005000000EDD808C4EB1C55C0403978E5F8503E40EDD808C4EB1C55C0971346F4ED8D3E409F76F86BB20655C0971346F4ED8D3E409F76F86BB20655C0$
> Total runtime: 375.467 ms
> (3 rows)
>
>
>
> fli=# set enable_indexscan=false;
> SET
> fli=# explain analyze SELECT 1 from rtgr.lines WHERE the_geom &&
> setSRID('BOX3D(-84.45189 30.3162978571429,-84.10464
> 30.5544121428571)'::BOX3D, find_srid('','rtgr.lines','the_geom') );
>
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Seq Scan on lines (cost=0.00..8543.50 rows=2 width=0) (actual
> time=0.051..3682.902 rows=17997 loops=1)
> Filter: (the_geom &&
> '01030000000100000005000000EDD808C4EB1C55C0403978E5F8503E40EDD808C4EB1C55C0971346F4ED8D3E409F76F86BB20655C0971346F4ED8D3E409F76F86BB20655C0403978E5F8503E40EDD808C4EB1C55C0403978E5F8503E40'::geometry)
> Total runtime: 3714.877 ms
> (3 rows)
>
> fli=# explain analyze SELECT 1 from rtgr.lines2 WHERE the_geom &&
> setSRID('BOX3D(-84.45189 30.3162978571429,-84.10464
> 30.5544121428571)'::BOX3D, find_srid('','rtgr.lines2','the_geom') );
>
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Seq Scan on lines2 (cost=0.00..9304.46 rows=7676 width=0) (actual
> time=0.268..1562.084 rows=17997 loops=1)
> Filter: (the_geom &&
> '01030000000100000005000000EDD808C4EB1C55C0403978E5F8503E40EDD808C4EB1C55C0971346F4ED8D3E409F76F86BB20655C0971346F4ED8D3E409F76F86BB20655C0403978E5F8503E40EDD808C4EB1C55C0403978E5F8503E40'::geometry)
> Total runtime: 1587.488 ms
> (3 rows)
>
> fli=# \d rtgr.lines;
> Table "rtgr.lines"
> Column | Type | Modifiers
> -----------+----------------------+-----------
> tigerfile | integer |
> tlid | integer |
> cfcc | character varying(3) |
> name | text |
> the_geom | geometry | not null
> Indexes:
> "lines__tlid" btree (tlid)
> "rtgr_lines2__name" btree (name)
> "rtgr_lines__gist" gist (the_geom) CLUSTER
> Check constraints:
> "enforce_srid_the_geom" CHECK (srid(the_geom) = -1)
> "enforce_dims_the_geom" CHECK (ndims(the_geom) = 2)
> "enforce_geotype_the_geom" CHECK (geometrytype(the_geom) =
> 'LINESTRING'::text OR the_geom IS NULL)
> fli=# \d rtgr.lines2
> Table "rtgr.lines2"
> Column | Type | Modifiers
> -----------+----------------------+-----------
> tigerfile | integer |
> tlid | integer |
> cfcc | character varying(3) |
> name | text |
> the_geom | geometry | not null
> Indexes:
> "rtgr_lines2__gist" gist (the_geom) CLUSTER
> 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=#
> fli=# select relname,reltuples,relpages from pg_class where relname like
> 'lines%';
> relname | reltuples | relpages
> -------------+-----------+----------
> lines | 305640 | 4708
> lines2 | 305640 | 5467
> (2 rows)
>
> fli=#
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
More information about the postgis-users
mailing list