[postgis-users] st_difference creates a huge toast tables

[Rémi Cura]

I don't know what's in you table , but your result may be very big

numberofresult = numberof(province ) * numberof(potentialite_tmp )



You can manually test this by using WITH statement with limits :

WITH province AS (
SELECT *
FROM province
LIMIT 2),
potentialite_tmp  AS (
SELECT *
FROM potentialite_tmp
LIMIT 2)
SELECT  id, gridcode, ST_Difference(a.geom32198_s,b.geom32198_s) as
geom32198_s
FROM province as a, potentialite_tmp as b;

And based on your "into", I guess this is executed inside a plpgsql
function.
You should first try it directly in pgadmin, because in a plpgsql function
the result will be cached and it could be the bottlneck (I'm not an expert)

Cheers,

Rémi-C


2013/11/28 <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
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20131128/7be17262/attachment.html>