pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgreSQL Tuning and running a query on a big data
5+ messages / 3 participants
[nested] [flat]
* PostgreSQL Tuning and running a query on a big data
@ 2020-11-17 17:34 Sachin Kumar <sachinkumaras@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Sachin Kumar @ 2020-11-17 17:34 UTC (permalink / raw)
To: pgsql-admin
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.
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";
--
Best Regards,
Sachin Kumar
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PostgreSQL Tuning and running a query on a big data
@ 2020-11-17 17:39 Ron <ronljohnsonjr@gmail.com>
parent: Sachin Kumar <sachinkumaras@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Ron @ 2020-11-17 17:39 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PostgreSQL Tuning and running a query on a big data
@ 2020-11-17 17:58 Sachin Kumar <sachinkumaras@gmail.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Sachin Kumar @ 2020-11-17 17:58 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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?
On Tue, Nov 17, 2020 at 11:10 PM Ron <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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PostgreSQL Tuning and running a query on a big data
@ 2020-11-17 19:19 Ron <ronljohnsonjr@gmail.com>
parent: Sachin Kumar <sachinkumaras@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Ron @ 2020-11-17 19:19 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PostgreSQL Tuning and running a query on a big data
@ 2020-11-17 21:03 Thomas Kellerer <shammat@gmx.net>
parent: Sachin Kumar <sachinkumaras@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Thomas Kellerer @ 2020-11-17 21:03 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org; +Cc: Sachin Kumar <sachinkumaras@gmail.com>
Sachin Kumar schrieb am 17.11.2020 um 18:34:
> 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";
The target table of an UPDATE shouldn't be repeated in the FROM clause in Postgres.
Not sure why you have the LIMIT in the sub-select, but if that is only for
testing purposes, then I think the following should do what you want, but much faster:
UPDATE hk_card_master_test m
SET "ACCOUNT_NUMBER" = v."v_account_number",
"ISSUANCE_NUMBER" = v."v_issuance_number",
"cron"=1
FROM vdaccount_card_bank v
WHERE SUBSTR(v."ACCOUNT_NUMBER", 1, 10) = m."CARD_SEQUENCE_NUMBER"
You probably want those indexes:
create index on vdaccount_card_bank ( (SUBSTR("ACCOUNT_NUMBER", 1, 10) );
create index on hk_card_master_test ("CARD_SEQUENCE_NUMBER");
Unrelated to your question, but using quoted/uppercase identifiers is generally discouraged in Postgres:
https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_upper_case_table_or_column_names
you probably will have a lot less trouble if you get rid of those.
Thomas
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2020-11-17 21:03 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-11-17 17:34 PostgreSQL Tuning and running a query on a big data Sachin Kumar <sachinkumaras@gmail.com>
2020-11-17 17:39 ` Ron <ronljohnsonjr@gmail.com>
2020-11-17 17:58 ` Sachin Kumar <sachinkumaras@gmail.com>
2020-11-17 19:19 ` Ron <ronljohnsonjr@gmail.com>
2020-11-17 21:03 ` Thomas Kellerer <shammat@gmx.net>
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