Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TjBj9-0000C0-7P for pgsql-performance@arkaria.postgresql.org; Thu, 13 Dec 2012 16:33:39 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1TjBj8-0001H7-OV for pgsql-performance@arkaria.postgresql.org; Thu, 13 Dec 2012 16:33:38 +0000 Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TjBj7-0001H2-TP for pgsql-performance@postgresql.org; Thu, 13 Dec 2012 16:33:38 +0000 Received: from nougat.ucs.ed.ac.uk ([129.215.13.205]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TjBj1-0002u0-2M for pgsql-performance@postgresql.org; Thu, 13 Dec 2012 16:33:37 +0000 Received: from lmtp1.ucs.ed.ac.uk (lmtp1.ucs.ed.ac.uk [129.215.149.64]) by nougat.ucs.ed.ac.uk (8.13.8/8.13.4) with ESMTP id qBDGXTo8001966; Thu, 13 Dec 2012 16:33:29 GMT Received: from [129.215.14.65] (sce-bio-c01478.bio.ed.ac.uk [129.215.14.65]) (authenticated user=lfischer mech=PLAIN bits=0) by lmtp1.ucs.ed.ac.uk (8.13.8/8.13.7) with ESMTP id qBDGXSSQ013444 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT); Thu, 13 Dec 2012 16:33:29 GMT Message-ID: <50CA0358.9040401@staffmail.ed.ac.uk> Date: Thu, 13 Dec 2012 16:33:28 +0000 From: Lutz Fischer User-Agent: Mozilla/5.0 (X11; Linux i686 on x86_64; rv:17.0) Gecko/17.0 Thunderbird/17.0 MIME-Version: 1.0 To: =?UTF-8?B?RmlsaXAgUmVtYmlhxYJrb3dza2k=?= CC: pgsql-performance@postgresql.org Subject: Re: problem with large inserts References: <50C9F63D.5040400@staffmail.ed.ac.uk> In-Reply-To: X-Enigmail-Version: 1.4.6 Content-Type: text/plain; charset=UTF-8 X-Edinburgh-Scanned: at nougat.ucs.ed.ac.uk with MIMEDefang 2.60, Sophie, Sophos Anti-Virus, Clam AntiVirus X-Scanned-By: MIMEDefang 2.60 on 129.215.13.205 X-Scanned-By: MIMEDefang 2.52 on 129.215.149.64 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable X-MIME-Autoconverted: from 8bit to quoted-printable by nougat.ucs.ed.ac.uk id qBDGXTo8001966 X-Pg-Spam-Score: -4.2 (----) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org Thanks a lot you saved my day create temp table foo AS SELECT DISTINCT ... did take a mere 77464.744 ms And an additional Insert into LPP select * from foo; Just 576.909 ms I don't really understand why it's working via a temp table but not directly (or in any reasonable amount of time) - but at least I have a solution I can work with. On 13/12/12 16:09, Filip Rembia=C5=82kowski wrote: > Just an idea - how long does it take to run _only_ > CREATE TEMP TABLE foo AS > > > > > On Thu, Dec 13, 2012 at 4:37 PM, Lutz Fischer > wrote: >> Hi >> >> 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 =3D sid) pp >> INNER JOIN >> has_protein hp1 >> ON pp.p1id =3D hp1.pid >> INNER JOIN >> has_protein hp2 >> ON pp.p2_id =3D hp2.pid >> INNER JOIN >> (SELECT * FROM L WHERE s_id =3D sid) l >> ON (hp1.pid =3D l.p1id AND hp2.pid =3D l.p2id AND hp1.ppos + >> pp.s1 =3D l.s1 AND hp2.ppos + pp.s2 =3D l.s2) >> OR (hp1.pid =3D l.p2id AND hp2.pid =3D l.p1id AND hp1.ppos + >> pp.s1 =3D l.s2 AND hp2.ppos + pp.s2 =3D l.s1) >> ; >> >> If I run only >> >> SELECT DISTINCT PPid, LID FROM >> (SELECT * FROM PP WHERE s_id =3D 708) pp >> INNER JOIN >> has_protein hp1 >> ON pp.p1id =3D hp1.pid >> INNER JOIN >> has_protein hp2 >> ON pp.p2_id =3D hp2.pid >> INNER JOIN >> (SELECT * FROM L WHERE s_id =3D 708) l >> ON (hp1.pid =3D l.p1id AND hp2.pid =3D l.p2id AND hp1.ppos + >> pp.s1 =3D l.s1 AND hp2.ppos + pp.s2 =3D l.s2) >> OR (hp1.pid =3D l.p2id AND hp2.pid =3D l.p1id AND hp1.ppos + >> pp.s1 =3D l.s2 AND hp2.ppos + pp.s2 =3D 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 day. >> >> 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. >> >> >> Thanks for any responds, >> >> Lutz Fischer >> >> >> -- >> The University of Edinburgh is a charitable body, registered in >> Scotland, with registration number SC005336. >> >> >> >> -- >> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.or= g) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-performance --=20 Lutz Fischer lfischer@staffmail.ed.ac.uk +44 131 6517057 The University of Edinburgh is a charitable body, registered in Scotland, with registration number SC005336. --=20 Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance