Re: pg_restore schema dump to schema with different name

AM PDT, David G. Johnston wrote: On Mon, Aug 23, 2021 at 2:46 AM Nagaraj Raj wrote: Currently this is not something can do. this functionality is there in oracle.  Is this future considering to add?  (it would really help for create any test schemas without disturbing current schema

Re: pg_restore schema dump to schema with different name

Wouldn’t be easy if we have option to_schema ?  Absolutely, I should not alter current schema, as it live 24/7. Thanks,RjOn Monday, August 23, 2021, 06:39:03 AM PDT, Jean-Christophe Boggio wrote: > The only way to do that is to create a new database, import the data > there, rename th

pg_restore schema dump to schema with different name

Hi, I know I can alter schema name after restoring but the problem is the name already exist and I don't want to touch that existing schema.The dump type is "custom". So effectively I want something like.pg_dump -U postgres --schema "source_schema" --format "c" --create --file "source_schema.b

Re: Partition column should be part of PK

stgres_11&fiddle=bcfdfc26685ffb498bf82e6d50da95e3 Please suggest. Thanks,Rj On Thursday, July 8, 2021, 08:52:35 PM PDT, Christophe Pettus wrote: > On Jul 8, 2021, at 20:32, Nagaraj Raj wrote: > > My apologies for making confusion with new thread. Yes its same issue rel

Re: Partition column should be part of PK

, Mike Sofen wrote: I believe this thread qualifies for the funniest thread of 2021 (so far).  And yes, this is a top post.  :-) Mike Sofen -Original Message- From: Alvaro Herrera Sent: Thursday, July 08, 2021 3:29 PM To: Justin Pryzby Cc: Nagaraj Raj ; pgsql-performa...@postgresql.org

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

Hello everyone, I have a scenario where wanted to add PK on partition to make sure to monitor unique values for two columns values. but as PG required to partition column should be part of PK. How can we make sure actual two columns need to be unique values. and also while insert into table need

Partition column should be part of PK

