From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: PostgreSQL Tuning and running a query on a big data
Date: Tue, 17 Nov 2020 13:19:51 -0600
Message-ID: <994aaff7-43c3-4c4c-0628-b133ddc6eee8@gmail.com> (raw)
In-Reply-To: <CALg-PKDgZ+VwzR2HXNCS7TwczqrV=9eSSF4gy4KSXgKF6zyFnA@mail.gmail.com>
References: <CALg-PKDzXckzuZfMu-zt768sG5T17iuqso8ton6Cgj_vPa6U9w@mail.gmail.com>
<165cda9c-07f3-0243-5378-5d520a8826f8@gmail.com>
<CALg-PKDgZ+VwzR2HXNCS7TwczqrV=9eSSF4gy4KSXgKF6zyFnA@mail.gmail.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: PostgreSQL Tuning and running a query on a big data
In-Reply-To: <994aaff7-43c3-4c4c-0628-b133ddc6eee8@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox