[postgis-users] Server not responding
andy at squeakycode.net
Fri Oct 7 07:07:23 PDT 2011
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 <andreasft at gmail.com
> <mailto:andreasft at gmail.com>>
> 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
> Could there be settings in postgresql.conf could cause this problem?
Yes. But you didnt offer computer info, nor settings, so I cannot help
> 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:
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.
More information about the postgis-users