Re: problem with large inserts

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I would strongly discourage you from droppping the referential integrity. You risk data corruption, which will cost you a good deal of time to sort it out properly, and corruption prevents you to apply the R.I. again. Also it has hardly any performance impact.

Are the plans different? ( i guess you've looked at http://wiki.postgresql.org/wiki/Slow_Query_Questions ?)

> Date: Thu, 13 Dec 2012 15:37:33 +0000
> From: lfischer@xxxxxxxxxxxxxxxxxx
> To: pgsql-performance@xxxxxxxxxxxxxx
> Subject: problem with large inserts
>
> 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 = 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 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@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux