Hi,
One of the service layer app is inserting Millions of records in a table
but one row at a time. Although COPY is the fastest way to import a file in
a table. Application has a requirement of processing a row and inserting it
into a table. Is there any way this INSERT can be tuned by increasing
Hi Bruce,
Correct rows are wider. One of the columns is text and one is bytea.
Regards,
Aditya.
On Sat, Mar 5, 2022 at 12:08 AM Bruce Momjian wrote:
> On Sat, Mar 5, 2022 at 12:01:52AM +0530, aditya desai wrote:
> > Hi,
> > One of the service layer app is inserting Millions
Thanks all for your inputs. We will try to implement inserts in single
transaction. I feel that is the best approach.
Thanks,
AD.
On Saturday, March 5, 2022, Bruce Momjian wrote:
> On Fri, Mar 4, 2022 at 01:42:39PM -0500, Tom Lane wrote:
> > aditya desai writes:
> > >
ote:
> aditya desai writes:
> > One of the service layer app is inserting Millions of records in a table
> > but one row at a time. Although COPY is the fastest way to import a file
> in
> > a table. Application has a requirement of processing a row and inserting
> it
> >
Ok Will check. But from pgadmin it takes 1min and by psql it is taking 20
mins for 100,000 rows with BEGIN; COMMIT;
Thanks,
Aditya.
On Tue, Mar 8, 2022 at 8:23 PM Bruce Momjian wrote:
> On Tue, Mar 8, 2022 at 06:36:17PM +0530, aditya desai wrote:
> > Hi Tom,
> > I added BEGI
Hi,
1. Have you tried creating indexes on columns for which it is showing
sequential scans?
2. In my experience if the view is referring some other view inside it, it
is advisable to directly query on tables instead on child view.
3. This table 'so_vendor_address_base' definitely needs indexing to
Hi,
We are trying to COPY a few tables from Oracle to Postgres and getting the
following error. Data gets partially copied. Table does not have any huge
data; there are 4 numeric columns and 1 vahchar column. Could you please
help?
FATAL:canceling authentication due to timeout
Regards,
Aditya.
Hi,
One of our applications needs 3000 max_connections to the database.
Connection pooler like pgbouncer or pgpool is not certified within the
organization yet. So they are looking for setting up high configuration
Hardware with CPU and Memory. Can someone advise how much memory and CPU
they will n
Thanks! I will run these suggestions with App team.
On Fri, May 20, 2022 at 4:01 PM Laurenz Albe
wrote:
> On Fri, 2022-05-20 at 12:15 +0200, Andreas Kretschmer wrote:
> > On 20 May 2022 10:27:50 CEST, aditya desai wrote:
> > > One of our applications needs 3000 max_connectio
Hi,
I have one Oracle fdw table which is giving performance issue when joined
local temp table gives performance issue.
select * from oracle_fdw_table where transaction_id in ( select
transaction_id from temp_table) 54 seconds. Seeing HASH SEMI JOIN in
EXPLAIN PLAN. temp_table has only 74 re
.
On Mon, Jul 11, 2022 at 5:43 PM Justin Pryzby wrote:
> On Mon, Jul 11, 2022 at 05:38:34PM +0530, aditya desai wrote:
> > Hi,
> > I have one Oracle fdw table which is giving performance issue when joined
> > local temp table gives performance issue.
> >
> > sel
Understood thanks!! Will try to build dynamiq query to send ids
across instead of join.
On Mon, Jul 11, 2022 at 8:56 PM Laurenz Albe
wrote:
> On Mon, 2022-07-11 at 17:38 +0530, aditya desai wrote:
> > I have one Oracle fdw table which is giving performance issue when joined
> > l
Hi,
We are doing an oracle to postgres migration(5 TB+ data). We are encoding
and decoding BLOB data after migration and for that we are running updates
on tables having BLOB/CLOB data. When we execute this pg_wal is filling up.
Do you have any general guidelines for migrating a 5TB + database fro
Hi,
We have a Postgres 11.16 DB which is continuously connected to informatica
and data gets read from it continuously.
When we have to ALTER TABLE.. ADD COLUMN.. it gets blocked by the SELECTs
on the table mentioned by process above.
Is there any way to ALTER the table concurrently without gett
t_lock IN 1 .. 100 LOOP
> BEGIN
> ALTER TABLE mytable ;
> EXIT;
> END;
> END LOOP;
> END;
> $$;
>
>
>
> Tom Lane wrote on 1/19/2023 12:45 PM:
>
> aditya desai writes:
>
> We have a Postgres 11.16 DB which is continuously connected to inf
Hi,
Is there any way to improve performance of LIKE clause on VIEWS.
select * From request_vw where upper(status) like '%CAPTURED%' - 28 seconds.
select * from request_vw where status='CAPTURED'
Application team is reluctant to change queries from the Application side
to = instead of LIKE.
Als
Hi,
We are getting this error when transferring data using COPY command or
running workflow for huge data. We are using Password Authentication(LDAP)
"Connection forcibly closed remote server"
Can someone help how we can avoid this?
Regards,
Aditya.
Forgot to mention. The error comes intermittently. It is not consistent.
Observation is that it comes when a connection has to process a larger data
set.
On Wed, Feb 15, 2023 at 5:43 PM aditya desai wrote:
> Hi,
> We are getting this error when transferring data using COPY command or
>
,
Aditya.
On Thu, Feb 16, 2023 at 12:07 AM Jeff Janes wrote:
> On Wed, Feb 15, 2023 at 7:13 AM aditya desai wrote:
>
>> Hi,
>> We are getting this error when transferring data using COPY command or
>> running workflow for huge data. We are using Password Authentication(LDAP)
&g
Hi All,
Unfortunately I am unable to share a query plan or query.
I have a SQL which is getting called from a web service. At a certain point
where it inserts data in the table . Process is going in a hung state.
pg_stat_activity shows wait_even='IPC' , wait_even_type=MessageQueueSend.
In Webserv
Hi,
We are getting intermittent connection errors on Postgres 11.16, in
informatica as well as Python jobs that run queries on Postgres.
In informatica logs we see below error.
ODBC PostgreSQL Wire Protocol driver]SSL I/O Error.[DataDirect][ODBC lib]
Connection in use
While in Postgres logs we
Hi,
We have an application where one of the APIs calling queries(attached) is
spiking the CPU to 100% during load testing.
However, queries are making use of indexes(Bitmap Index and Bitmap Heap
scan though). When run separately on DB queries hardly take less than 200
ms. Is CPU spiking due to Bitm
Hi,
We have Amazon RDS Postgres. Currently we are using .pgpass file and
running psql from different EC2 instances to connect to DB. But the
password in this file is not encrypted. What are our options to encrypt the
password? Or do passwordless connection from EC2 to database? Lambda
functions hav
>
>
> Hi,
> We have an application where one of the APIs calling queries(attached) is
> spiking the CPU to 100% during load testing.
> However, queries are making use of indexes(Bitmap Index and Bitmap Heap
> scan though). When run separately on DB queries hardly take less than 200
> ms. Is CPU spi
mbda was 100 only.
> And adding connection pooler(RDS proxy) helped us to reduce the CPU load
> from 100% to 30%
>
> Happy to help :)
> Prince Pathria Systems Engineer | Certified Kubernetes Administrator |
> AWS Certified Solutions Architect Evive +91 9478670472 goevive.com
>
>
Hi,
We have AWS RDS and we are trying to connect to DB remotely from EC2
instance.as client connection using psql. We are trying to set up IAM
roles. We did all the necessary settings but got below error. Could you
please advise?
Password for user lmp_cloud_dev:
psql: FATAL: PAM authentication f
Also do we have to grant the role below to the user?
grant rds_iam to app_user;
If you have any document/Steps to set this up from scratch,could you please
forward? That would be really helpful.
Regards,
Aditya.
On Wed, Sep 30, 2020 at 4:47 PM Hannah Huang
wrote:
>
>
> On 30 Sep 2020,
Hi,
Below query always shows up on top in the CPU matrix. Also despite having
indexes it does sequential scans(probably because WHERE condition satisfies
almost all of the data from table). This query runs on the default landing
page in application and needs to fetch records in less that 100 ms wit
Mon, Oct 19, 2020 at 9:50 PM Michael Lewis wrote:
> Reply to the group, not just me please. Btw, when you do reply to the
> group, it is best practice on these lists to reply in-line and not just
> reply on top with all prior messages quoted.
>
> On Sun, Oct 18, 2020 at 3:23 AM adit
Hi Laurenz,
I created
On Fri, Oct 16, 2020 at 2:06 PM Laurenz Albe
wrote:
> On Thu, 2020-10-15 at 20:34 +0530, aditya desai wrote:
> > Below query always shows up on top in the CPU matrix. Also despite
> having indexes it does sequential scans
> > (probably because WHERE c
ine response. I tried all this on Dev env 2 vCPU and 8
GB RAM. Still waiting for the PST environment :( with better configuration.
>
> On Sun, Oct 18, 2020 at 3:23 AM aditya desai wrote:
>
>> I tried vacuum full and execution time came down to half.
>>
> Great to hear.
&
Hi,
Kindly requesting an update on this. Thanks.
-Aditya.
On Tue, Oct 20, 2020 at 6:26 PM aditya desai wrote:
>
>
> On Mon, Oct 19, 2020 at 9:50 PM Michael Lewis wrote:
>
>> Reply to the group, not just me please. Btw, when you do reply to the
>> group, it is best pr
Hi,
Kindly requesting for help on this. Thanks.
-Aditya.
On Tue, Oct 20, 2020 at 6:00 PM aditya desai wrote:
> Hi Laurenz,
> I created
>
> On Fri, Oct 16, 2020 at 2:06 PM Laurenz Albe
> wrote:
>
>> On Thu, 2020-10-15 at 20:34 +0530, aditya desai wrote:
>> &g
Hi David,
Thanks for the suggestion. Let me try to implement this as well. WIll get
back to you soon.
Regards,
Aditya.
On Thu, Oct 22, 2020 at 11:03 AM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Wed, Oct 21, 2020 at 10:22 PM aditya desai wrote:
>
>> As per ap
abase.
>
> Yeah..
>
> That result is quite hard to see, but..
>
> > On Fri, Dec 4, 2020 at 11:43 AM aditya desai wrote:
> >
> > > Hi Postgres Experts,
> > > Requesting for advice on below. I am new to postgres :(
> > >
> > > Regards,
&g
020 at 1:47 PM Justin Pryzby wrote:
> On Fri, Dec 04, 2020 at 01:31:14PM +0530, aditya desai wrote:
> > Hi Justin,
> > Many thanks for your response. Please see my response below.
> >
> > What do you mean by API ? If it's a different client, how does it
> connect ?
&g
Hi,
We migrated our Oracle Databases to PostgreSQL. One of the simple select
query that takes 4 ms on Oracle is taking around 200 ms on PostgreSQL.
Could you please advise. Please find query and query plans below. Gather
cost seems high. Will increasing max_parallel_worker_per_gather help?
explain
Hi Michael,
Thanks for your response.
Is this table partitioned? - No
How long ago was migration done? - 27th March 2021
Has vacuum freeze and analyze of tables been done? - We ran vacuum analyze.
Was index created after populating data or reindexed after perhaps? -
Index was created after data lo
Hi Justin,
Yes, force_parallel_mode is on. Should we set it off?
Regards,
Aditya.
On Sat, Apr 3, 2021 at 7:46 PM Justin Pryzby wrote:
> On Sat, Apr 03, 2021 at 04:08:01PM +0200, Pavel Stehule wrote:
> > so 3. 4. 2021 v 15:38 odesílatel aditya desai
> napsal:
> > >
Thanks Bruce!! Will set it off and retry.
On Sat, Apr 3, 2021 at 8:42 PM Bruce Momjian wrote:
> On Sat, Apr 3, 2021 at 08:38:18PM +0530, aditya desai wrote:
> > Hi Justin,
> > Yes, force_parallel_mode is on. Should we set it off?
>
> Yes. I bet someone set it wit
ation file
work_mem | 16MB
| configuration file
On Sat, Apr 3, 2021 at 8:59 PM aditya desai wrote:
> Hi Bruce,
> Please find the below output.force_parallel_mode if off now.
>
> aad_log_min_messages | warning
>
I will gather all information and get back to you
On Sat, Apr 3, 2021 at 9:00 PM Pavel Stehule
wrote:
>
>
> so 3. 4. 2021 v 17:15 odesílatel aditya desai napsal:
>
>> Hi Pavel,
>> Thanks for response. Please see below.
>> work_mem=16MB
>> maintenance_work_m
Thanks Justin. Will review all parameters and get back to you.
On Sat, Apr 3, 2021 at 9:11 PM Justin Pryzby wrote:
> On Sat, Apr 03, 2021 at 11:39:19AM -0400, Tom Lane wrote:
> > Bruce Momjian writes:
> > > On Sat, Apr 3, 2021 at 08:38:18PM +0530, aditya desai
Hi Justin/Bruce/Pavel,
Thanks for your inputs. After setting force_parallel_mode=off Execution
time of same query was reduced to 1ms from 200 ms. Worked like a charm. We
also increased work_mem to 80=MB. Thanks again.
Regards,
Aditya.
On Sat, Apr 3, 2021 at 9:14 PM aditya desai wrote:
> Tha
Yes. I have made suggestions on connection pooling as well. Currently it is
being done from Application side.
On Sat, Apr 3, 2021 at 11:12 PM Pavel Stehule
wrote:
>
>
> so 3. 4. 2021 v 19:37 odesílatel aditya desai napsal:
>
>> Hi Justin/Bruce/Pavel,
>> Thanks for yo
Hi,
We have few select queries during which we see SHARED LOCKS and EXCLUSIVE
LOCKS on tables. Can these locks cause slowness? Is there any way to reduce
the locks?
What must be causing ACCESS EXCLUSIVE LOCKS when the application is running
select queries? Is it AUTOVACUUM?
Regards,
Aditya.
Noted thanks!!
On Sun, Apr 4, 2021 at 4:19 PM Pavel Stehule
wrote:
>
>
> ne 4. 4. 2021 v 12:39 odesílatel aditya desai napsal:
>
>> Hi Pavel,
>> Notes thanks. We have 64 core cpu and 320 GB RAM.
>>
>
> ok - this is probably good for max thousand connec
Thanks Amine and Justin. I will check and try this.
Regards,
Aditya.
On Sun, Apr 4, 2021 at 10:49 PM Justin Pryzby wrote:
> On Sun, Apr 04, 2021 at 04:12:14PM +0530, aditya desai wrote:
> > Hi,
> > We have few select queries during which we see SHARED LOCKS and EXCLUSIVE
>
Hi,
We have to access data from one schema to another. We have created a view
for this but performance is not good. We tried materialized views as well
but Refresh MV is creating problem as it puts and access exclusive locks.
Is there any other way to achieve this?
Regards,
Aditya.
Hi,
Below query takes 12 seconds. We have an index on postcode.
select count(*) from table where postcode >= '00420' AND postcode <= '00500'
index:
CREATE INDEX Table_i1
ON table USING btree
((postcode::numeric));
Table has 180,000 rows and the count is 150,000. Expectation is to run
Thanks will check.
On Tue, Apr 6, 2021 at 4:11 PM hubert depesz lubaczewski
wrote:
> On Tue, Apr 06, 2021 at 01:22:31PM +0530, aditya desai wrote:
> > Hi,
> > We have to access data from one schema to another. We have created a
> view for this but performance is
Thanks Tom. Will try with numeric. Please ignore table and index naming.
On Tue, Apr 6, 2021 at 6:55 PM Tom Lane wrote:
> aditya desai writes:
> > Below query takes 12 seconds. We have an index on postcode.
>
> > select count(*) from table where postcode >= '00420
Thanks to all of you. Removed casting to numeric from Index. Performance
improved from 12 sec to 500 ms. Rocket!!!
On Tue, Apr 6, 2021 at 9:14 PM Andrew Dunstan wrote:
>
> On 4/6/21 9:30 AM, aditya desai wrote:
> > Thanks Tom. Will try with numeric. Please ignore table and
Hi,
I need to combine results of multiple rows in one row. I get below error.
Could you please help.
Query:
select string_agg((select '**' || P.PhaseName || ' - ' ||
R.Recommendation AS "ABC" from tblAssessmentRecommendation
R,tblAssessmentPhases P
where R.PhaseID = P.PhaseID Order BY P.sor
Thanks Patrick. I used WITH Query and feeded that output to string_aggr
which worked. However it is giving performance issues. Will check on that.
THanks.
On Thu, Apr 8, 2021 at 5:11 PM Patrick FICHE
wrote:
> *From:* aditya desai
> *Sent:* Thursday, April 8, 2021 1:32 PM
> *T
low, you’re getting a basic subquery error – research how to fix that.
> Mike
>
>
>
> *From:* aditya desai
> *Sent:* Thursday, April 08, 2021 4:32 AM
> *To:* Pgsql Performance
> *Subject:* str_aggr function not wokring
>
>
>
> Hi,
>
> I need to combine
Hi Mustafa,
You can look into the SQLine tool. We recently used to migrate MSSQL to
PostgreSQL. For procedures/functions etc you need to have good amount of
understanding of TSQL and PL-PGSQL. SQLine will convert 60-80% of you TSQL
code. Some manual effort is required at the end.
Regards,
Aditya.
Hi,
One of the procs which accept tabletype as parameter gives below error
while being called from Application. Could not find a concrete solution for
this. Can someone help?
call PROCEDURE ABC (p_optiontable optiontype)
Below is the error while executing proc -
“the clr type system.data.datat
Pryzby wrote:
> On Thu, Apr 29, 2021 at 02:52:23PM +0530, aditya desai wrote:
> > Hi,
> > One of the procs which accept tabletype as parameter gives below error
> > while being called from Application. Could not find a concrete solution
> for
> > this. Can someone he
aditya desai
6:32 PM (19 minutes ago)
to Justin, Pgsql
Hi Justin,
Thanks for your response. We have a user defined type created as below and
we need to pass this user defined parameter to a procedure from .net code.
Basically the procedure needs to accept multiple rows as parameters(user
Hi All,
What is the difference between pg_triggers and information_schema.triggers?
I want to list all triggers in the database.
The count differs in both.
select count(1) from information_schema.triggers -55
select count(1) from pg_trigger - 48
What is the best way to list all objects in Postg
August 11, 2021, aditya desai wrote:
>
>> Hi All,
>> What is the difference between pg_triggers and
>> information_schema.triggers? I want to list all triggers in the database.
>>
>
> Read the docs for information_schema.triggers.
>
>
>> What is the
Hi,
We are migrating Oracle to PostgreSQL. We need the equivalent of UTL_HTTP.
How to invoke Web service from PostgreSQL.
Also please let me know the PostgreSQL equivalents of below
Oracle utilities..
utl.logger,UTL_FILE,UTL_SMTP
Regards,
Aditya.
Hi,
Query on one of our partitioned tables which is range partitioned on
"run"date" column is going to all partitions despite having run_date in
WHERE clause. "enable_parition_pruning" is also on. I am unable to generate
a query plan as the query never runs fully even waiting for say half an
hour.
Will try to get a query in text format. It looks difficult though.
Regards,
Aditya.
On Fri, Oct 1, 2021 at 4:03 PM hubert depesz lubaczewski
wrote:
> On Fri, Oct 01, 2021 at 02:24:11PM +0530, aditya desai wrote:
> > Hi Laurenz,
> > Please find attached explain query plan and
Sending to a performance group instead of PLPGSQL.
.
.
Hi,
I am running the below query. Table has 21 million records. I get an Out Of
Memory error after a while.(from both pgadmin and psql). Can someone review
DB parameters given below.
select t.*,g.column,a.column from
gk_staging g, transaction
that gets referred in a
procedure.Transaction table is partitioned table but due to business
requirements partition key is not part of where clause.
Regards,
Aditya.
On Tuesday, October 19, 2021, Justin Pryzby wrote:
> On Tue, Oct 19, 2021 at 11:28:46AM +0530, aditya desai wrote:
> > I a
Thanks Michael. I will check this further.
On Tue, Oct 19, 2021 at 7:09 PM Michael Lewis wrote:
> Check explain plan, change work mem to 100MBs and then check explain plan
> again. If it changed, then try explain analyze.
>
> Work mem is limit is used per node in the plan, so especially with
> p
Hi,
In a trigger function I am creating a temp table . When an update on a
table is executed for say 10k rows. I get the below error.
ERROR: out of shared memory
HINT:You might need to increase max_locks_per_transaction
CONTEXT: SQL Statement "created temp table changedinfo(colName
varchar(100), o
info);
perform insert_info(v_message);
raise notice '%',v_message;
END;
$$ LANGUAGE plpgsql;
Regards,
AD.
On Wed, Nov 24, 2021 at 11:22 AM Tom Lane wrote:
> aditya desai writes:
> > In a trigger function I am creating a temp table . When an update on a
>
Ok. Let me try this. Thanks!!
On Wed, Nov 24, 2021 at 12:01 PM Thomas Kellerer wrote:
> aditya desai schrieb am 24.11.2021 um 07:25:
> > Thanks Tom. However I could not find any solution to achieve the given
> requirement. I have to take all values in the temp table and assign it t
H Michael,
Please see insert_info function below. Also r_log_message is composite data
type and it's definition is also given below.
CREATE OR REPLACE FUNCTION insert_info(
info_array r_log_message[]
) RETURNS varchar AS $$
DECLARE
info_element r_log_message;
BEGIN
FO
Kellerer wrote:
> aditya desai schrieb am 24.11.2021 um 07:25:
> > Thanks Tom. However I could not find any solution to achieve the given
> requirement. I have to take all values in the temp table and assign it to
> an array variable to pass it to the audit procedure as shown
Thanks Thomas! Sorry to say this but ,this was migrated from Oracle to PG
:) and the app team just wants to keep the data type as it is :(
On Wed, Nov 24, 2021 at 5:40 PM Thomas Kellerer wrote:
> aditya desai schrieb am 24.11.2021 um 08:35:
> > Hi Thomas,
> > v_message is of
74 matches
Mail list logo