we have some partitioned tables with inherence and planning to migrate them to the declaration. Table DDL: CREATE TABLE c_account_p (     billing_account_guid character varying(40)  NOT NULL,     ingestion_process_id bigint NOT NULL DEFAULT '-1'::integer,     load_dttm timestamp(6) withou

Re: issue partition scan

n on the above two conditions(1 and 2) included in the where clause ?  Both sets are pointing to different tables. On Tuesday, May 25, 2021, 04:01:53 PM PDT, Christophe Pettus wrote: > On May 25, 2021, at 15:50, Nagaraj Raj wrote: > > SELECT  >        t2.cid_hash AS BI

issue partition scan

I have a table 'sub_soc' with 3BIL records, it's been partitioned and indexed on the soc column. when the user is running a query with left join on this table and joining some other tables, the query planner doing a full table scan instead of looking into partitioned tables and index scan.  SEL

Re: Partition with check constraint with "like"

sorry, forgot to attach the test cases.Postgres 13 | db<>fiddle | | | | Postgres 13 | db<>fiddle Free online SQL environment for experimenting and sharing. | | | On Friday, May 21, 2021, 03:59:18 PM PDT, Nagaraj Raj wrote: Hi,  This is also not working,

Re: Partition with check constraint with "like"

ct: create table mytable_z of mytable  for values from ('Z') to ('Z[');   It should be create table mytable_z of mytable  for values from ('Z') to ('[')   Michel SALAIS   De : Nagaraj Raj Envoyé : vendredi 21 mai 2021 18:39 À : David Rowley Cc :

Re: Partition with check constraint with "like"

h 100KB size per row. that would be around 200GB. also, queries may benefit from trigram matching.Index Columns for `LIKE` in PostgreSQL | Niall Burkley's Developer Blog   On Fri, 21 May 2021 at 22:08, Nagaraj Raj wrote: Hi David, Hi, I am trying to create partitions on the table w

Re: Partition with check constraint with "like"

ntains (name) = (ZAR83NB).SQL state: 23514 On Friday, May 21, 2021, 01:24:13 AM PDT, David Rowley wrote: On Fri, 21 May 2021 at 19:02, Nagaraj Raj wrote: > then what would be the range of Z > FROM (Z) to (?) ; postgres=# select chr(ascii('z')+1) ; chr - { (1 row)

Re: Partition with check constraint with "like"

ay 21, 2021 at 02:36:14AM +, Nagaraj Raj wrote: >  Thank you. This is a great help.  > But "a" have some records with alpha and numeric.  So then you should make one or more partitions FROM ('1')TO('9'). > example : > insert into mytable values(&#x

Re: Partition with check constraint with "like"

Rowley wrote: On Fri, 21 May 2021 at 12:32, Nagaraj Raj wrote: > I am trying to create partitions on the table based on first letter of the > column record  value using inherit relation & check constraint. You'll get much better performance out of native partitioning than

Partition with check constraint with "like"

Hi, I am trying to create partitions on the table based on first letter of the column record  value using inherit relation & check constraint. Somehow able to create and load the data into the tables as per my requirement. But the problem is when querying the data on that partitioned column, it'

Re: INSERTS waiting with wait_event is "transactionid"

Hi Laurenz, Thanks for the response.  Yeah understand that, but I'm trying to figure out why it is taking too long. there is foreign key relation to this table.  Thanks,RjOn Friday, April 9, 2021, 02:16:08 AM PDT, Laurenz Albe wrote: On Thu, 2021-04-08 at 20:14 +0000, Nagara

INSERTS waiting with wait_event is "transactionid"

Hi, We are trying to load data around 1Bil records into one table with INSERT statements (not able to use COPY command) and they are been waiting for a lock and the wait_event is "transactionid", I didn't find any information in the documents. Queries have been waiting for hours. Table DDL'sCREA

Re: Users grants with setting options

Thank you for confirmation.  On Monday, March 8, 2021, 03:46:28 PM PST, David G. Johnston wrote: On Mon, Mar 8, 2021 at 4:30 PM Nagaraj Raj wrote: I have a quick question, does role custom parameters settings will be granted to users well?  Parameters are not inherited - the role

Users grants with setting options

Hi, I have a quick question, does role custom parameters settings will be granted to users well?  Does user c_role will have the same settings m_role.CREATE ROLE m_role ;CREATE ROLE c_role ;ALTER ROLE m_role  SET configuration_parameter TO 'VALUE';GRANT m_role TO c_role;

tables meta data collection

Hi Everyone, I was trying to collect table metadata with a description; the use case is that I need to show all columns of the tables whether it has the description or not.  I tried the below query, but it only gives column details that have a description and ignore others if not.  Postgres 11 |

Query performance issue

Hi, I have a query performance issue, it takes a long time, and not even getting explain analyze the output. this query joining on 3 tables which have around a - 176223509 b - 286887780 c - 214219514 explainselect  Count(a."individual_entity_proxy_id")from "prospect" ainner join "individual_d

Temporarily disable not null constraints

Hi, Can we disable not null constraints temporarily in the session-based transaction, like we disable FK constraints?  SETsession_replication_role = ‘replica’; alter table table_name disable trigger user;” above two options are working for unique constraints violation exception.  Thanks,Rj

Query performance

Hi, I have long running query which running for long time and its planner always performing sequnce scan the table2.My gole is to reduce Read IO on the disk cause, this query runns more oftenly ( using this in funtion for ETL).  table1: transfer_order_header(records 2782678)table2: transfer_orde

Re: Single column vs composite partial index

;"                          Buffers: shared hit=218""Planning Time: 0.374 ms""Execution Time: 2.661 ms" >The actual performance might change based on thing like maintenance like >reindex, cluster, vacuum, hardware, and DB state (like cached blocks). Note: Stats a

Single column vs composite partial index

Hi, I'm running one query, and I created two types of index one is composite and the other one with single column one and query planner showing almost the same cost for both index bitmap scan, I'm not sure which is appropriate to keep in production tables. explain analyze SELECT BAN, SUBSCRIBER

Re: Query performance issue

| explain.depesz.com | | | Thanks,Rj On Saturday, September 5, 2020, 06:42:31 AM PDT, Michael Lewis wrote: On Fri, Sep 4, 2020, 4:20 PM Nagaraj Raj wrote: Hi Mechel, I added the index as you suggested and the planner going through the bitmap  index scan,heap and the new planner is,HaOx

Re: Query performance issue

Hi Mechel, I added the index as you suggested and the planner going through the bitmap  index scan,heap and the new planner is,HaOx | explain.depesz.com | | | | HaOx | explain.depesz.com | | | Mem config:  Aurora PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3,

Re: Query performance issue

Sorry, I have attached the wrong query planner, which executed in lower environment which has fewer resources: Updated one,eVFiF | explain.depesz.com | | | | eVFiF | explain.depesz.com | | | Thanks,RjOn Friday, September 4, 2020, 02:39:57 PM PDT, Michael Lewis wrote: CR

Re: Query performance issue

query planner:SPJe | explain.depesz.com | | | | SPJe | explain.depesz.com | | | On Friday, September 4, 2020, 02:19:06 PM PDT, Nagaraj Raj wrote: I have a query which will more often run on DB and very slow and it is doing 'seqscan'. I was trying to opti

Query performance issue

I have a query which will more often run on DB and very slow and it is doing 'seqscan'. I was trying to optimize it by adding indexes in different ways but nothing helps. Any suggestions? Query: EXPALIN ANALYZE select serial_no,receivingplant,sku,r3_eventtime from (select serial_no,receivingpl

Re: simple query running for ever

And here is the explain analyze: https://explain.depesz.com/s/uQGA Thanks! On Tuesday, June 16, 2020, 02:13:37 PM PDT, Nagaraj Raj wrote: Hi Michael, Sorry, I missed table structure, explain select T0."physical_address_sid", T0."individual_entity

Re: simple query running for ever

character varying(40) COLLATE pg_catalog."default", auto_insrnc_byr_propnsty_code character varying(40) COLLATE pg_catalog."default", load_dttm timestamp without time zone NOT NULL, updt_dttm timestamp without time zone, md5_chk_sum character varying(200) COLLATE pg_catalog.&quo

Re: simple query running for ever

s, Rj On Tuesday, June 16, 2020, 01:47:21 PM PDT, Justin Pryzby wrote: On Tue, Jun 16, 2020 at 08:35:31PM +0000, Nagaraj Raj wrote: > I wrote a simple query, and it is taking too long, not sure what is wrong in > it, even its not giving EXPLAIN ANALYZE. Is this related to last week

simple query running for ever

I wrote a simple query, and it is taking too long, not sure what is wrong in it, even its not giving EXPLAIN ANALYZE. select T0."physical_address_sid", T0."individual_entity_proxy_id", T2."infrrd_hh_rank_nbr" from "cms_prospects".PROSPECT T0 --inner join "sas_prs_tmp".DEDUPE3583E3F18 T1 on T0."

Performance issue

Hello My PostgreSQL server 10.11 running on windows which are running very slow. DB has two tables with ~200Mil records in each. user queries are very slow even explain analyze also taking a longer. Could you please help me to tune this query and any suggestions to improve system performanc

Re: DB running out of memory issues after upgrade

Hi Merlin, Its configured high value for max_conn, but active and idle session have never crossed the count 50. DB Size: 20 GBTable size: 30MBRAM: 16GBvC: 4 yes, its view earlier I posted and here is there query planner for new actual view, "Append  (cost=0.00..47979735.57 rows=3194327000 width

Re: DB running out of memory issues after upgrade

288)""              Filter: (((data -> 'info'::text) ->> 'status'::text) = 'CLOSE'::text)" Thanks, On Tuesday, February 18, 2020, 09:59:37 AM PST, Tomas Vondra wrote: On Tue, Feb 18, 2020 at 05:46:28PM +, Nagaraj Raj wrote: >af