Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dmDB3-0000bs-Dd for pgsql-performance@arkaria.postgresql.org; Mon, 28 Aug 2017 06:05:37 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dmDB2-0005Fw-Jh for pgsql-performance@arkaria.postgresql.org; Mon, 28 Aug 2017 06:05:36 +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 1dmDB1-0005Dr-Pr for pgsql-performance@postgresql.org; Mon, 28 Aug 2017 06:05:35 +0000 Received: from mail-wm0-x22b.google.com ([2a00:1450:400c:c09::22b]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dmDAy-00026U-LC for pgsql-performance@postgresql.org; Mon, 28 Aug 2017 06:05:35 +0000 Received: by mail-wm0-x22b.google.com with SMTP id t201so14720185wmt.1 for ; Sun, 27 Aug 2017 23:05:32 -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=5AehbB5uBxXtkIhyUnO0BewqndJMpAklgUNwJOy/i/k=; b=BNDuqhcVT6uhs9rmztKgcM5R86X65Vy/gbCrOiVrVDh4DIB2WuHGaD0cKUn2wmdVLF RQ7xCT1JTgnOxs0FiU3tDAO3Rqe+c7YktC4uke8lLPOMIa9sLy4SN4HIpxkrCuy99U6T 6HsIzA6gpNLkXIsbu4i4rEavnE0r72JrulBwBhjDPbrF2JYs1CMh+CysJdrXCeBp6B2o 2egFZpMaQfhp0c2XFUWfLAx/FZwqOBbtWyNG614smH1H6My5PhgJZDq6yS5DhaxPbxLB K21pvcoMWeVlt8znl5BHDpqoL24/KlOdi68BByAaS+ZdgsH0Pm1kMCEVrpFk9vu+q5CZ Oydw== 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=5AehbB5uBxXtkIhyUnO0BewqndJMpAklgUNwJOy/i/k=; b=KSMNFo45zmm7DSSvv+rbJcCwfNLW56UNDIDo5Ibg+ZsFVNdu3PClnFK+qxyZJQ1RqA JE27FIyopsciTn+YjMbXrDVw2Lm3/nqQjJxvi8QYEvHEkVUi3v78LWfswwB/gTo1XPv+ x7xF3IPm6w9skS5E4/fZwR7FPb/o6vDmrr7vVYr+vL5V2EhxqC1dVVaQkG7/+9FnDDRq XomdUsTXjTv5Ax+toRwXtO6LahE4QzQOgy3BMohkQQveN7dKVvo4KFGgVrkf8aczTM1X NyWa3MLrYiNNHe06iJNFb10WvnkyRteHpF8vlWLax43s1aNlhHg8rsbDoqDFLKePIxyY s0MQ== X-Gm-Message-State: AHYfb5ji1qnHG5xOZMKnQ0CNcZBHzZ2BuSEkt7xmsVaof22K5vIAkjCl 5zta8aVmDZuFhLl5eIi604b9RkbXmg== X-Received: by 10.28.107.69 with SMTP id g66mr3383602wmc.119.1503900331564; Sun, 27 Aug 2017 23:05:31 -0700 (PDT) MIME-Version: 1.0 Received: by 10.28.144.139 with HTTP; Sun, 27 Aug 2017 23:05:30 -0700 (PDT) In-Reply-To: References: <88350892-C4AB-4191-9F8E-DB7CFA81F6C6@gmail.com> <00990FBB-D288-4878-8B8E-36E24F808F2C@gmail.com> From: Mariel Cherkassky Date: Mon, 28 Aug 2017 09:05:30 +0300 Message-ID: Subject: Re: performance problem on big tables To: Claudio Freire Cc: "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary="001a1147ccbe0a635c0557ca15ff" 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 --001a1147ccbe0a635c0557ca15ff Content-Type: text/plain; charset="UTF-8" I have the newest version : select oracle_diag(); oracle_diag --------------------------------------------------------------------------------------------------------------------- oracle_fdw 1.5.0, PostgreSQL 9.6.3, Oracle client 11.2.0.4.0, ORACLE_HOME=/PostgreSQL/9.6/tools/instantclient_11_2/ (1 row) Is there a prefetch also for local tables ? I mean If I run with a cursor over results of a select query, mybe setting the prefetch for a local table might also improve performance ? 2017-08-28 8:51 GMT+03:00 Claudio Freire : > On Sun, Aug 27, 2017 at 1:34 PM, Mariel Cherkassky > wrote: > > Hi, yes indeed I'm using laurenz`s oracle_fdw extension. I tried to run > it > > but I'm getting error > > > > dbch=# ALTER FOREIGN TABLE tc_sub_rate_ver_prod OPTIONS ( SET prefetch > 10240 > > ); > > ERROR: syntax error at or near "10240" > > LINE 1: ...N TABLE tc_sub_rate_ver_prod OPTIONS ( SET prefetch 10240 ); > > > > > > dbch=# alter foreign table tc_sub_rate_ver_prod OPTIONS (SET prefetch > > '10240'); > > ERROR: option "prefetch" not found > > Oh, sorry, I hadn't seen this until I hit send. > > Unless the documentation is inaccurate or you're using a really old > version (from the changelog that option is from 2016), that should > work. > > I don't have enough experience with oracle_fdw to help there, most of > my dealings have been with postgres_fdw. > --001a1147ccbe0a635c0557ca15ff Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
I have the newest version :=C2=A0
select oracle_diag();
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0oracle_diag
= ---------------------------------------------------------------------------= ------------------------------------------
=C2=A0orac= le_fdw 1.5.0, PostgreSQL 9.6.3, Oracle client 11.2.0.4.0, ORACLE_HOME=3D/Po= stgreSQL/9.6/tools/instantclient_11_2/
(1 row)
<= div dir=3D"ltr">

Is there a prefet= ch also for local tables ? I mean If I run with a cursor over results of a = select query, mybe setting the prefetch for a local table might also improv= e performance ?

2017-08-28 8:51 GMT+03:00 Claudio Freire = <klaussfreire@gmail.com>:
On Sun, Aug 27, 2017 at 1:34 PM, Mariel Cherkassky<= br> <mariel.cherkassky@gmail.= com> wrote:
> Hi, yes indeed I'm using laurenz`s oracle_= fdw extension. I tried to run it
> but I'm getting error
>
> dbch=3D# ALTER FOREIGN TABLE tc_sub_rate_ver_prod OPTIONS ( SET prefet= ch 10240
> );
> ERROR:=C2=A0 syntax error at or near "10240"
> LINE 1: ...N TABLE tc_sub_rate_ver_prod OPTIONS ( SET prefetch 10240 )= ;
>
>
> dbch=3D#=C2=A0 alter foreign table tc_sub_rate_ver_prod OPTIONS (SET p= refetch
> '10240');
> ERROR:=C2=A0 option "prefetch" not found

Oh, sorry, I hadn't seen this until I hit send.

Unless the documentation is inaccurate or you're using a really old
version (from the changelog that option is from 2016), that should
work.

I don't have enough experience with oracle_fdw to help there, most of my dealings have been with postgres_fdw.

--001a1147ccbe0a635c0557ca15ff--