Lutz Fischer wrote: > I have currently some trouble with inserts into a table > > INSERT INTO LPP (PPID, LID) > SELECT DISTINCT PPid, LID FROM > (SELECT * FROM PP WHERE s_id = sid) pp > INNER JOIN > has_protein hp1 > ON pp.p1id = hp1.pid > INNER JOIN > has_protein hp2 > ON pp.p2_id = hp2.pid > INNER JOIN > (SELECT * FROM L WHERE s_id = sid) l > ON (hp1.pid = l.p1id AND hp2.pid = l.p2id AND hp1.ppos + > pp.s1 = l.s1 AND hp2.ppos + pp.s2 = l.s2) > OR (hp1.pid = l.p2id AND hp2.pid = l.p1id AND hp1.ppos + > pp.s1 = l.s2 AND hp2.ppos + pp.s2 = l.s1) > ; > > If I run only > > SELECT DISTINCT PPid, LID FROM > (SELECT * FROM PP WHERE s_id = 708) pp > INNER JOIN > has_protein hp1 > ON pp.p1id = hp1.pid > INNER JOIN > has_protein hp2 > ON pp.p2_id = hp2.pid > INNER JOIN > (SELECT * FROM L WHERE s_id = 708) l > ON (hp1.pid = l.p1id AND hp2.pid = l.p2id AND hp1.ppos + > pp.s1 = l.s1 AND hp2.ppos + pp.s2 = l.s2) > OR (hp1.pid = l.p2id AND hp2.pid = l.p1id AND hp1.ppos + > pp.s1 = l.s2 AND hp2.ppos + pp.s2 = l.s1) > ; > > it returns 200620 rows in 170649 ms ( thats just under 3 minutes). I > stopped the actual insert after about 8h. > > The table that the insert happens to, is following: > CREATE TABLE LPP > ( > ppid bigint NOT NULL, > lid bigint NOT NULL, > CONSTRAINT pk_lpp PRIMARY KEY (ppid,lid) > ) > > I also tried without the primary key but that one is still running for > more that a hour. > > Currently the table LPP holds 471139 rows. Its linking the PP and the L > table. > > There are no foreign keys referring to that table nor are there any > other constraints on it. > Previously I had foreign keys on lid and ppid refering to the L and PP > table. But in a desperate try to get some speed up I deleted these. - > But still... > > I am running postgresql 9.2 on a windows 2008 R2 server with 256 GB and > the database is on something like a raid 1+0 (actually a raid1e) > consisting of 3x4TB disks (limit of what could easily be fitted into the > server). > > At the given time there were no concurrent access to any of the > involved tables. > > Has anybody some idea why the insert takes so long and/or how to speed > things up a bit? I could live with something like half an hour - better > would be in minutes. Can you spot the bottleneck? Is it disk I/O? CPU? Do you see complaints in the log file? These are the usual suspects to tune to get better insert performance (if commit is not the problem): wal_buffers = 16MB checkpoint_segments = 100 checkpoint_completion_target = 0.9 Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
