[postgis-users] Odd problem with pgsql2shp

[Ethan Alpert]

I'm running postgres 7.4.6 with postgis 0.9.0.

I have a table that suddenly about a month ago started taking
significantly more time to dump to a shapefile all of the sudden.

I run vacuum analyze nightly on my database. This particular table has
had a lot of data inserted into it when the problem started occurring
but the number or records is only 16,156. The polygons are all 5 vertice
rectangles. Suddenly it's taking nearly 20 minutes to dump to a shape.

Meanwhile a table with over 500,000 records takes less than 4 minutes. I
am at a complete loss to understand what about this particular table is
causing the problem.

I'd like to upgrade but this is a production system and I really don't
want to take it down. I may have to if I can't come up with an
explanation and fix for this problem.

I can find no differences in how the two tables I'm dumping are defined
with respect to indexes.

Has anyone ever run into anything like this? Any idea what I can
possibly try to do to fix this?

-bash-2.05b$ /usr/bin/time pgsql2shp -u spatial spatialdb dems
Initializing... Done.
Dumping: X..X [16156 rows].
Command exited with non-zero status 1
0.27user 0.35system 17:50.62elapsed 0%CPU (0avgtext+0avgdata
0maxresident)k
0inputs+0outputs (168major+490minor)pagefaults 0swaps
-bash-2.05b$ /usr/bin/time pgsql2shp -u spatial spatialdb snaps
Initializing... Done.
Dumping: X..X [568961 rows].
Command exited with non-zero status 1
12.62user 15.47system 3:42.43elapsed 12%CPU (0avgtext+0avgdata
0maxresident)k
0inputs+0outputs (168major+15637minor)pagefaults 0swaps