Subject: Re: PostgreSQL Tuning and running a query on a big data
To: pgsql-admin@lists.postgresql.org
References: 
 <CALg-PKDzXckzuZfMu-zt768sG5T17iuqso8ton6Cgj_vPa6U9w@mail.gmail.com>
From: Ron <ronljohnsonjr@gmail.com>
Message-ID: <165cda9c-07f3-0243-5378-5d520a8826f8@gmail.com>
Date: Tue, 17 Nov 2020 11:39:55 -0600
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.10.0
MIME-Version: 1.0
In-Reply-To: 
 <CALg-PKDzXckzuZfMu-zt768sG5T17iuqso8ton6Cgj_vPa6U9w@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------C1D5B78B52DAA515E77D8978"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------C1D5B78B52DAA515E77D8978
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

On 11/17/20 11:34 AM, Sachin Kumar wrote:
> Hi Experts,
>
> 1.   I require guidance in tuning my PostgreSQL server installed on 
> Windows 2016 Server. Please help me out with the configuration. or a url 
> to ture PostgreSQL Server
>
> I am not able to find an expert who can help me in Postgres, that why I am 
> asking too many questions. I will be really grateful if you can help me out.
>
>
> 2.   I have to upload a 5 GB file into a Table. Will it allow a 5GB file 
> to be uploaded? or I have to increase the tablespace of the particular table.

Postgres auto-expands files as needed.

>
> 3.   while running the query on 1 million cards it is taking too much 
> time, say 150 min. is there any way I can reduce it.
> *Query I am using*
> UPDATE hk_card_master_test m
> SET "ACCOUNT_NUMBER" = v."v_account_number", "ISSUANCE_NUMBER" = 
> v."v_issuance_number","cron"=1
> FROM (
> SELECT h."id",h."CARD_SEQUENCE_NUMBER" ,h."ACCOUNT_NUMBER" 
> ,h."ISSUANCE_NUMBER",c."ACCOUNT_NUMBER" 
> v_account_number,c."ISSUANCE_NUMBER" v_issuance_number
> FROM
> hk_card_master_test h
> JOIN
> vdaccount_card_bank c
> ON SUBSTR(c."ACCOUNT_NUMBER", 1, 10) = h."CARD_SEQUENCE_NUMBER"
> ORDER BY h."id" ASC LIMIT 1000
> ) AS v
> WHERE m."CARD_SEQUENCE_NUMBER" = v."CARD_SEQUENCE_NUMBER";

Did you put indices on m."CARD_SEQUENCE_NUMBER and 
SUBSTR(c."ACCOUNT_NUMBER", 1, 10)?

-- 
Angular momentum makes the world go 'round.

--------------C1D5B78B52DAA515E77D8978
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    On 11/17/20 11:34 AM, Sachin Kumar wrote:<br>
    <blockquote type="cite"
cite="mid:CALg-PKDzXckzuZfMu-zt768sG5T17iuqso8ton6Cgj_vPa6U9w@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">
        <div>Hi Experts,<br>
          <br>
          1.   I require guidance in tuning my PostgreSQL server
          installed on Windows 2016 Server. Please help me out with
          the configuration. or a url to ture PostgreSQL Server<br>
          <br>
          I am not able to find an expert who can help me in Postgres,
          that why I am asking too many questions. I will be really
          grateful if you can help me out.<br>
          <br>
        </div>
        <div><br>
        </div>
        <div>2.   I have to upload a 5 GB file into a Table. Will it
          allow a 5GB file to be uploaded? or I have to increase the
          tablespace of the particular table.<br>
        </div>
      </div>
    </blockquote>
    <br>
    Postgres auto-expands files as needed.<br>
    <br>
    <blockquote type="cite"
cite="mid:CALg-PKDzXckzuZfMu-zt768sG5T17iuqso8ton6Cgj_vPa6U9w@mail.gmail.com">
      <div dir="ltr">
        <div><br>
          3.   while running the query on 1 million cards it is taking
          too much time, say 150 min. is there any way I can reduce it.</div>
        <div><span style="background-color:rgb(255,0,0)"><b>Query I am
              using</b></span></div>
        <div>UPDATE hk_card_master_test m<br>
          SET "ACCOUNT_NUMBER" = v."v_account_number", "ISSUANCE_NUMBER"
          = v."v_issuance_number","cron"=1<br>
          FROM (<br>
          SELECT h."id",h."CARD_SEQUENCE_NUMBER" ,h."ACCOUNT_NUMBER"
          ,h."ISSUANCE_NUMBER",c."ACCOUNT_NUMBER"
          v_account_number,c."ISSUANCE_NUMBER" v_issuance_number<br>
          FROM<br>
          hk_card_master_test h<br>
          JOIN<br>
          vdaccount_card_bank c<br>
          ON SUBSTR(c."ACCOUNT_NUMBER", 1, 10) =
          h."CARD_SEQUENCE_NUMBER"<br>
          ORDER BY h."id" ASC LIMIT 1000<br>
          ) AS v<br>
          WHERE m."CARD_SEQUENCE_NUMBER" = v."CARD_SEQUENCE_NUMBER";</div>
      </div>
    </blockquote>
    <br>
    Did you put indices on <font color="#0000bb">m."CARD_SEQUENCE_NUMBER</font>
    and <font color="#0000bb">SUBSTR(c."ACCOUNT_NUMBER", 1, 10)</font>?<br>
    <br>
    <div class="moz-signature">-- <br>
      Angular momentum makes the world go 'round.</div>
  </body>
</html>

--------------C1D5B78B52DAA515E77D8978--