Can you post an explain analyze for the delete query? That will at least tell you if it is the delete itself which is slow, or a trigger / referential integrity constraint check. Which version of PG is this?
-- Mark Lewis On Wed, 2006-03-29 at 12:58 -0500, Eric Lauzon wrote: > Greetings, > > We have an issue where we have a database with many tables. > The layout of the database is 3 set of look alike tables with different > names. > Each set of tables has some referential integrety that point back to > the main > control table. > > On two set of tables we are able to efficiently delete referential and > main record > without a problems, but on the 3rd set we have an issue where the > control table is clugged > and delete seem to take forever , as example on the first two set a > delete of 60K record take about > 4 second to 10 second but on the 3rd set it can take as long as 3hours. > > This seem to be only affecting one database , the schema and way of > doing is replicated elsewhere > and if efficient. > > The question is, even after droping 3rd set integrity , dumping the > table data , deleting the table, > doing a manual checkpoint and recreating the table with the dump data , > with or without referential > integrity , the problems still araise. > > If we copy the data from the live table and do a create table aaa as > select * from problematic_table; > the table aaa operations are normal and efficient. > > This is why our investigation brought us to the folowing questions: > > 1. Are postgresql data file name are hashed references to table name(as > oracle)? [~path to data EX:/var/log/pgsql/data/[arbitraty > numbers]/[datafile]]? > > 2. If the data files are corrupted and we re-create is it possible it > uses the same files thus creating the same issue? > > 3. Since we know that all the tables has that problems is there an > internal table with undisclosed references to tables data files? > > I hope the questions were clear. > > Have a good day, and thank you in advance. > > > Eric Lauzon > [Recherche & Développement] > Above Sécurité / Above Security > Tél : (450) 430-8166 > Fax : (450) 430-1858 > > AVERTISSEMENT CONCERNANT LA CONFIDENTIALITÉ > > Le présent message est à l'usage exclusif du ou des destinataires mentionnés > ci-dessus. Son contenu est confidentiel et peut être assujetti au secret > professionnel. Si vous avez reçu le présent message par erreur, veuillez nous > en aviser immédiatement et le détruire en vous abstenant d'en faire une > copie, d'en divulguer le contenu ou d'y donner suite. > > CONFIDENTIALITY NOTICE > > This communication is intended for the exclusive use of the addressee > identified above. Its content is confidential and may contain privileged > information. If you have received this communication by error, please notify > the sender and delete the message without copying or disclosing it. > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
