On 10/7/2011 4:32 AM, Andreas Forø Tollefsen wrote:
We will now try the python script again and terminate and open the
connection for each year in the for loop.
If this works, then it seems like there is a resource issue in
Postgresql or in one of the functions of postgis i think.
As i said. This worked without a problem before.
2011/10/7 Andreas Forø Tollefsen <[email protected]
<mailto:[email protected]>>
Hi,
What i have done now is to try to run the query in PgAdmin SQL
windows instead of through the Python/Psycopg2 framework to see if
that was the problem.
This means one insert query for each year. I tried to run a limited
set with 10 annual insert queries and this worked. Then i tried the
rest of the years, and this never completed. Stopped the query. Then
trying with 3 years, it works. So there is a problem here with a
query that runs for a long time. It never finishes, and the cpu load
is still 100 %, but it never completes.
Did you wait forever? No? Then its just slow. It will eventually
finish if you let it. But you don't want to wait that long, I
understand. My point being, fixing "slow" is different than fixing "it
crashes".
> Could there be settings in postgresql.conf could cause this problem?
Yes. But you didnt offer computer info, nor settings, so I cannot help
you there.
As i wrote earlier, this query had no problem finishing on our
previous server.
What's different? Saying it used to work doesnt really help anyone.
How about you compare the two boxes, and their settings.
Reading this might help:
http://wiki.postgresql.org/wiki/Guide_to_reporting_problems
especially the "Server Configuration" link.
Here is my explain analyze for one year: http://explain.depesz.com/s/xRj
I notice the _st_intersects(b.geom, c.geom) takes a while. Do you have
complicated shapes (with lots of points)? Have you thought of
st_simplify? You might loose a little accuracy, but it might speed up
10 fold. Just depends.
I also see a Materialize line, which I believe means its swapping out to
disk. (Which points at work_mem being too low). If you have slow disk
IO then that'll be even worse.
The GroupAggregate is just killing you, but I'm not sure what that step
is actually doing.
-Andy
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
