[postgis-users] Buffer bug ?

[Daniel]

Hi,

Thank u very much for your help. Here are precisions u requested:

1) vacuum, update_geometry_stats : a cron job do that daily (a daily 
process is enough).

2) I tested the same request on latests versions: same problem. (We have 
old versions on a server to avoid updating all our code to be compliant 
to postgis 1.0 evolutions, like new geom storage format, ...). With pg 8 
and postgis 1, it take more than 4000 seconds (I stopped before end). On 
windows, the problem occurs too, exactly in the same way.

3) geos version: 2.1.2  (postgis 0.9.1)

4) We realized different tests on differents pc, differents os, 
differents versions ... Finally, I suspect an issue with geos 2 on 
requests like "buffer(collect(the_geom), nnnn)". It's probably related 
to the buffer() function.

5) explain:
If we try "explain(SELECT nextval('buffer_id_seq'),9999, 'buf', 
buffer(collect(lga_geom), 1000) FROM
lg_annee WHERE lga_annee='2004' and lga_nat_cdn=2 and lga_var > 0 GROUP 
BY lga_annee);", we get:

QUERY PLAN
"HashAggregate  (cost=8.91..8.92 rows=1 width=267)"
"  ->  Index Scan using lga_var_ndx on lg_annee  (cost=0.00..8.69 
rows=44 width=267)"
"        Index Cond: (lga_var > 0::double precision)"
"        Filter: ((lga_annee = 2004::smallint) AND (lga_nat_cdn = 2))"

6) splitting of the request:

6.1) CREATE TABLE temp_lg_annee_bis AS SELECT * FROM lg_annee_bis WHERE
lga_annee=2004 and lga_nat_cdn=2 and lga_var > -1; ---> 345 milliseconds

6.2) buffer computing: the bug occur

Hope this could help.

Yours,

dF


Mark Cave-Ayland wrote:

>>-----Original Message-----
>>From: postgis-users-bounces at postgis.refractions.net 
>>[mailto:postgis-users-bounces at postgis.refractions.net] On 
>>Behalf Of Daniel
>>Sent: 25 August 2005 13:49
>>To: PostGIS Users Discussion
>>Subject: [postgis-users] Buffer bug ?
>>
>>
>>Hello,
>>
>>We developped one and half year ago a Postgis application 
>>with buffer() 
>>computation. The first version was coded for postgre 7.3.9 and 
>>postgis-cvs, and, after, with postgis 0.8 and 0.8.1
>>
>>We upgraded a server to postgresql 7.4 and postgis 0.9.1 and 
>>it doesn't 
>>work anymore:
>>
>>The request was:
>>
>>SELECT nextval('buffer_id_seq'),9999, 'buf', 
>>buffer(collect(lga_geom), 
>>1000) FROM
>>lg_annee_bis WHERE lga_annee=2004 and lga_nat_cdn=2 and lga_var > -1 
>>GROUP BY lga_annee;
>>
>>I've got 7500 records which verify the where condition. The 
>>whole table 
>>contain 15000 records. Our past attempts got an execution 
>>time smaller 
>>than 25-30 seconds.
>>
>>Now, after 360 seconds, the request is not finished. 
>>Furthermore, if the 
>>thread is stopped (for example in spite of a web server timeout), a 
>>remaining PostgreSQL thread continue to eat CPU time. Without a quick 
>>"kill -9", the server hang by 100% CPU load after few minutes.
>>
>>Here is an "explain analyze" on a reduced dataset:
>>
>>QUERY PLAN
>>"HashAggregate  (cost=1901.96..1901.97 rows=1 width=264) (actual 
>>time=5316.071..5316.076 rows=1 loops=1)"
>>"  ->  Seq Scan on lg_annee_bis  (cost=0.00..1816.64 rows=17065 
>>width=264) (actual time=35.224..36.885 rows=200 loops=1)"
>>"        Filter: ((lga_annee = 2004) AND (lga_nat_cdn = 2) 
>>AND (lga_var 
>> > -1::double precision))"
>>"Total runtime: 5316.956 ms"
>>
>>Does anybody know something about this (supposed) issue  ?
>>
>>We are really very annoyed by this problem. Thank you very 
>>much for any 
>>help.
>>
>>dF
>>    
>>
>
>
>Hi Daniel,
>
>The first things to check are if you have run VACCUM ANALYZE and SELECT
>update_geometry_stats() on your new database, and whether you have altered
>the postgresql.conf shared_buffers, random_page_cost, and
>effective_cache_size parameters for your new postmaster (changing these
>options will require you to restart PostgreSQL). If that doesn't solve the
>problem then we need some more detail.
>
>To begin with, what version of GEOS are you using? Also, the EXPLAIN (rather
>than EXPLAIN ANALYZE) of the *full* table would be useful as well as the
>schema of the lg_annee_bis table (given by \d in psql). This is because the
>PostgreSQL query planner is quite sensitive to changes in the number of rows
>when the number of rows in the table is small.
>
>It would also be useful to see how much time is being spent performing just
>the buffer() operation by trying it on the data from your WHERE condition,
>e.g.
>	
>	BEGIN;
>	CREATE TABLE temp_lg_annee_bis AS SELECT * FROM lg_annee_bis WHERE
>lga_annee=2004 and lga_nat_cdn=2 and lga_var > -1;
>	SELECT buffer(collect(lga_geom), 1000) FROM temp_lg_annee_bis GROUP
>BY lga_annee;
>	ABORT;
>
>BTW, is there any reason why during your upgrade you didn't go straight to
>PostgreSQL 8.0? Having the latest and greatest database always helps.
>
>
>Kind regards,
>
>Mark.
>
>------------------------
>WebBased Ltd
>17 Research Way
>Tamar Science Park
>Plymouth
>PL6 8BT 
>
>T: +44 (0)1752 797131
>F: +44 (0)1752 791023
>W: http://www.webbased.co.uk
> 
>
>
>  
>

-------------- next part --------------
A non-text attachment was scrubbed...
Name: daniel.faivre.vcf
Type: text/x-vcard
Size: 323 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20050826/51819ce9/attachment.vcf>