pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Thor Tall <tall_thor@yahoo.com>
To: Alexandre Leclerc <alexandre.leclerc@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Re: Slow delete/insert.
Date: Thu, 24 Aug 2006 01:24:31 -0700 (PDT)
Message-ID: <20060824082431.76181.qmail@web50313.mail.yahoo.com> (raw)
In-Reply-To: <1dc7f0e30608231105h1da7c892n43dd53c27bf1a4fe@mail.gmail.com>

Please see my comments below

--- Alexandre Leclerc <alexandre.leclerc@gmail.com>
wrote:

> 2006/8/23, Thor Tall <tall_thor@yahoo.com>:
> > 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 



view thread (7+ messages)  latest in thread

Message-ID: <20060824082431.76181.qmail@web50313.mail.yahoo.com>
Permalink:  ../20060824082431.76181.qmail@web50313.mail.yahoo.com/
Also on:    postgresql.org/message-id/20060824082431.76181.qmail@web50313.mail.yahoo.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: tall_thor@yahoo.com, alexandre.leclerc@gmail.com
  Subject: Re: Slow delete/insert.
  In-Reply-To: <20060824082431.76181.qmail@web50313.mail.yahoo.com>
  References: <1dc7f0e30608231105h1da7c892n43dd53c27bf1a4fe@mail.gmail.com> <20060824082431.76181.qmail@web50313.mail.yahoo.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox