X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 70CC19FB28A for ; Thu, 24 Aug 2006 05:25:00 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 72524-05 for ; Thu, 24 Aug 2006 08:24:40 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web50313.mail.yahoo.com (web50313.mail.yahoo.com [206.190.39.215]) by postgresql.org (Postfix) with SMTP id 162059FB2D0 for ; Thu, 24 Aug 2006 05:24:39 -0300 (ADT) Received: (qmail 76183 invoked by uid 60001); 24 Aug 2006 08:24:31 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=OSGSyT9i2vVe5heSST2Rqy4VHxHPDiZEvupzgEw3O+Ecxu+ULzGpbK++2OtB6PlPBXzvcsIcMxJKpybX0Y0DORtq6WTRQIv8SU49ZpNHFZ7yUHwiusddfqMOTNlbmC4szdmDogZOMpGaT21J3F3/DZ6S23skRgXjNhK99TwChLM= ; Message-ID: <20060824082431.76181.qmail@web50313.mail.yahoo.com> Received: from [193.163.1.105] by web50313.mail.yahoo.com via HTTP; Thu, 24 Aug 2006 01:24:31 PDT Date: Thu, 24 Aug 2006 01:24:31 -0700 (PDT) From: Thor Tall Subject: Re: Slow delete/insert. To: Alexandre Leclerc , pgsql-admin@postgresql.org In-Reply-To: <1dc7f0e30608231105h1da7c892n43dd53c27bf1a4fe@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=3.816 tagged_above=0 required=5 tests=SPF_HELO_SOFTFAIL, SPF_SOFTFAIL, UPPERCASE_25_50 X-Spam-Level: *** X-Archive-Number: 200608/261 X-Sequence-Number: 22804 Please see my comments below --- Alexandre Leclerc wrote: > 2006/8/23, Thor Tall : > > The processing is done as follows: > > 1. Start transaction > > > > 2. check if message is in table 1 and if so delete > > records from table 1(1 row),2(2 rows),3(30 > rows),4(50 > > rows). > > > > 3. split the message and insert the date in table > 1(1 > > row),2(2 rows),3(30 rows),4(50 rows) > > > > 4. end transaction > > In addition to the other post, the SQL requests can > be the cause of > the slow performance. I don't know how you do it, > but the more you > think the database way the better your SQL requests > will be done. > Also, are the tables auto-deleting the records using > a real > Relationnal approach in the DB (references fields > with defined > update/delete auto-events) or are they deleted > manually by you with > SQL requests? The tables are deleted using cascaded deletes. > > The process can be very much slow of super-fast > depending of the > actual DB / Index / Table / Logic implementation and > the actual > programmer's SQL / Logic / requests. > > Best regards. > > -- > Alexandre Leclerc > I have the following simple tables which are populate: CREATE TABLE tb_Flight ( FLIGHT_ID integer UNIQUE NOT NULL, IFPLID varchar (20) NOT NULL , TIMESTAMP timestamp NULL , EVENT varchar (50) NULL , EVENTCLASS varchar (50) NULL , FLTSTATE varchar (50) NULL , ARCID varchar (10) NULL , ADEP varchar (4) NULL , ADES varchar (4) NULL , MODELTYP varchar (50) NULL , ARCTYP varchar (4) NULL , ETO_ADEP timestamp NULL , ETO_ADES timestamp NULL , ProcessTime_ms integer NULL , PRIMARY KEY (FLIGHT_ID) ); CREATE SEQUENCE seq_Flight_Id; CREATE TABLE tb_Flight_AD ( FLIGHT_ID integer REFERENCES tb_Flight ON DELETE CASCADE, AD_ID integer UNIQUE NOT NULL , AD varchar (4) NOT NULL , ETO timestamp NOT NULL ); CREATE SEQUENCE seq_Flight_AD_Id; CREATE TABLE tb_Flight_ASP ( FLIGHT_ID integer REFERENCES tb_Flight ON DELETE CASCADE, ASP_ID integer UNIQUE NOT NULL, AIRSPDES varchar (20) NOT NULL , ETI timestamp NOT NULL , XTI timestamp NOT NULL ); CREATE SEQUENCE seq_Flight_ASP_Id; CREATE TABLE tb_Flight_RTEPTS ( FLIGHT_ID integer REFERENCES tb_Flight ON DELETE CASCADE, RTEPTS integer UNIQUE NOT NULL, PT varchar (10) NOT NULL , ETO timestamp NOT NULL , Obsolete integer NOT NULL ); CREATE SEQUENCE seq_Flight_RTEPTS_Id; __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com