[postgis-users] Performance: PostGIS vs Shape

[Johannes Sommer]

Hi everybody!

I made a little performance test: shape vs. postgis.
I always thought that a select query in a postgresql database with 
postgis extension
is much faster than a comparable query inside a Desktop GIS that has 
loaded shape-files.
So I compared various queries with shape-files (different sizes and 
geometry types) and relations in a postgis database.
The shapes I queried in the Desktop GISs have been converted to a 
postgis-layer.
I used Kosmo, Openjump and ArcMap as Desktop GISs.

However the result is the following: PostGIS is always slower than the 
Desktop GISs.

I know Openjump is using Quadtrees to index a shape-file, about Kosmo 
and ArcMap I have no idea, but I think they use an index too.
And in postgis I build an GiST- index on the queried relations and did a 
VACUUM ANALYZE on them.

So why is a (spatial) query in a database slower, than a query of a 
shape-file? Can anybody explain this to me?
Anyone with similar experiences?
Perhaps there is something wrong with the "select query" or with the 
database's properties?

System: 2,8 Ghz, 1 GB RAM, S-ATA HDD, Windows XP Pro
Software: Postgresql 8.2.5 with PostGIS 1.3
OpenJUMP 1.2 D
ArcMap 9.1

Thanks for your comments.

greetings,
Johannes Sommer

*A. MULTIPOLYGONS and POINTS*

   1.

      *shapes: *lsg (678 MULTIPOLYGONS), 14,5 MB, bze_wze, 52 KB, (386
      POINTS),

intersection

Results:

    *

      Openjump 1.2 D: 24 sec

    *

      ArcMap 9.1: 18 sec

    *

      Kosmo 1.1: 1:24 min

                      #

                        both are marked as „Layer in memory“: 0:59 min

   1.

      *postgis-layer: *lsg (678 MULTIPOLYGONS, GiST-Index on geometry),
      bze_wze (386 POINTS, GiST-Index on geometry)

      Result:

                +

                  Openjump 1.2 D: 24 sec

                +

                  Kosmo 1.1: 2:44 min

                      #

                        both are marked as „Layer in memory“: 1:00 min

                +

                  PostGIS-SQL:

                      #

                        intersection with && (bbox): 2:45 min

      SELECT intersection(a.geometry, b.geometry) AS intersection_geom,
      a.gid, a.tnr, a.ist_x, a.ist_y, a.holzboden, a.eu_punkt,
      b.rok_prj_nr, b.id, b.name, b.area_qm
      FROM lsg b, bze_wze a
      WHERE intersects(a.geometry, b.geometry); 

                      #

                        intersection with && (bbox): 2:24 min

    SELECT intersection(a.geometry, b.geometry) AS intersection_geom,
    a.*, b.rok_prj_nr, b.id, b.name, b.area_qm
    FROM lsg b, bze_wze a
    WHERE a.geometry && b.geometry
    AND intersects(a.geometry, b.geometry);


*B. POLYGONS and POINTS*

   1.

      *shapes:** *ffh_gebiete (2670 POLYGONS), 12,7 MB, bze_wze, 52 KB,
      (386 POINTS)

intersection

Results:

    *

      Openjump 1.2 D: 1 sec

    *

      ArcMap 9.1: 12 sec

    *

      Kosmo 1.1: 6 sec

          o

            Layer in memory: 1 sec

   2.

      *postgis-layer: *ffh_gebiete (2670 POLYGONS, GiST-Index on
      geometry), bze_wze (386 POINTS, GiST-Index on geometry)

      Results:

                +

                  Openjump 1.2 D: 1 sec

                +

                  Kosmo 1.1: 8 sec

                      #

                        Layer in memory: 2 sec

                +

                  PostGIS-SQL:

                      # intersection without && (bbox): 35 sec
                      # intersection with && (bbox): 17 sec


*C. POLYGONS and POLYGONS*

   1.

      *shapes:** *ffh_gebiete (2670 POLYGONS) 12,7 MB, * *gk500_1 (16503
      POLYGONS) 22,8 MB

intersection

Result:

    *

      Openjump 1.2 D: 1:58 min

    *

      ArcMap 9.1: 38 sec

    *

      Kosmo 1.1: 4:02 min

   2.

      *postgis-layer: *ffh_gebiete (2670 POLYGONS, GiST-Index on
      geometry), gk500_1 (16503 POLYGONS, GiST-Index on geometry)

      Result:

                +

                  Openjump 1.2 D: 2:00 min

                +

                  Kosmo 1.1: 3:59 min

                +

                  PostGIS-SQL:

                      # intersection without && (bbox): 33:09 min
                      # intersection with && (bbox): 8:02 min