Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1djo3c-0005NB-SX for pgsql-performance@arkaria.postgresql.org; Mon, 21 Aug 2017 14:52:00 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1djo3b-0006I9-N3 for pgsql-performance@arkaria.postgresql.org; Mon, 21 Aug 2017 14:51:59 +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 1djo3a-0006HU-RW for pgsql-performance@postgresql.org; Mon, 21 Aug 2017 14:51:58 +0000 Received: from mout.perfora.net ([74.208.4.194]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1djo3Y-0000Xn-45 for pgsql-performance@postgresql.org; Mon, 21 Aug 2017 14:51:57 +0000 Received: from [100.84.38.146] ([174.204.0.226]) by mrelay.perfora.net (mreueus003 [74.208.5.2]) with ESMTPSA (Nemesis) id 0MS31o-1e7MIG111m-00TDXb; Mon, 21 Aug 2017 16:51:50 +0200 Content-Type: multipart/alternative; boundary=Apple-Mail-669C813A-793E-445C-9EB4-6F406AA9E5FD Mime-Version: 1.0 (1.0) Subject: Re: performance problem on big tables From: Michael DNA X-Mailer: iPhone Mail (14G60) In-Reply-To: Date: Mon, 21 Aug 2017 10:51:47 -0400 Cc: Mariel Cherkassky , "pgsql-performance@postgresql.org" Content-Transfer-Encoding: 7bit Message-Id: <8C271F26-85F7-4C84-852C-A51C470146DB@sqlexec.com> 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> To: Igor Neyman X-Provags-ID: V03:K0:PjYYKKNTOveHC5wNWdpBoPYo9D1U+ekEgpEAm0qbgJTentrpXsT Mg8s4BGf9j78E/E+FRpQ3SnMnhKQHQQn4ZRTGYkz0fJDSg0Lhhshex6l2h9eJbfrxbh8ZKj ocRhmNYRlMaNtnl6TBvv0w2tLo94mU4bQddwgmmHSbiccvxJQNCZj5eBEM19jX++JrUwLiy TmwknpX6+K6TSQOT1c/Pw== X-UI-Out-Filterresults: notjunk:1;V01:K0:vQDCxvgz0z4=:J+trSlf2Hl+CSOsN4fcApP 7s539RzKXRMKYUKpR/c8/dysWMC9zFT63KWOahsCr9+9Am58YHuKdfMoZsFYXxc9Bcp90nVfK eIFBsQcQHAFp2tmtmm4/YHGwJzHjjPJ5ORXe6F8bmjchU0D4DLvvFSMJFZaBWJAuVr+71tYpU FZ/KVDerXQcrEpnmb9NnxXPcbOh3/rXn2zstoMIgt44h8M97digNcMBq3RY1pkrpKXenvYpUb CymNBIbf4grxlrnV7tbc25uk4zB6VcXMKIhaeqUe1/wn9AE1Rg8+p3Pby+f2KeTwyBRB6wjWZ 5ZUIbkUkK0Kwes1zyMuhjSuvjjitA6vnu7ibEREmu10ThiXHLvRlXrWBvHCFrE784hzGi0oIt aqdjBDbIYfTK69Q0eo0JNiyoeO6+3jn7rJo7UOXsgQYcr7ND2M9mmjW7zu5QkLsJvGxt89RNo 05kBj9Dr9Rua9k71n6h8f5BOIyTyHtWn0BwjPrk2HVkGjheECYNoI3SRGQAYoQgvm06Qk3b2j 79BDJkVfKdmDo6vYnF0KDpMiCoO3qbz4W2ImN1zHeNH6GsILhWAEaWf/921M0E2oQpe2/CCPy /MN35KSUzqCqdjZPbGGOLROocFlKcyGpo2WVdcgK4It8u31m4UzzsDGzPq2ikf+wVAO6v8BYu TujN1ZjWdizYFW9Wclxw3LaT3GgFT1NTENe0fX91EnIBacg2RKDsieivsU5wqbK8WtuhfP9Ug ayUQsQomahCFVCI4An7hOXQJoec+Bj2jFCzJaDsDSfu3f9Wa8ZbRWw46POaw8nocPeSbo7WxY Lm018dt 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 --Apple-Mail-669C813A-793E-445C-9EB4-6F406AA9E5FD Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable If your procedures to get the data is part is a query predicate, then you ca= n still use ora2pg Sent from my iPhone > On Aug 21, 2017, at 10:35 AM, Igor Neyman wrote: >=20 > =20 > From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-own= er@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 > =20 > I had a system that consist from many objects(procedures,functions..) on a= n oracle database. We decided to integrate that system to postgresql. That s= ystem coppied alot of big tables from a different read only oracle database a= nd preformed on it alot of queries to produce reports. The part of getting t= he data is part of some procedures, I cant change it so freely. I'm searchin= g a way to improve the perfomance of the database because I'm sure that I di= dnt conifgure something well. Moreover, When I run complicted queries (joint= between 4 big tables and filtering) it takes alot of time and I see that th= e server is cacheing all my ram memory. > =20 > =20 > Probably your joins are done on Postgres side. > =20 > m.b. instead of Postgres pulling data from Oracle, you should try pushing d= ata from Oracle to Postgres using Oracle=E2=80=99s Heterogeneous Services an= d Postgres ODBC driver. In this case you do your joins and filtering on Orac= les side and just push the result set to Postgres. > That=E2=80=99s how I did migration from Oracle to Postgres. > =20 > Regards, > Igor Neyman --Apple-Mail-669C813A-793E-445C-9EB4-6F406AA9E5FD Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
If your procedures to get the data is p= art is a query predicate, then you can still use ora2pg

Sent from my i= Phone

On Aug 21, 2017, at 10:35 AM, Igor Neyman <ineyman@perceptron.com> wrote:

 

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 <MichaelD= BA@sqlexec.com>
Cc: pgsql-perform= ance@postgresql.org
Subject: Re: [PERFORM] performance problem on big tables

 

I had a system that consist from many objects(procedu= res,functions..) on an oracle database. We decided to integrate that system t= o postgresql. That system coppied alot of big tables from a different read o= nly oracle database and preformed on it alot of queries to produce reports. The part of getting the data is p= art of some procedures, I cant change it so freely. I'm searching a way to i= mprove the perfomance of the database because I'm sure that I didnt conifgur= e something well. Moreover, When I run complicted queries (joint between 4 big tables and filtering) it take= s alot of time and I see that the server is cacheing all my ram memory.=

 

 

Probably your joins are done on Postgre= s side.

 

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

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

 

Regards,

Igor Neyman

= --Apple-Mail-669C813A-793E-445C-9EB4-6F406AA9E5FD--