pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedperformance on insert/update
2+ messages / 2 participants
[nested] [flat]
* performance on insert/update
@ 2000-08-29 12:09 Jerome Raupach <jraupach@intelcom.fr>
0 siblings, 1 reply; 2+ messages in thread
From: Jerome Raupach @ 2000-08-29 12:09 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: performance on insert/update
@ 2000-08-29 14:55 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jerome Raupach <jraupach@intelcom.fr>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2000-08-29 14:55 UTC (permalink / raw)
To: Jerome Raupach <jraupach@intelcom.fr>; +Cc: pgsql-sql
Jerome Raupach <jraupach@intelcom.fr> writes:
> I have a big problem of performance, please help me.
You could code the update so it only evaluates the view once,
rather than twice per table2 row as you now have it:
UPDATE table2 SET
nb=table2.nb+view1.nb,
time=table2.time+view1.time
FROM view1
WHERE view1.f1=table2.f1 and view1.date=table2.date;
Subselects are nice, but they're not cheap. Joins are faster.
The view itself also seems to be pretty inefficiently coded,
although I'm not sure you can do much better given that GROUP BY
doesn't really work in views right now. Hopefully by 7.2 there
will be a better way to do the view.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2000-08-29 14:55 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-08-29 12:09 performance on insert/update Jerome Raupach <jraupach@intelcom.fr>
2000-08-29 14:55 ` Tom Lane <tgl@sss.pgh.pa.us>
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