[postgis-users] Performance: PostGIS vs Shape

[Paul Ramsey]

You're not testing the speed of the data sources, you're testing the  
speed of the geometry processing (that Intersection() and Intersects 
() ).

There should not be significant differences between the JUMP/Kosmo  
geometry processing and PostGIS, since they are the same algorithmic  
base.  Probably the different lies in your combination of Intersects 
() and Intersection(), that guarantees a full topological calculation  
TWICE for each candidate row.  The algorithm in the desktop systems  
is probably smarter.

    SELECT intersection(a.geometry, b.geometry) AS intersection_geom,
    ...
    FROM lsg b, bze_wze a
    WHERE a.geometry && b.geometry;

Try removing the Intersects() and just letting the && winnow things  
down.  I would hope for comparable results, but there is no reason  
the database should be any faster, given what you are testing.

If you want to see faster, see who can write back a change to a  
single record to disk fastest.

P.

On 8-Nov-07, at 5:16 AM, Johannes Sommer wrote:

> 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
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users