[postgis-users] Server not responding

[Andy Colson]

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>>
>
>     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