From: Andy Colson <andy@squeakycode.net>
To: Andreas Forø Tollefsen <andreasft@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Oleg Bartunov <oleg@sai.msu.su>
Cc: Kenneth Marshall <ktm@rice.edu>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance issues
Date: Tue, 08 Mar 2011 13:13:38 -0600
Message-ID: <4D767FE2.9000707@squeakycode.net> (raw)
In-Reply-To: <AANLkTikhpt0RNx_O-N8W_Li2A7AR5tnc4P9wp5H1TXky@mail.gmail.com>
References: <AANLkTinZkVapdjrZULpc8zmwb4w8wotkYzj0gqj_jPXb@mail.gmail.com>
<20110307140101.GL8169@aart.is.rice.edu>
<AANLkTim71gwPa3y0Sggdzqh+-m=DY7-NWNe-BE7XwwPb@mail.gmail.com>
<20110307142216.GM8169@aart.is.rice.edu>
<AANLkTim4RoKaQcBWhjer34a0EjSX-PWQkoXGYV0YY=kM@mail.gmail.com>
<Pine.LNX.4.64.1103071834040.14517@sn.sai.msu.ru>
<AANLkTik1Nkq-pRb7OEFDRzDPA8147rTrkh8as=ALRtm2@mail.gmail.com>
<18812.1299544721@sss.pgh.pa.us>
<AANLkTimmj7K2kgkgY=YiU5+Ej1NAQYqUUgRN9HDNfZNs@mail.gmail.com>
<4D764981.2090000@squeakycode.net>
<AANLkTikhpt0RNx_O-N8W_Li2A7AR5tnc4P9wp5H1TXky@mail.gmail.com>
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 <andy@squeakycode.net <mailto:andy@squeakycode.net>>
>
> 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@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>
> <mailto:tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>>>
>
>
> =?ISO-8859-1?Q?Andreas_For=F8_Tollefsen?=
> <andreasft@gmail.com <mailto:andreasft@gmail.com>
> <mailto:andreasft@gmail.com <mailto:andreasft@gmail.com>>> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andy@squeakycode.net, andreasft@gmail.com, tgl@sss.pgh.pa.us, oleg@sai.msu.su, ktm@rice.edu
Subject: Re: Performance issues
In-Reply-To: <4D767FE2.9000707@squeakycode.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox