It seems slower than I might expect. Is your table really wide? (100s of columns, or some really big objects?) If the rows are all toasted that would make updates and reads both a good deal slower.
P On Tue, May 5, 2009 at 8:40 PM, Ben Madin <[email protected]> wrote: > G'day all, > > I realise that there are probably many reasons, but I have a table with > about 4 million tuples reflecting start and end point id's over two years. > > I haven't been able to workable join these on the fly, so I have added three > columns, one for the geometry of the start point, one for the geometry of > the end point, and one for the (direct) line between them. > > I then updated the start and end point columns (took around 20 minutes for > each) by a UPDATE ... FROM ... JOIN syntax. > > then this morning reduced the fillfactor of the table to 50% to try to make > writes easier and I used st_makeline to create the third geometry (the > line). the query plan didn't look very complex (unfortunately I didn't > explain analyze): > > update nlis set line_geom = st_makeline(s_geom, d_geom) where s_geom is not > null and d_geom is not null; > > QUERY PLAN > ----------------------------------------------------------------- > Seq Scan on nlis (cost=0.00..194364.48 rows=3654306 width=246) > Filter: ((s_geom IS NOT NULL) AND (d_geom IS NOT NULL)) > (2 rows) > > UPDATE 3701850 > > duration: 1 743 563.733 ms > > Is 30 minutes reasonable for this sort of query? > > I have increased max_connection, shared_buffers, effective cache size etc > and kern.sysv.shmmax and kern.sysv.shmall and a few other tuning bits and > pieces based on information at > http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server. This is > running on a 2.4 GHz Core Duo (Mac OS X) with 2GB RAM and I notice that the > postgres process seems to only variably run between 10 and 45% of total CPU. > Disk writes seemed to come in packets every few seconds, with nothing much > in between. > > I could be worrying about nothing, but I see posts of people with millions > and millions of records, so I guess I'm just checking. > > cheers > > Ben > > -- > > Ben Madin > REMOTE INFORMATION > > t : +61 8 9192 5455 > f : +61 8 9192 5535 > m : 0448 887 220 > Broome WA 6725 > > [email protected] > > > > Out here, it pays to > know... > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
