[postgis-users] st_difference creates a huge toast tables

[Steve.Toutant at inspq.qc.ca]

Hi,
This query didn't endup after 4 hours....and eat 40gig of disk space
SELECT  id, gridcode, ST_Difference(a.geom32198_s,b.geom32198_s) as 
geom32198_s
into potentialite 
FROM province as a, potentialite_tmp as b;

I stopped the execution and cancel request sent appears, but after an hour 
the request wasn't stopped yet.
So I killed the process and restat postgres.

The database is up and running but I didn't get the 40gig of disk space 
back. And only 5gig remains on the server

It is this table that is so huge
pg_toast.pg_toast_11037520

I tried with vaccuumdb without success. Vacuumdb full didn't work because 
only 5 gig left on the server
1-
What can I do to get back the disk space? Can I simply drop 
pg_toast.pg_toast_11037520?

2-
Both tables have gist index, any idea why it takes so long to execute and 
why this query is so "heavy" for postgis?

thanks in advance for your help
Steve

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20131128/20ed3f7b/attachment.html>