Re: PostgreSQL Tuning and running a query on a big data

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(at)gmail(dot)com
> <mailto:ronljohnsonjr(at)gmail(dot)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.