Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dhHZz-00060A-JP for pgsql-performance@arkaria.postgresql.org; Mon, 14 Aug 2017 15:46:59 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dhHZz-0007gw-5s for pgsql-performance@arkaria.postgresql.org; Mon, 14 Aug 2017 15:46:59 +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 1dhHYB-0003MH-6q for pgsql-performance@postgresql.org; Mon, 14 Aug 2017 15:45:07 +0000 Received: from mail-qk0-x22b.google.com ([2607:f8b0:400d:c09::22b]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dhHY8-00025p-4L for pgsql-performance@postgresql.org; Mon, 14 Aug 2017 15:45:06 +0000 Received: by mail-qk0-x22b.google.com with SMTP id u139so52264514qka.1 for ; Mon, 14 Aug 2017 08:45:03 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=fa0BZbmG4UOljAngqKWHR/bIVkZKq/7bW1p3qTu2MKI=; b=GfZ3ZJRJaglTYozHvFfZd9+iXyh3JhvOg9Vs+1UrcbcX/zFA2zHhzTNqKe7esHZzG8 5Anh/UyDgubtFp/HtH8R9g2Y1M5SNxyKqbayeLKYCul0fkhEEA47bf+3IlLx1kPTJVR5 GTojXynyi+fm/s0BYoxh+dRu3qQuXhPvzsjTn3sITb9BqrGnUvoM6Hd31Oo+TBFpIv64 iMVdNhfbU+vIDpQa8/HVT7QIJkUZ6H9pQRHxGLVUZY1+9h+5BhBDhptIJGNJNodIZ4pX dc56SjjDLKTXLe2c67m2gTMcB8DAyAqw8E2uyYLXSk8rT/92pOBIkIwQSKapoNpShuWD qeuw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=fa0BZbmG4UOljAngqKWHR/bIVkZKq/7bW1p3qTu2MKI=; b=JKv2o6ez2II7NmLLlKlKI4ly3+U7knlKRkf+FDf2sR1I3MQ4FOGeCd/orpdapev0hM MkrBABEmqIwHhRF9nq1cJTPJdAWQy0o1fySQsk0581KtDMfm3BjdIH9KEbnlWeYsI0MW MktsUbEYOaYnffLCnut68R2behm6cgJO4htvhL1clwonTCk7uD4oXMaiPiLHKJhmidtS PpeLnGL+ZjCjEkN4zx/jgaGeSYsIxnDdAeMdvaHl8nxLMgXg1Zh7/OwLKCmKCLPC6AvA 8t+bui6ybPu5NszZBlYQ4QmiKqyF30KHkN84xOmprEH8GMFlqxwex7UMk9ZR6E7kgUv7 K5kA== X-Gm-Message-State: AHYfb5jeLkLdvcZWrtVt+P2y63odSUjE+AZddDhIYGdaPmS7YV/ogoas dT8RaQIorej/wnd9kPRSo7C94/Vb7w== X-Received: by 10.55.113.67 with SMTP id m64mr32921446qkc.227.1502725502200; Mon, 14 Aug 2017 08:45:02 -0700 (PDT) MIME-Version: 1.0 Received: by 10.12.179.9 with HTTP; Mon, 14 Aug 2017 08:45:01 -0700 (PDT) In-Reply-To: <8F28ABE4-047E-45CF-B502-480103D8EA97@gmail.com> References: <8F28ABE4-047E-45CF-B502-480103D8EA97@gmail.com> From: Rick Otten Date: Mon, 14 Aug 2017 11:45:01 -0400 Message-ID: Subject: Re: performance problem on big tables To: Daniel Blanch Bataller Cc: Mariel Cherkassky , "pgsql-performa." Content-Type: multipart/alternative; boundary="94eb2c05e3dcc0f7350556b88b88" 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 --94eb2c05e3dcc0f7350556b88b88 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Moving that many gigs of data across your network could also take a long time simply depending on your network configuration. Before spending a huge amount of energy tuning postgresql, I'd probably look at how long it takes to simply copy 20 or 30 G of data between the two machines. > El 14 ago 2017, a las 15:24, Mariel Cherkassky < > mariel.cherkassky@gmail.com> escribi=C3=B3: > > I have performance issues with two big tables. Those tables are located o= n > an oracle remote database. I'm running the quert : insert into > local_postgresql_table select * from oracle_remote_table. > > The first table has 45M records and its size is 23G. The import of the > data from the oracle remote database is taking 1 hour and 38 minutes. Aft= er > that I create 13 regular indexes on the table and it takes 10 minutes per > table ->2 hours and 10 minutes in total. > > The second table has 29M records and its size is 26G. The import of the > data from the oracle remote database is taking 2 hours and 30 minutes. Th= e > creation of the indexes takes 1 hours and 30 minutes (some are indexes on > one column and the creation takes 5 min and some are indexes on multiples > column and it takes 11 min. > > > --94eb2c05e3dcc0f7350556b88b88 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Moving that many gigs of data across your network could al= so take a long time simply depending on your network configuration.=C2=A0 B= efore spending a huge amount of energy tuning postgresql, I'd probably = look at how long it takes to simply copy 20 or 30 G of data between the two= machines.



El 14 ago 2017, a las 15= :24, Mariel Cherkassky <mariel.cherkassky@gmail.com> escribi=C3=B3:

I have performance = issues with two big tables. Those tables are located on an oracle remote da= tabase. I'm running the quert :=C2=A0insert into local_postgresql_table = select * from oracle_remote_table.

The first table has 45M records and its size is 23G. Th= e import of the data from the oracle remote database is taking 1 hour and 3= 8 minutes. After that I create 13 regular indexes on the table and it takes= 10 minutes per table ->2 hours and 10 minutes in total.

The second table has 29M records and = its size is 26G. The import of the data from the oracle remote database is = taking 2 hours and 30 minutes. The creation of the indexes takes 1 hours an= d 30 minutes (some are indexes on one column and the creation takes 5 min a= nd some are indexes on multiples column and it takes 11 min.



--94eb2c05e3dcc0f7350556b88b88--