Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bym8U-00046N-WD for pgsql-performance@arkaria.postgresql.org; Mon, 24 Oct 2016 20:46:23 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bym8U-00031P-Im for pgsql-performance@arkaria.postgresql.org; Mon, 24 Oct 2016 20:46:22 +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 1bym6o-0008Pm-OL for pgsql-performance@postgresql.org; Mon, 24 Oct 2016 20:44:38 +0000 Received: from mail.itasnett.no ([128.39.191.25] helo=symbm03.itasdata.no) by magus.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bym6l-0001AW-Of for pgsql-performance@postgresql.org; Mon, 24 Oct 2016 20:44:37 +0000 X-AuditID: 8027bf19-55dff70000004339-ef-580e72b09046 Received: from nibioexch.nibio.no (pf-18498.bioforsk.no [128.39.184.98]) by symbm03.itasdata.no (Symantec Messaging Gateway) with SMTP id D8.6B.17209.0B27E085; Mon, 24 Oct 2016 22:44:34 +0200 (CEST) Received: from NIBIOEXCH01.int.nibio.no (10.100.32.20) by NIBIOEXCH01.int.nibio.no (10.100.32.20) with Microsoft SMTP Server (TLS) id 15.1.225.42; Mon, 24 Oct 2016 22:44:32 +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:44:32 +0200 From: Lars Aksel Opsahl To: Scott Marlowe CC: Tom Lane , "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: AQHSLc5ExwlyuwQD1U6YnaijFY0RCKC3bbgAgACMrkD///FYAIAAJi+7 Date: Mon, 24 Oct 2016 20:44:32 +0000 Message-ID: <8b96ff68632e468dbab70d4f8533acbb@nibio.no> References: <58dff6f9c2e64a8bb82a990ea1910d32@nibio.no> <21210.1477313528@sss.pgh.pa.us> , In-Reply-To: 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+NgFprLIsWRmVeSWpSXmKPExsXSoL4jSXdTEV+EwclDOhbXZ55jtlj26Tyj xalVd5kcmD12zrrL7nFh3zR2j0XPNrIEMEdx2aSk5mSWpRbp2yVwZbz/tZyx4JRwxbbWZ0wN jG/4uhg5OSQETCROT5nM2MXIxSEkcIJRYvHNtewQzjZGiZXvHrCDVAkJLGOUeHyOCcRmE9CT eL+vCSwuIqAt8ejNdUYQm1kgU2LK4s9gNcICsRIz5+5mhaiJkziyq4ENwnaTWLXhE1icRUBV 4uL3x8wgNq+AucS2883MEIuXM0lMuvQArIhTIFBi04P5QAs4OBgFZCXmNvFC7BKXmHH0DgvE BwISS/acZ4awRSVePv7HCmHrSJy9/oQRwpaXODbpF9SdehI3pk5hg7C1JZYtfA11g6DEyZlP WCYwis9CsmIWkpZZSFpmIWlZwMiyilG4uDI3KdfAWC+zJLE4JbEkUS8vfxMjONL2S+5g/Hbd 5RCjAAejEg/vCl2+CCHWxDKghkOMEhzMSiK8hflAId6UxMqq1KL8+KLSnNTiQ4zSHCxK4rzP DjSHCQmkJ5akZqemFqQWwWSZODilGhh1lNmL7sVvkDwoXCX3OGbXmcel2oxtUmarL6c3fT+3 39Ki5oLwCrNv2lNNZpxgTwhT/FWx/f3hdfYnJuxJYbQNyz3KuHjd4/sLk5b6WBTxfpx83+Xf LSF/Nh3PSzfWz2F0ubnvPs87zcX6oeufl0eWcWz+Pbv11+m5xQdmx/z91Cpi5aa378dMJZbi jERDLeai4kQAlCs+J7ACAAA= 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 that helps, I tested this on now on the first column now. This basically means that only the first column in multiple column index ma= y be used in single column query. EXPLAIN analyze SELECT count(o.*) FROM met_vaer_wisline.nora_bc25_observat= ion o WHERE o.point_uid_ref =3D 15 ; = QUERY PLAN=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20 ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------ Aggregate (cost=3D45540.97..45540.98 rows=3D1 width=3D42) (actual time=3D= 24.715..24.715 rows=3D1 loops=3D1) -> Bitmap Heap Scan on nora_bc25_observation o (cost=3D477.66..45427.4= 0 rows=3D45430 width=3D42) (actual time=3D6.436..19.006 rows=3D43832 loops= =3D1) Recheck Cond: (point_uid_ref =3D 15) -> Bitmap Index Scan on idx_met_vaer_wisline_nora_bc25_observatio= n_test (cost=3D0.00..466.30 rows=3D45430 width=3D0) (actual time=3D6.320..= 6.320 rows=3D43832 loops=3D1) Index Cond: (point_uid_ref =3D 15) Total runtime: 24.767 ms (6 rows) Thanks Lars ________________________________________ Fra: Scott Marlowe Sendt: 24. oktober 2016 22:23 Til: Lars Aksel Opsahl Kopi: Tom Lane; pgsql-performance@postgresql.org Emne: Re: [PERFORM] Fast insert, but slow join and updates for table with 4= billion rows On Mon, Oct 24, 2016 at 2:07 PM, Lars Aksel Opsahl w= rote: > Hi > > Yes this makes both the update and both selects much faster. We are now d= own to 3000 ms. for select, but then I get a problem with another SQL where= I only use epoch in the query. > > SELECT count(o.*) FROM met_vaer_wisline.nora_bc25_observation o WHERE o.= epoch =3D 1288440000; > count > ------- > 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 eat= s memory when we have more than 4 billion rows. > > Is it any way to avoid to two indexes on the epoch column ? You could try reversing the order. Basically whatever comes first in a two column index is easier / possible for postgres to use like a single column index. If not. then you're probably stuck with two indexes. --=20 Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance