Re: Performance issues

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I have seen really complex geometries cause problems. If you have thousands of points, when 10 would do, try ST_Simplify and see if it doesnt speed things up.

-Andy


On 3/8/2011 2:42 AM, Andreas Forø Tollefsen wrote:
Hi. Thanks for the comments. My data is right, and the result is exactly
what i want, but as you say i think what causes the query to be slow is
the ST_Intersection which creates the intersection between the vector
grid (fishnet) and the country polygons.
I will check with the postgis user list if they have any idea on how to
speed up this query.

Best,
Andreas

2011/3/8 Tom Lane <tgl@xxxxxxxxxxxxx <mailto:tgl@xxxxxxxxxxxxx>>

    =?ISO-8859-1?Q?Andreas_For=F8_Tollefsen?= <andreasft@xxxxxxxxx
    <mailto:andreasft@xxxxxxxxx>> writes:
     > This is a query i am working on now. It creates an intersection
    of two
     > geometries. One is a grid of 0.5 x 0.5 decimal degree sized
    cells, while the
     > other is the country geometries of all countries in the world for
    a certain
     > year.

    Hm, are you sure your data is right?  Because the actual rowcounts imply
    that each country intersects about half of the grid cells, which doesn't
    seem right.

     > priogrid=# EXPLAIN ANALYZE SELECT priogrid_land.gid, gwcode,
     > ST_Intersection(pri
     > ogrid_land.cell, cshapeswdate.geom) FROM priogrid_land,
    cshapeswdate WHERE
     > ST_Intersects(priogrid_land.cell, cshapeswdate.geom);
     >
       QUERY
     > PLAN

     >
    --------------------------------------------------------------------------------
     > ------------------------------------------------------------------
     >  Nested Loop  (cost=0.00..12644.85 rows=43351 width=87704) (actual
     > time=1.815..7
     > 074973.711 rows=130331 loops=1)
     >    Join Filter: _st_intersects(priogrid_land.cell, cshapeswdate.geom)
     >    ->  Seq Scan on cshapeswdate  (cost=0.00..14.42 rows=242
    width=87248)
     > (actual
     >  time=0.007..0.570 rows=242 loops=1)
     >    ->  Index Scan using idx_priogrid_land_cell on priogrid_land
     >  (cost=0.00..7.1
     > 5 rows=1 width=456) (actual time=0.069..5.604 rows=978 loops=242)
     >          Index Cond: (priogrid_land.cell && cshapeswdate.geom)
     >  Total runtime: 7075188.549 ms
     > (6 rows)

    AFAICT, all of the runtime is going into calculating the ST_Intersects
    and/or ST_Intersection functions.  The two scans are only accounting for
    perhaps 5.5 seconds, and the join infrastructure isn't going to be
    terribly expensive, so it's got to be those functions.  Not knowing much
    about PostGIS, I don't know if the functions themselves can be expected
    to be really slow.  If it's not them, it could be the cost of fetching
    their arguments --- in particular, I bet the country outlines are very
    large objects and are toasted out-of-line.  There's been some past
    discussion of automatically avoiding repeated detoastings in scenarios
    like the above, but nothing's gotten to the point of acceptance yet.
    Possibly you could do something to force detoasting in a subquery.

                            regards, tom lane




--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux