Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1byla4-0008U6-HX for pgsql-performance@arkaria.postgresql.org; Mon, 24 Oct 2016 20:10:48 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1byla4-0004bZ-4K for pgsql-performance@arkaria.postgresql.org; Mon, 24 Oct 2016 20:10:48 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1byla3-0004Ne-5p for pgsql-performance@postgresql.org; Mon, 24 Oct 2016 20:10:47 +0000 Received: from mail.itasnett.no ([128.39.191.25] helo=symbm03.itasdata.no) by makus.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bylX8-0007HR-Cv for pgsql-performance@postgresql.org; Mon, 24 Oct 2016 20:07:48 +0000 X-AuditID: 8027bf19-549ff70000004339-c0-580e6a07893e Received: from nibioexch.nibio.no (pf-18457.bioforsk.no [128.39.184.57]) by symbm03.itasdata.no (Symantec Messaging Gateway) with SMTP id 84.6B.17209.70A6E085; Mon, 24 Oct 2016 22:07:38 +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; Mon, 24 Oct 2016 22:07:34 +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; Mon, 24 Oct 2016 22:07:35 +0200 From: Lars Aksel Opsahl To: Tom Lane 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: AQHSLc5ExwlyuwQD1U6YnaijFY0RCKC3bbgAgACMrkA= Date: Mon, 24 Oct 2016 20:07:35 +0000 Message-ID: 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+NgFprMIsWRmVeSWpSXmKPExsXSoL7DUpcriy/CYNdxNYvrM88xW5xadZfJ gcnjwr5p7B6Lnm1kCWCK4rJJSc3JLEst0rdL4Mp405pZ8E2r4v6hfYwNjAtVuxg5OSQETCTu X1rM2sXIxSEkcIJR4tvSz8wQzjZGiR0dJ5kgnGWMEgs7rjODtLAJ6Em839fEDmKLCChI9D14 B2YzC3hKdJxaxQhiCwvESsycu5sVoiZO4siuBjYI20qi7dFusDksAqoS61Y8AIvzCphLvFnX A3XGJkaJ318ngg3iFNCV6H2xEGgBBwejgKzE3CZeiF3iEjOO3mGBeEFAYsme88wQtqjEy8f/ WCFsHYmz158wQtjyEscm/WKE6NWTuDF1ChuErS2xbOFrZogbBCVOznzCMoFRfBaSFbOQtMxC 0jILScsCRpZVjMLFlblJuQbGepklicUpiSWJenn5mxjBUbVfcgfjt+suhxgFOBiVeHhX6PJF CLEmlgE1HGKU4GBWEuG9HQsU4k1JrKxKLcqPLyrNSS0+xCjNwaIkzvvsQHOYkEB6Yklqdmpq QWoRTJaJg1OqgdHnemihksTKT/3alp3TzdTa9pSePfXMqCNKZ/XHZkWnzsuZx+U7q0L3bz0g JTGp6beH+Uw58e1frQVTkgRviD2dVSFy+7YCw362Aut2pTA/V8bZPzW9P3MY8q5TjZvqWC8m 9nmezRWjUtG3l+r3SWQ8UZpzvcFK9ffaGV8dbt0RXdrzMJe5T4mlOCPRUIu5qDgRAPcgIAum AgAA 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 Yes this makes both the update and both selects much faster. We are now dow= n to 3000 ms. for select, but then I get a problem with another SQL where I= only use epoch in the query.=20 SELECT count(o.*) FROM met_vaer_wisline.nora_bc25_observation o WHERE o.ep= och =3D 1288440000; count=20 ------- 97831 (1 row) Time: 92763.389 ms To get the SQL above work fast it seems like we also need a single index on= the epoch column, this means two indexes on the same column and that eats = memory when we have more than 4 billion rows. Is it any way to avoid to two indexes on the epoch column ? Thanks. Lars EXPLAIN analyze SELECT count(o.*) FROM met_vaer_wisline.nora_bc25_observa= tion o WHERE o.epoch =3D 1288440000; -[ RECORD 1 ]--------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------------------------------- QUERY PLAN | Aggregate (cost=3D44016888.13..44016888.14 rows=3D1 width=3D4= 2) (actual time=3D91307.470..91307.471 rows=3D1 loops=3D1) -[ RECORD 2 ]--------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------------------------------- QUERY PLAN | -> Index Scan using idx_met_vaer_wisline_nora_bc25_observat= ion_test on nora_bc25_observation o (cost=3D0.58..44016649.38 rows=3D95500= width=3D42) (actual time=3D1.942..91287.495 rows=3D97831 loops=3D1) -[ RECORD 3 ]--------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------------------------------- QUERY PLAN | Index Cond: (epoch =3D 1288440000) -[ RECORD 4 ]--------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------------------------------- QUERY PLAN | Total runtime: 91307.534 ms EXPLAIN analyze SELECT count(o.*) FROM=20 met_vaer_wisline.nora_bc25_observation o, met_vaer_wisline.new_data n WHERE o.point_uid_ref =3D n.id_point AND o.epoch =3D n.epoch; -[ RECORD 1 ]--------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------------- QUERY PLAN | Aggregate (cost=3D131857.71..131857.72 rows=3D1 width=3D42) (= actual time=3D182.459..182.459 rows=3D1 loops=3D1) -[ RECORD 2 ]--------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------------- QUERY PLAN | -> Nested Loop (cost=3D0.58..131727.00 rows=3D52283 width= =3D42) (actual time=3D0.114..177.420 rows=3D50000 loops=3D1) -[ RECORD 3 ]--------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------------- QUERY PLAN | -> Seq Scan on new_data n (cost=3D0.00..1136.00 rows= =3D50000 width=3D8) (actual time=3D0.050..7.873 rows=3D50000 loops=3D1) -[ RECORD 4 ]--------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------------- QUERY PLAN | -> Index Scan using idx_met_vaer_wisline_nora_bc25_ob= servation_test on nora_bc25_observation o (cost=3D0.58..2.60 rows=3D1 widt= h=3D50) (actual time=3D0.003..0.003 rows=3D1 loops=3D50000) -[ RECORD 5 ]--------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------------- QUERY PLAN | Index Cond: ((point_uid_ref =3D n.id_point) AND = (epoch =3D n.epoch)) -[ RECORD 6 ]--------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------------- QUERY PLAN | Total runtime: 182.536 ms Time: 3095.618 ms Lars =20 ________________________________________ 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