pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jerome Raupach <jraupach@intelcom.fr>
To: pgsql-sql@postgresql.org
Subject: performance on insert/update
Date: Tue, 29 Aug 2000 14:09:22 +0200
Message-ID: <39ABA7F2.6180FFC7@intelcom.fr> (raw)

I have a big problem of performance, please help me.

it is my work :
first : COPY table1 FROM 'file'        -> 43s,
	INSERT INTO table2             -> 34s, 
	UPDATE table2                  -> 1mn 29s ( =2m 46s : OK)

second : COPY table1 FROM 'same file'  -> 1m 10s,
	 INSERT INTO table2            -> 2m 14s,
	 UPDATE table2                 -> 5mn 20s ( =8m 44s )

third  : COPY table1 FROM 'same file'  -> 1m, 
	 INSERT INTO table2            -> 2m 20s, 
	 UPDATE table2                 -> 7mn 30s ( =10m 50s )
...


EXPLAIN UPDATE table2 (first, second and third) :

Merge Join  (cost=0.00..8425.71 rows=1 width=102)
-> Index Scan using index2 on table2(cost=0.00..8320.70 rows=1000
width=42)
      SubPlan
      ->  Index Scan using table1_pkey on table1  (cost=0.00..8.26
rows=1 width=46)
->  Index Scan using index2 on table2  (cost=0.00..60.00 rows=1000
width=60)
SubPlan
  ->  Index Scan using index2 on table2  (cost=0.00..10.28 rows=1
width=42)
      SubPlan
      ->  Aggregate  (cost=8.26..8.26 rows=1 width=4)
            ->  Index Scan using table1_pkey on table1  (cost=0.00..8.26
rows=1 width=4)
      ->  Index Scan using table1_pkey on table1  (cost=0.00..8.26
rows=1 width=46)
  ->  Index Scan using index2 on table2  (cost=0.00..10.28 rows=1
width=42)
      SubPlan
      ->  Aggregate  (cost=8.26..8.26 rows=1 width=4)
            ->  Index Scan using table1_pkey on table1  (cost=0.00..8.26
rows=1 width=4)
      ->  Index Scan using table1_pkey on table1  (cost=0.00..8.26
rows=1 width=46)


------------------------------------------------------------------------------

.../bin/postmaster -d2 -D .../data -p 19118 -i -N64 -B1024

CREATE TABLE table1 (f1 VARCHAR(20) NOT NULL, date_conn DATETIME NOT
NULL,
time INT4, PRIMARY KEY(f1, date_conn));
CREATE INDEX index1 ON table1( f1, date_conn ) ;

CREATE TABLE table2 (f1 VARCHAR(20),nb INT4,time INT4,tmc INT4,date
DATE);	
CREATE INDEX index2 ON table2( f1, date ) ;

CREATE VIEW view1 AS 
	SELECT f1, ( SELECT COUNT(*) FROM table1 
			WHERE table1.f1=table2.f1 
			  AND table1.date_conn::date=table2.date ) AS nb,
		   ( SELECT SUM(table1.time) FROM table1 
			WHERE table1.f1=table2.f1 
			  AND table1.date_conn::date=table2.date ) AS time, 		date FROM
table2 
	WHERE exists ( SELECT f1, date_conn FROM table1
			WHERE table1.f1=table2.f1 
			  AND table1.date_conn::date=table2.date) ;

CREATE INDEX index_view1 ON view1( f1, date ) ;

------------------------------------------------------------------------------

I have a file 'file' : 20162 tuples. (f1/date_conn/time)

"COPY table_brut FROM 'file' USING DELIMITERS '/';

INSERT INTO table2 
	SELECT DISTINCT f1, 0, 0, 0, date_conn::date 
	FROM table1 
	WHERE not exists ( 
		SELECT table2.f1, table2.date 
		FROM table2, table1 
		WHERE table2.f1=table1.f1 
		  AND table2.date=table1.date_conn::date 
		);

UPDATE table2 SET nb=nb+( 
		SELECT nb FROM view1 
			WHERE view1.f1=table2.f1 
			  AND view1.date=table2.date ), 
	          temps=time+( 
		SELECT time FROM view1 
			WHERE view1.f1=table2.f1 
			  AND view1.date=table2.date ) 
WHERE table2.f1=view1.f1 
  AND table2.date=view1.date ;

UPDATE table2 SET tmc=time/nb;
DELETE FROM table1;
------------------------------------------------------------------------------

Sorry for my english, and Thanks in advance.
Jerome.



view thread (2+ messages)  latest in thread

Message-ID: <39ABA7F2.6180FFC7@intelcom.fr>
Permalink:  ../39ABA7F2.6180FFC7@intelcom.fr/
Also on:    postgresql.org/message-id/39ABA7F2.6180FFC7@intelcom.fr

 · 

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-sql@postgresql.org
  Cc: jraupach@intelcom.fr
  Subject: Re: performance on insert/update
  In-Reply-To: <39ABA7F2.6180FFC7@intelcom.fr>
  References: <39ABA7F2.6180FFC7@intelcom.fr>

* 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