Re: [postgis-users] Polygon validity
first of all: sorry for my poor english ..:( >>* So a curve with 2 consecutive point like this *>>* linestring (10 10, 20 20, 20 20, 30 30) is violating this definition *>I disagree. In the formal definition of Curve above, note the condition >that x1 < x2. In any continuous parameterization f of LINESTRING(10 10, >20 20, 20 20, 30 30), if f(x1) = pt[1] (20 20) and f(x2 = pt[2] (20 20), >then x1 must = x2. Oh no. x1 and x2 report simply a sequence (or a serial of point), for example 1 , 2 , 3 is a sequence of points. but even 1 , 1, 1 is a sequence. (where x1 = 1, x2 = 1 and x3 = 1) So x1 is always differente from x2. x1 < x2 is needed for saying that it is a sequence, or that matemathically there is a sequence of point where the first point come in before of the second point, and the second point com after the first point. the 1 and 2 prefix is a positional index in the sequence. so: is cannot be true that if f(x1) = f(x2) then x1 = x2. Andrea. -- - Andrea Peri . . . . . . . . . qwerty àèìòù - ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_Buffer questions
Thanks Ben, I will try what you suggested. We only need to represent mobile cell tower, so geography might be too much for the application. Especially consider there are much more functions for geometry than geography. Happy Easter:) -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Ben Madin Sent: Thursday, 1 April 2010 4:42 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] ST_Buffer questions G'day Li, I can't help with Q2, but On 01/04/2010, at 12:28 , Chen, Li [Contractor] wrote: > Q1. > ST_Buffer(g1, range) is able to return a geometry within the range of g1. > > So, I define two point using lon/lat (SRID=4326) and range 10km. I want to > see whether they cross each other by using ST_Crosses(g1, g2). > However, I don't know the unit of the range parameter in ST_Buffer(g1,range) > as it is not provide in the docs. So is it km or meters? The same unit as your Geometry - decimal degrees. Obviously due to the change in the value of this unit at differing latitudes, this is not useful, so a more sensible approach is either to transform your point into a projection using metres, and then use metres (off the top of my head it would look like : select st_buffer(st_transform(g1, appropriate_projection_epsg),1); but you should check the docs) or use the geography type from postgis 1.5? but I haven't tried it yet? cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_Buffer questions
G'day Li, I can't help with Q2, but On 01/04/2010, at 12:28 , Chen, Li [Contractor] wrote: > Q1. > ST_Buffer(g1, range) is able to return a geometry within the range of g1. > > So, I define two point using lon/lat (SRID=4326) and range 10km. I want to > see whether they cross each other by using ST_Crosses(g1, g2). > However, I don’t know the unit of the range parameter in ST_Buffer(g1,range) > as it is not provide in the docs. So is it km or meters? The same unit as your Geometry - decimal degrees. Obviously due to the change in the value of this unit at differing latitudes, this is not useful, so a more sensible approach is either to transform your point into a projection using metres, and then use metres (off the top of my head it would look like : select st_buffer(st_transform(g1, appropriate_projection_epsg),1); but you should check the docs) or use the geography type from postgis 1.5? but I haven't tried it yet? cheers Ben ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] ST_Buffer questions
Hi Everyone, I am a newbie to this community and I would like to ask a question/s :). Q1. ST_Buffer(g1, range) is able to return a geometry within the range of g1. So, I define two point using lon/lat (SRID=4326) and range 10km. I want to see whether they cross each other by using ST_Crosses(g1, g2). However, I don't know the unit of the range parameter in ST_Buffer(g1,range) as it is not provide in the docs. So is it km or meters? Q2. I want to define many circle sectors around the earth. And I have a column to set the location of the centre and another column to set the shape of the sector (direction, diameter). Is there a function to return a geometry that represents both the location and shape of the sector? ST_Buffer only returns a full circle. Thanks for the help, Li ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Nested loop join = very bad performance
Hi, Thanks again for the suggestions. The default values for join_collapse_limit and from_collapse_limit were set to 8 - I upped these both to 128, with no observable difference. I guess this issue is better suited for the general PostgreSQL mailing lists... Regards, Mike On Wednesday 31 March 2010 15:51:14 Paragon Corporation wrote: > Mike, > > Couple of thoughts. Given you have so many joins, could be you are > reaching the join collapse limit and the planner is kicking out before > making an optimal plan. > > Try increasing the join_collapse_limit and from_collapse_limit > > As was detailed in this thread > > http://archives.postgresql.org/pgsql-performance/2009-04/msg00258.php > > > > Alternatively could be your actual and estimated costs are out of wack and > might help upping your default targets and reanalyzing data. You can > probably get a sense of this by doing a an explain analyze of your query > and comparing the actual cost/row count with the estimated cost/row count > where its doing a nested loop. > > Admittedly this hasn't helped much for us. > > http://archives.postgresql.org/pgsql-performance/2009-02/msg00336.php > > Leo and Regina, > > http:///www.postgis.us > > > > > -Original Message- > From: postgis-users-boun...@postgis.refractions.net > [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Mike > Leahy > Sent: Wednesday, March 31, 2010 1:17 PM > To: Mark Cave-Ayland > Cc: PostGIS Users Discussion > Subject: Re: [postgis-users] Nested loop join = very bad performance > > Hi Mark, > > I set effective_cache_size to 3072mb, and shared_buffers to 1024mb (as my > system has a total of 4gb). This only slightly (if at all) improves the > performance, maybe reducing the query by somewhere around 500 ms (down to > ~14700 ms). All other parameters in the postgresql.conf are defaults. I > don't recall exactly what I changed before (I was just tried increasing > memory limits and other things pretty much without knowing what I was > doing), but none of that really seems to have a significant impact on the > performance. > > The challenge with trying to reduce this query is that the nested loop join > only happens with the query as a whole (in general). The briefest example > I could put together was presented in the thread last week (see the > attachment here: > http://postgis.org/pipermail/postgis-users/2010-March/026239.html). If I > pull any more parameters or parts out of the query, the nest loop (and the > resulting errors/crashes I was encountering at the time) would not happen. > > Regards, > Mike > > On Wednesday 31 March 2010 04:40:09 Mark Cave-Ayland wrote: > > Mike Leahy wrote: > > > Mark/List, > > > > > > I just replaced my postgresql.conf with the default copy that > > > appears in /etc/postgresql/8.4/main/ after a fresh install. The > > > performance is pretty much the same as before (maybe even about 400 > > > ms worse than before). > > > > > > Is there anything else I should try? > > > > > > Mike > > > > Hi Mike, > > > > Which parameters did you change? effective_cache_size and > > shared_buffers should be tweaked to suit the RAM available in your > > machine but the rest of the defaults are fairly sensible. > > > > You probably want to set effective_cache_size to ~75% of your physical > > RAM and shared_buffers to ~25%. Does that make any difference at all? > > > > Otherwise, you'll need to start breaking down your query into parts to > > see which bit is causing the slowdown. Start with the innermost query > > and then add one join at a time until you find the part which is > > causing the slowdown. > > > > > > ATB, > > > > Mark. > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Nested loop join = very bad performance
Mike, Couple of thoughts. Given you have so many joins, could be you are reaching the join collapse limit and the planner is kicking out before making an optimal plan. Try increasing the join_collapse_limit and from_collapse_limit As was detailed in this thread http://archives.postgresql.org/pgsql-performance/2009-04/msg00258.php Alternatively could be your actual and estimated costs are out of wack and might help upping your default targets and reanalyzing data. You can probably get a sense of this by doing a an explain analyze of your query and comparing the actual cost/row count with the estimated cost/row count where its doing a nested loop. Admittedly this hasn't helped much for us. http://archives.postgresql.org/pgsql-performance/2009-02/msg00336.php Leo and Regina, http:///www.postgis.us -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Mike Leahy Sent: Wednesday, March 31, 2010 1:17 PM To: Mark Cave-Ayland Cc: PostGIS Users Discussion Subject: Re: [postgis-users] Nested loop join = very bad performance Hi Mark, I set effective_cache_size to 3072mb, and shared_buffers to 1024mb (as my system has a total of 4gb). This only slightly (if at all) improves the performance, maybe reducing the query by somewhere around 500 ms (down to ~14700 ms). All other parameters in the postgresql.conf are defaults. I don't recall exactly what I changed before (I was just tried increasing memory limits and other things pretty much without knowing what I was doing), but none of that really seems to have a significant impact on the performance. The challenge with trying to reduce this query is that the nested loop join only happens with the query as a whole (in general). The briefest example I could put together was presented in the thread last week (see the attachment here: http://postgis.org/pipermail/postgis-users/2010-March/026239.html). If I pull any more parameters or parts out of the query, the nest loop (and the resulting errors/crashes I was encountering at the time) would not happen. Regards, Mike On Wednesday 31 March 2010 04:40:09 Mark Cave-Ayland wrote: > Mike Leahy wrote: > > Mark/List, > > > > I just replaced my postgresql.conf with the default copy that > > appears in /etc/postgresql/8.4/main/ after a fresh install. The > > performance is pretty much the same as before (maybe even about 400 > > ms worse than before). > > > > Is there anything else I should try? > > > > Mike > > Hi Mike, > > Which parameters did you change? effective_cache_size and > shared_buffers should be tweaked to suit the RAM available in your > machine but the rest of the defaults are fairly sensible. > > You probably want to set effective_cache_size to ~75% of your physical > RAM and shared_buffers to ~25%. Does that make any difference at all? > > Otherwise, you'll need to start breaking down your query into parts to > see which bit is causing the slowdown. Start with the innermost query > and then add one join at a time until you find the part which is > causing the slowdown. > > > ATB, > > Mark. > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Polygon validity
Andrea Peri 2007 wrote: I think it would be invalid only in the domain of simple-features. Not in general. So I think is right think that for a geometric linestring selfintersect, or having some consecutive or not consecutive (but always internal) point repeated is invalid for a simple-feature world, but it can be valid for the more huge world of "not simple-feature" geometries. Another thing to consider - if you change the semantics of isSimple to report false for linestrings containing duplicate points, then you have no way of telling the difference between linestrings which contain true, topological self-intersections and ones which just happen to contain a topologically irrelevant duplicate point. Why it is irrilevant ? See my previous post. "This is why I say that repeated points are topologically irrelevant - they are topologically indistinguishable under any continuous parameterization function." I think there's some confusion here between the textual and in-memory representation of a LineString, and its topological, point-set meaning. In the textual representation repeated points can occur, but in the topological representation repeated points have no meaning. -- Martin Davis Senior Technical Architect Refractions Research, Inc. (250) 383-3022 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Polygon validity
Andrea Peri 2007 wrote: In the 6.1.5 paragraph of 06-103r3 is reported: For MultiPoints: .. A MultiPoint is simple if no two Points in the MultiPoint are equal (have identical coordinate values in X and Y). Every MultiPoint is spatially equal under the definition in Clause 6.1.15.3 to a simple Multipoint. .. Yes, this is clear. MultiPoints with duplicate Point values are non-simple. For curves: .. A Curve is simple if it does not pass through the same Point twice with the possible exception of the two end points (Reference [1], section 3.12.7.3): ∀ c ∈ Curve, [a, b] = c.Domain, c =: f :[a, b] → ℜ n c.IsSimple ⇔ ∀ x1, x2 ∈ [a, b]: [ f(x1)=f(x2) ∧ x1 So a curve with 2 consecutive point like this linestring (10 10, 20 20, 20 20, 30 30) is violating this definition I disagree. In the formal definition of Curve above, note the condition that x1 < x2. In any continuous parameterization f of LINESTRING(10 10, 20 20, 20 20, 30 30), if f(x1) = pt[1] (20 20) and f(x2 = pt[2] (20 20), then x1 must = x2. This is why I say that repeated points are topologically irrelevant - they are topologically indistinguishable under any continuous parameterization function. But even this line is invalid for this definition: linestring (10 10, 20 20, 30 30, 40 40, 50 50, 30 30, 60 60) (where the repeated points are not consecutive). because the definition reporting in document: ∀ x1, x2 ∈ [a, b]: [ f(x1)=f(x2) ∧ x1Yes, there's no issue here. Under the continuous parameterization definition non-consecutive identical points clearly make the linestring non-simple. -- Martin Davis Senior Technical Architect Refractions Research, Inc. (250) 383-3022 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Polygon validity
I think it would be invalid only in the domain of simple-features. Not in general. So I think is right think that for a geometric linestring selfintersect, or having some consecutive or not consecutive (but always internal) point repeated is invalid for a simple-feature world, but it can be valid for the more huge world of "not simple-feature" geometries. Another thing to consider - if you change the semantics of isSimple to report false for linestrings containing duplicate points, then you have no way of telling the difference between linestrings which contain true, topological self-intersections and ones which just happen to contain a topologically irrelevant duplicate point. Why it is irrilevant ? In a topological world 2 polygon in touch between each other can have a line in common on their boundaries of-course. Identically two lines can have a point in common beetween they (on their boundary of course), phisically that point can be is the start point or the end point of the line, but if the start point or the end point are 2 or more point because there are repeated points I think is a problem even topologically. Andrea. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Polygon validity
In the 6.1.5 paragraph of 06-103r3 is reported: For MultiPoints: .. A MultiPoint is simple if no two Points in the MultiPoint are equal (have identical coordinate values in X and Y). Every MultiPoint is spatially equal under the definition in Clause 6.1.15.3 to a simple Multipoint. .. For curves: .. A Curve is simple if it does not pass through the same Point twice with the possible exception of the two end points (Reference [1], section 3.12.7.3): ∀ c ∈ Curve, [a, b] = c.Domain, c =: f :[a, b] → ℜ n c.IsSimple ⇔ ∀ x1, x2 ∈ [a, b]: [ f(x1)=f(x2) ∧ x1 So a curve with 2 consecutive point like this linestring (10 10, 20 20, 20 20, 30 30) is violating this definition But even this line is invalid for this definition: linestring (10 10, 20 20, 30 30, 40 40, 50 50, 30 30, 60 60) (where the repeated points are not consecutive). because the definition reporting in document: ∀ x1, x2 ∈ [a, b]: [ f(x1)=f(x2) ∧ x1 Kevin Neufeld wrote: This is often something that is easily misunderstood. So, to be clear, according to the OGC specs, simplicity really *only* applies to point and linear features ([multi]point, [multi]linestrings). Validity on the other hand *only* applies to areal features ([multi]polygons). I put together some thoughts [1] a while back on this. The OGC specs aren't totally clear regarding duplicate points in linestrings, just that "has no anomalous geometric points, such as self intersection or self tangency" and "is simple if it does not pass through the same point twice". The developers at the time decided that this does not include duplicate points. I suppose one could argue either way, but to me this really does include duplicate points. PostGIS also somewhat subscribes to the SQL/MM specifications. In there I read "is not simple if any interior point has the same location as another interior point or a point on the boundary". To me, this is more clear and, Rykov, I think you're right, ST_IsSimple really should not allow duplicate points. I'll put in a ticket. So, simplicity is for lines and points. A *polygon* is *valid* iff all the underlying linear features are *simple* (like no figure 8's in the boundary) and also follows several other rules (like the inner rings being contained within the exterior ring, etc). ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Nested loop join = very bad performance
Hi Mark, I set effective_cache_size to 3072mb, and shared_buffers to 1024mb (as my system has a total of 4gb). This only slightly (if at all) improves the performance, maybe reducing the query by somewhere around 500 ms (down to ~14700 ms). All other parameters in the postgresql.conf are defaults. I don't recall exactly what I changed before (I was just tried increasing memory limits and other things pretty much without knowing what I was doing), but none of that really seems to have a significant impact on the performance. The challenge with trying to reduce this query is that the nested loop join only happens with the query as a whole (in general). The briefest example I could put together was presented in the thread last week (see the attachment here: http://postgis.org/pipermail/postgis-users/2010-March/026239.html). If I pull any more parameters or parts out of the query, the nest loop (and the resulting errors/crashes I was encountering at the time) would not happen. Regards, Mike On Wednesday 31 March 2010 04:40:09 Mark Cave-Ayland wrote: > Mike Leahy wrote: > > Mark/List, > > > > I just replaced my postgresql.conf with the default copy that appears in > > /etc/postgresql/8.4/main/ after a fresh install. The performance is > > pretty much the same as before (maybe even about 400 ms worse than > > before). > > > > Is there anything else I should try? > > > > Mike > > Hi Mike, > > Which parameters did you change? effective_cache_size and shared_buffers > should be tweaked to suit the RAM available in your machine but the rest > of the defaults are fairly sensible. > > You probably want to set effective_cache_size to ~75% of your physical > RAM and shared_buffers to ~25%. Does that make any difference at all? > > Otherwise, you'll need to start breaking down your query into parts to > see which bit is causing the slowdown. Start with the innermost query > and then add one join at a time until you find the part which is causing > the slowdown. > > > ATB, > > Mark. > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Polygon validity
So you're saying that any geometry which contains consecutive duplicate points is invalid? This is a pretty major change to validity semantics. (As a side note, I wouldn't say that validity doesn't apply to linear features - just that there are few constraints on what constitutes a valid linear feature.) Another thing to consider - if you change the semantics of isSimple to report false for linestrings containing duplicate points, then you have no way of telling the difference between linestrings which contain true, topological self-intersections and ones which just happen to contain a topologically irrelevant duplicate point. So there should probably be a function which detects *just* duplicate points, to allow users to differentiate these two cases. And in that case, why not just leave the semantics of isSimple the way it is, and users can use either or both functions as needed? Kevin Neufeld wrote: On 3/30/2010 11:46 PM, Andrea Peri wrote: You can use the ST_IsSimple to know if a geometry is simple-feature. If true it is simple-feature. After, if you geometry is ST_IsSimpe = true, you can test with ST_IsValid , to detect eventually invalidity for geometry. So you can detect if it is a simple-feature valid geometry. This is often something that is easily misunderstood. So, to be clear, according to the OGC specs, simplicity really *only* applies to point and linear features ([multi]point, [multi]linestrings). Validity on the other hand *only* applies to areal features ([multi]polygons). I put together some thoughts [1] a while back on this. The OGC specs aren't totally clear regarding duplicate points in linestrings, just that "has no anomalous geometric points, such as self intersection or self tangency" and "is simple if it does not pass through the same point twice". The developers at the time decided that this does not include duplicate points. I suppose one could argue either way, but to me this really does include duplicate points. PostGIS also somewhat subscribes to the SQL/MM specifications. In there I read "is not simple if any interior point has the same location as another interior point or a point on the boundary". To me, this is more clear and, Rykov, I think you're right, ST_IsSimple really should not allow duplicate points. I'll put in a ticket. So, simplicity is for lines and points. A *polygon* is *valid* iff all the underlying linear features are *simple* (like no figure 8's in the boundary) and also follows several other rules (like the inner rings being contained within the exterior ring, etc). Rykov, this is why your polygon passes validity - currently PostGIS does not include duplicate points in linestrings (the boundary of your polygon) as non-simple. Cheers, Kevin [1] http://postgis.refractions.net/docs/ch04.html#OGC_Validity ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Martin Davis Senior Technical Architect Refractions Research, Inc. (250) 383-3022 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Polygon validity
On 3/30/2010 11:46 PM, Andrea Peri wrote: You can use the ST_IsSimple to know if a geometry is simple-feature. If true it is simple-feature. After, if you geometry is ST_IsSimpe = true, you can test with ST_IsValid , to detect eventually invalidity for geometry. So you can detect if it is a simple-feature valid geometry. This is often something that is easily misunderstood. So, to be clear, according to the OGC specs, simplicity really *only* applies to point and linear features ([multi]point, [multi]linestrings). Validity on the other hand *only* applies to areal features ([multi]polygons). I put together some thoughts [1] a while back on this. The OGC specs aren't totally clear regarding duplicate points in linestrings, just that "has no anomalous geometric points, such as self intersection or self tangency" and "is simple if it does not pass through the same point twice". The developers at the time decided that this does not include duplicate points. I suppose one could argue either way, but to me this really does include duplicate points. PostGIS also somewhat subscribes to the SQL/MM specifications. In there I read "is not simple if any interior point has the same location as another interior point or a point on the boundary". To me, this is more clear and, Rykov, I think you're right, ST_IsSimple really should not allow duplicate points. I'll put in a ticket. So, simplicity is for lines and points. A *polygon* is *valid* iff all the underlying linear features are *simple* (like no figure 8's in the boundary) and also follows several other rules (like the inner rings being contained within the exterior ring, etc). Rykov, this is why your polygon passes validity - currently PostGIS does not include duplicate points in linestrings (the boundary of your polygon) as non-simple. Cheers, Kevin [1] http://postgis.refractions.net/docs/ch04.html#OGC_Validity ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Polygon validity
This polygon is valid according to the OGC SFS, and thus validates correctly in PostGIS (also GEOS/JTS). You'd have to look at the validation semantics of the other systems to determine why they don't validate it. The OGC SFS model is fairly tolerant in terms of trivial structural aspects - for instance, it allows duplicate points. I believe this is done in order to allow as a wide a range of geometries as possible to be represented, consistent with topological integrity. Some other systems are less tolerant. So I think strk is probably right - perhaps they don't allow duplicate points, or perhaps they are reducing the precision and thus causing a self-intersection (although I tried this in JTS, and reducing precision doesn't actually cause self-intersections) Martin Rykov Denis wrote: There is following polygon: POLYGON((317371.62563763 5731575.49932608, 317028.110300846 5731043.43103426, 317028.110300846 5731043.43103426, 316389.822682815 5731138.79331604, 316478.209591618 5731705.00321977, 316478.209591619 5731705.00321977, 317350.223258488 5731584.46661525, 317371.62563763 5731575.49932608)) with two duplicate nodes. When I try to check it with ST_IsValidReason function I get message that it is Valid Geometry. Is this OGC compliant? When I try to check it with QGIS, ArcView or ArcGIS - I get notice about self intersection. Please explain this situation. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Martin Davis Senior Technical Architect Refractions Research, Inc. (250) 383-3022 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Nested loop join = very bad performance
Mike Leahy wrote: Mark/List, I just replaced my postgresql.conf with the default copy that appears in /etc/postgresql/8.4/main/ after a fresh install. The performance is pretty much the same as before (maybe even about 400 ms worse than before). Is there anything else I should try? Mike Hi Mike, Which parameters did you change? effective_cache_size and shared_buffers should be tweaked to suit the RAM available in your machine but the rest of the defaults are fairly sensible. You probably want to set effective_cache_size to ~75% of your physical RAM and shared_buffers to ~25%. Does that make any difference at all? Otherwise, you'll need to start breaking down your query into parts to see which bit is causing the slowdown. Start with the innermost query and then add one join at a time until you find the part which is causing the slowdown. ATB, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
