Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1djnrK-0004Wa-KX for pgsql-performance@arkaria.postgresql.org; Mon, 21 Aug 2017 14:39: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 1djnrJ-0004qD-WC for pgsql-performance@arkaria.postgresql.org; Mon, 21 Aug 2017 14:39:18 +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 1djnpY-0000pt-Jy for pgsql-performance@postgresql.org; Mon, 21 Aug 2017 14:37:28 +0000 Received: from mail-wr0-x232.google.com ([2a00:1450:400c:c0c::232]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1djnpU-0001Tg-Py for pgsql-performance@postgresql.org; Mon, 21 Aug 2017 14:37:28 +0000 Received: by mail-wr0-x232.google.com with SMTP id z91so99438080wrc.4 for ; Mon, 21 Aug 2017 07:37:24 -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=NILuJFsyrrgYPwwjEgJFkz+Ec+76LfYSyHq56/YW02o=; b=SPO12qyV4RVerxx+NfrkJ79WXvvkEMtrCuXxiTs4iR0g+1VM1IPaT2xOAnbyGZL/Mw deVefOLsvHp6uhliMaq9D/fjrVW0Az4SEFaK44c629M1rdJDMKWiCvHbqIdueAFc596z Q25GjFYWYTS4TQmC4rXj87FsA7AgIwBXMD2RGxt7FwLammzzCrVDOjtVImoAGrd+6SQh t0A2mLvzOQdT84cEeAKxAxM9PgDsNlyJ9PdVKfA6qBVugwkmqPhyf33D6oA7jqrOr/nd tJ8UYiKCQlbpJsFa0k9cayZVRyAzWDOlozt0mU5Ih4V2Dzy7t7vWAnRurfYBXPwLmlta dY2A== 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=NILuJFsyrrgYPwwjEgJFkz+Ec+76LfYSyHq56/YW02o=; b=rxrQSFFXRi2PQMS1Fo8o2qYkeoyHYdsC/LCMolsICPkKVZWvRaKWbTxa9llbPt7LoU g6nk3EyBTJa8yugY7UkOzVpBKpDuPp+YJZQ0RpmthMSRTIn1wjqXtkfG90YA26gMfmwC h5iMKCyZVNyDYq4tJ/qsowUOnaAeE2/bRuuCwRZOB26GozPoVzw1Brpwqv7uxo/NyVba U2AYaZ1UCW3QykPaeKJon/foCKysFI9YZMu4x/RO7YesRFSYXckJRibsc5AeqUSA5q2T /4SomO+FU2ixQh2raWfnif4hBzrlxGfrrFr2LgOSE3NxPvOu4asRM/Nwg1vS6lv/+M5f Nnaw== X-Gm-Message-State: AHYfb5iW7bAMR+T1HGYl8hSokFEHei91WscB7H5buTfph/vNUFY6nNcp we2dXjJXRxLIKcx0Gbyh0PuXDcqx4w== X-Received: by 10.223.139.23 with SMTP id n23mr11769942wra.249.1503326243634; Mon, 21 Aug 2017 07:37:23 -0700 (PDT) MIME-Version: 1.0 Received: by 10.28.184.85 with HTTP; Mon, 21 Aug 2017 07:37:22 -0700 (PDT) In-Reply-To: References: <00990FBB-D288-4878-8B8E-36E24F808F2C@gmail.com> <236952AA-596F-4526-BB84-2EDC4906454F@gmail.com> <9BC9216C-5081-403D-A08C-77B3AB154F65@gmail.com> <599AE654.9010406@sqlexec.com> From: Mariel Cherkassky Date: Mon, 21 Aug 2017 17:37:22 +0300 Message-ID: Subject: Re: performance problem on big tables To: Igor Neyman Cc: MichaelDBA , "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary="f403045eae1abbc3dc0557446a64" 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 --f403045eae1abbc3dc0557446a64 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable I already finished migrating the system from oracle to postgresql. Right now, I'm trying to improve its performance - Im bringing data from another read only database that is updaded every minute. I cant push data from the oracle side to the postgresql side because the oracle database is read only= . 2017-08-21 17:35 GMT+03:00 Igor Neyman : > > > *From:* pgsql-performance-owner@postgresql.org [mailto:pgsql-performance- > owner@postgresql.org] *On Behalf Of *Mariel Cherkassky > *Sent:* Monday, August 21, 2017 10:20 AM > *To:* MichaelDBA > *Cc:* pgsql-performance@postgresql.org > *Subject:* Re: [PERFORM] performance problem on big tables > > > > I had a system that consist from many objects(procedures,functions..) on > an oracle database. We decided to integrate that system to postgresql. Th= at > system coppied alot of big tables from a different read only oracle > database and preformed on it alot of queries to produce reports. The part > of getting the data is part of some procedures, I cant change it so freel= y. > I'm searching a way to improve the perfomance of the database because I'm > sure that I didnt conifgure something well. Moreover, When I run complict= ed > queries (joint between 4 big tables and filtering) it takes alot of time > and I see that the server is cacheing all my ram memory. > > > > > > Probably your joins are done on Postgres side. > > > > m.b. instead of Postgres pulling data from Oracle, you should try pushing > data from Oracle to Postgres using Oracle=E2=80=99s Heterogeneous Service= s and > Postgres ODBC driver. In this case you do your joins and filtering on > Oracles side and just push the result set to Postgres. > > That=E2=80=99s how I did migration from Oracle to Postgres. > > > > Regards, > > Igor Neyman > --f403045eae1abbc3dc0557446a64 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
I already finished migrating the system f= rom oracle to postgresql. Right now, I'm trying to improve its performa= nce - Im bringing data from another read only database that is updaded ever= y minute. I cant push data from the oracle side to the postgresql side beca= use the oracle database is read only.

2017-08-21 17:35 GMT+03:00= Igor Neyman <ineyman@perceptron.com>:

=C2=A0

From: pgsql-performance-owner@= postgresql.org [mailto:pgsql-performance-owner@postgresql.org= ] On Behalf Of Mariel Cherkassky
Sent: Monday, August 21, 2017 10:20 AM
To: MichaelDBA <MichaelDBA@sqlexec.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] performance problem on big tables

=C2=A0

I had a system that consist from many objects(proced= ures,functions..) on an oracle database. We decided to integrate that = system to postgresql. That system coppied alot of big tables from a differe= nt read only oracle database and preformed on it alot of queries to produce reports. The part of getting the data is = part of some procedures, I cant change it so freely. I'm searching a wa= y to improve the perfomance of the database because I'm sure that I did= nt conifgure something well. Moreover, When I run complicted queries (joint between 4 big tables and filtering) it tak= es alot of time and I see that the server is cacheing all my ram memory.=

=C2=A0

=C2=A0

Probably your joins are done on Postg= res side.

=C2=A0

m.b. instead of Postgres pulling data= from Oracle, you should try pushing data from Oracle to Postgres using Ora= cle=E2=80=99s Heterogeneous Services and Postgres ODBC driver. In this case you do your joins and filtering on Oracles side and j= ust push the result set to Postgres.

That=E2=80=99s how I did migration fr= om Oracle to Postgres.

=C2=A0

Regards,

Igor Neyman


--f403045eae1abbc3dc0557446a64--