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
