Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 739F5133616B for ; Tue, 8 Mar 2011 15:13:43 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 20317-02 for ; Tue, 8 Mar 2011 19:13:33 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from marajade.localdomain (vcs5.camavision.com [63.228.164.252]) by mail.postgresql.org (Postfix) with ESMTP id 991A61337BDD for ; Tue, 8 Mar 2011 15:13:32 -0400 (AST) Received: from [192.168.10.84] (unknown [192.168.10.84]) (Authenticated sender: andy) by marajade.localdomain (Postfix) with ESMTPA id 0F4D34081C; Tue, 8 Mar 2011 13:13:32 -0600 (CST) Message-ID: <4D767FE2.9000707@squeakycode.net> Date: Tue, 08 Mar 2011 13:13:38 -0600 From: Andy Colson User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.15) Gecko/20110303 Lightning/1.0b2 Thunderbird/3.1.9 MIME-Version: 1.0 To: =?UTF-8?B?QW5kcmVhcyBGb3LDuCBUb2xsZWZzZW4=?= CC: Tom Lane , Oleg Bartunov , Kenneth Marshall , pgsql-performance@postgresql.org Subject: Re: Performance issues References: <20110307140101.GL8169@aart.is.rice.edu> <20110307142216.GM8169@aart.is.rice.edu> <18812.1299544721@sss.pgh.pa.us> <4D764981.2090000@squeakycode.net> In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201103/93 X-Sequence-Number: 42772 On 3/8/2011 10:58 AM, Andreas Forø Tollefsen wrote: > Andy. Thanks. That is a great tips. I tried it but i get the error: > NOTICE: ptarray_simplify returned a <2 pts array. > > Query: > SELECT ST_Intersection(priogrid_land.cell, > ST_Simplify(cshapeswdate.geom,0.1)) AS geom, > priogrid_land.gid AS divider, gwcode, gwsyear, gweyear, startdate, > enddate, capname, caplong, caplat, col, row, xcoord, ycoord > FROM priogrid_land, cshapeswdate WHERE ST_Intersects(priogrid_land.cell, > ST_Simplify(cshapeswdate.geom,0.1)) AND cshapeswdate.gwsyear <=1946 AND > cshapeswdate.gweyear >=1946 AND cshapeswdate.startdate <= '1946/1/1'; > > > 2011/3/8 Andy Colson > > > 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 > >> > > > =?ISO-8859-1?Q?Andreas_For=F8_Tollefsen?= > > >> 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 > > > > ew... thats not good. Seems like it simplified it down to a single point? (not 100% sure that's what the error means, just a guess) Try getting some info about it: select ST_Npoints(geom) As before, ST_NPoints(ST_Simplify(geom,0.1)) as after from cshapeswdate Also try things like ST_IsSimple ST_IsValid. I seem to recall sometimes needing ST_Points or st_NumPoints instead of ST_Npoints. -Andy