pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
performance 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