Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bz8hm-0008GA-27 for pgsql-performance@arkaria.postgresql.org; Tue, 25 Oct 2016 20:52:18 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bz8hl-0002MM-DM for pgsql-performance@arkaria.postgresql.org; Tue, 25 Oct 2016 20:52:17 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bz8hi-0002JD-2E for pgsql-performance@postgresql.org; Tue, 25 Oct 2016 20:52:14 +0000 Received: from symbm03.itasdata.no ([128.39.191.25]) by magus.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bz8hd-0004ww-Kp for pgsql-performance@postgresql.org; Tue, 25 Oct 2016 20:52:12 +0000 X-AuditID: 8027bf19-55dff70000004339-57-580fc5f6de92 Received: from nibioexch.nibio.no (pf-18457.bioforsk.no [128.39.184.57]) by symbm03.itasdata.no (Symantec Messaging Gateway) with SMTP id 32.8D.17209.6F5CF085; Tue, 25 Oct 2016 22:52:08 +0200 (CEST) Received: from NIBIOEXCH01.int.nibio.no (10.100.32.20) by NIBIOEXCH02.int.nibio.no (10.100.32.21) with Microsoft SMTP Server (TLS) id 15.1.225.42; Tue, 25 Oct 2016 22:52:05 +0200 Received: from NIBIOEXCH01.int.nibio.no ([10.100.32.20]) by NIBIOEXCH01.int.nibio.no ([10.100.32.20]) with mapi id 15.01.0225.041; Tue, 25 Oct 2016 22:52:05 +0200 From: Lars Aksel Opsahl To: Tom Lane , Scott Marlowe CC: "pgsql-performance@postgresql.org" Subject: Re: Fast insert, but slow join and updates for table with 4 billion rows Thread-Topic: [PERFORM] Fast insert, but slow join and updates for table with 4 billion rows Thread-Index: AQHSLc5ExwlyuwQD1U6YnaijFY0RCKC3bbgAgAFIyrk= Date: Tue, 25 Oct 2016 20:52:05 +0000 Message-ID: <1511d5311a414c49b9cc061b84e04a3d@nibio.no> References: ,<58dff6f9c2e64a8bb82a990ea1910d32@nibio.no> ,<21210.1477313528@sss.pgh.pa.us> In-Reply-To: <21210.1477313528@sss.pgh.pa.us> Accept-Language: nb-NO, en-US Content-Language: nb-NO X-MS-Has-Attach: X-MS-TNEF-Correlator: x-ms-exchange-messagesentrepresentingtype: 1 x-originating-ip: [10.100.44.5] Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable MIME-Version: 1.0 X-Brightmail-Tracker: H4sIAAAAAAAAA+NgFprLIsWRmVeSWpSXmKPExsXSoL7DUvfHUf4Ig+UHuC2uzzzHbLHs03lG i1Or7jI5MHvsnHWX3ePCvmnsHouebWQJYI7isklJzcksSy3St0vgyvg1MaVggVjFs8nXWRsY 7wl2MXJySAiYSNy61MnaxcjFISRwglHi4PwrUM42RonPMzdCOcsYJbYua2UGaWET0JN4v6+J HcQWEfCUuLH4PSuIzQxkd5xaxQhiCwvESsycu5sVoiZO4siuBjYI20ri4MqJYDUsAqoSd/ae YwKxeQXMJQ6u7WeCWLaJUeL3V4giTgFdid4XC4GWcXAwCshKzG3ihdglLjHj6B0WiBcEJJbs Oc8MYYtKvHz8jxXC1pE4e/0JI4QtL3Fs0i9GiF49iRtTp7BB2NoSyxa+Zoa4QVDi5MwnLBMY xWchWTELScssJC2zkLQsYGRZxShcXJmblGtgrJdZklickliSqJeXv4kRHGn7JXcwfrvucohR gINRiYdXIIs/Qog1sQyo4RCjBAezkgjv+/1AId6UxMqq1KL8+KLSnNTiQ4zSHCxK4rzPDjSH CQmkJ5akZqemFqQWwWSZODilGhhl9v56tU/l8Fxn+wObQ12l7677MWPxo9O/128XiRdf2WX1 3uOFqcW2k28NFqSFBLVduc59dtLOLff//0nY5vVfP/RMwqO5gj2qPd0rpWO+e3BZxK7P8j2e EHHF1mhT2j1r1yqncme/S7uUGdawt5QmKd4tFp6nKW/L/Ef7Qqp/y4HXdyQOHViuxFKckWio xVxUnAgA+hbYBLACAAA= X-Pg-Spam-Score: -1.9 (-) 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 Hi I have now tested through insert and the updated and it works extremely goo= d with out doing any partitioning on big table (428.812.8392 rows) this cas= e, when we used a common index as Tom Suggested.=20 We are able to insert 172.000 rows pr. second. The number of rows are competed based total time from when we start to read= the csv files and until the last file is done. We use GNU parallel and run= 5 threads. The number of inserts are actually 172.000 * 2 because first we= copy the rows into a temp table and there we prepare the date and then ins= ert them into the common big main table. There is no errors in the log.=20 We are able update 98.000 rows pr, second. Since each update also means one insert we are close 200.000 inserts and up= dates pr. second. For update we give a column column that is null a value. = Thats is done for all the 4.3 billions rows. We run 5 threads in parallel h= ere also, and there is no error and no dead locks.=20 To get around the problem with duplication of indexes it's solvable in this= project because first we add date and then we do analyses, this means that= we can have different indexes when adding data and we are using them. In this project we going add about 25 billions geo located observations whi= ch which will be used for doing analyses. I suppose that we at some level h= ave to do partitioning but so far Postgres has worked extremely well even i= f it's based on MVCC.=20 Postgres/Postgis software and communities are sure for sure really fun to w= ork with Postgres/Postgis open source software hold a very high quality.=20 Thanks. Lars ________________________________________ Fra: pgsql-performance-owner@postgresql.org p=E5 vegne av Tom Lane Sendt: 24. oktober 2016 14:52 Til: Lars Aksel Opsahl Kopi: pgsql-performance@postgresql.org Emne: Re: [PERFORM] Fast insert, but slow join and updates for table with 4= billion rows Lars Aksel Opsahl writes: > In this example I have two tables one with 4 billion rows and another wit= h 50000 rows and then I try to do a standard simple join between this two t= ables and this takes 397391 ms. with this SQL (the query plan is added is = further down) This particular query would work a lot better if you had an index on nora_bc25_observation (point_uid_ref, epoch), ie both join columns in one index. I get the impression that that ought to be the primary key of the table, which would be an even stronger reason to have a unique index on it. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance --=20 Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance