[postgis-users] Buffer bug ?

[Mark Cave-Ayland]

> -----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