[postgis-users] Buffer bug ?

[Daniel]

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


-------------- 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/20050825/2a42d540/attachment.vcf>