query creates a huge toast tables

From: Steve(dot)Toutant(at)inspq(dot)qc(dot)ca
To: pgsql-general(at)postgresql(dot)org
Subject: query creates a huge toast tables
Date: 2013-11-28 16:25:41
Message-ID: OFE36F61A0.99302B75-ON85257C31.0059DE5B-85257C31.005A3E08@isn.rtss.qc.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,
This query (postgis) 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 vaccuumdb without success. Vacuumdb full didn't work because only
5 gig left on the server

What can I do to get the disk space back ?
Can I simply drop pg_toast.pg_toast_11037520?

I'm using
"PostgreSQL 9.1.3 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux)
4.3.4 [gcc-4_3-branch revision 152973], 64-bit"
"POSTGIS="1.5.3" GEOS="3.3.2-CAPI-1.7.2" PROJ="Rel. 4.8.0, 6 March 2012"
LIBXML="2.7.6" USE_STATS"

thanks in advance for your help
Steve

Steve Toutant, M. Sc.
Analyste en géomatique
Secteur environnement
Direction de la santé environnementale et de la toxicologie
Institut national de santé publique du Québec
Tél.: 418 646-6777 poste 30015
Téléc.: 418 644-4593
steve(dot)toutant(at)inspq(dot)qc(dot)ca
http://www.inspq.qc.ca
http://www.monclimatmasante.qc.ca
Pour me rejoindre par courrier
Service de l'infrastructure et du réseau
Direction des technologies de l'information
Ministère de la Sécurité Publique
2525 boul. Laurier, 2è étage
Tour Laurentides
Québec, G1V 2L2

Responses

Browse pgsql-general by date

  From Date Subject
Next Message N 2013-11-28 17:46:51 What query optimisations are included in Postgresql?
Previous Message Pavel Stehule 2013-11-28 16:03:37 Re: unnest on multi-dimensional arrays