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>
 <165cda9c-07f3-0243-5378-5d520a8826f8@gmail.com>
 <CALg-PKDgZ+VwzR2HXNCS7TwczqrV=9eSSF4gy4KSXgKF6zyFnA@mail.gmail.com>
From: Ron <ronljohnsonjr@gmail.com>
Message-ID: <994aaff7-43c3-4c4c-0628-b133ddc6eee8@gmail.com>
Date: Tue, 17 Nov 2020 13:19:51 -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-PKDgZ+VwzR2HXNCS7TwczqrV=9eSSF4gy4KSXgKF6zyFnA@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------4C9C6F35B52FA189037F560B"
Content-Language: en-US
Precedence: bulk

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


On 11/17/20 11:58 AM, Sachin Kumar wrote:
> Hi Ron,
>
> Thanks for the revert,
>
> Indices mean Indexes than yes I am using Indexes for teh table. if it is 
> something else, can you help me with a simple query of indices which I can 
> use in my code?

What indexes are on those tables?
Have you run ANALYZE on the tables?
Have you run EXPLAIN on the query?

The documentation explains how to use them.

> On Tue, Nov 17, 2020 at 11:10 PM Ron <ronljohnsonjr@gmail.com 
> <mailto:ronljohnsonjr@gmail.com>> wrote:
>
>     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.
>
>
>
> -- 
>
> Best Regards,
> Sachin Kumar

-- 
Angular momentum makes the world go 'round.

--------------4C9C6F35B52FA189037F560B
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>
    <br>
    <br>
    <div class="moz-cite-prefix">On 11/17/20 11:58 AM, Sachin Kumar
      wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CALg-PKDgZ+VwzR2HXNCS7TwczqrV=9eSSF4gy4KSXgKF6zyFnA@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">Hi Ron,
        <div><br>
        </div>
        <div>Thanks for the revert,</div>
        <div><br>
        </div>
        <div>Indices mean Indexes than yes I am using Indexes for teh
          table. if it is something else, can you help me with a simple
          query of indices which I can use in my code?</div>
        <img src="http://s.bl-1.com/LDWnMky.gif" moz-do-not-send="true"
          width="1" height="1"></div>
    </blockquote>
    <br>
    What indexes are on those tables?<br>
    Have you run ANALYZE on the tables?<br>
    Have you run EXPLAIN on the query?<br>
    <br>
    The documentation explains how to use them.<br>
    <br>
    <blockquote type="cite"
cite="mid:CALg-PKDgZ+VwzR2HXNCS7TwczqrV=9eSSF4gy4KSXgKF6zyFnA@mail.gmail.com">
      <div class="gmail_quote">
        <div dir="ltr" class="gmail_attr">On Tue, Nov 17, 2020 at 11:10
          PM Ron &lt;<a href="mailto:ronljohnsonjr@gmail.com"
            moz-do-not-send="true">ronljohnsonjr@gmail.com</a>&gt;
          wrote:<br>
        </div>
        <blockquote class="gmail_quote" style="margin:0px 0px 0px
          0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
          <div> On 11/17/20 11:34 AM, Sachin Kumar wrote:<br>
            <blockquote type="cite">
              <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">
              <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>-- <br>
              Angular momentum makes the world go 'round.</div>
          </div>
        </blockquote>
      </div>
      <br clear="all">
      <div><br>
      </div>
      -- <br>
      <div dir="ltr" class="gmail_signature"><br>
        Best Regards, <br>
        Sachin Kumar<br>
      </div>
    </blockquote>
    <br>
    <div class="moz-signature">-- <br>
      Angular momentum makes the world go 'round.</div>
  </body>
</html>

--------------4C9C6F35B52FA189037F560B--