[postgis-users] GeometryCollection vs LineString speed

[Ron Mayer]

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=#