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.
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