Re: CPU hogged by concurrent SELECT..FOR UPDATE SKIP LOCKED

Hi all, especially Jim Jarvie, I saw your email to me only now on my related issue. My issue remains this one: Well this got interesting  - the already moved error showed up: and I have already gone through all those index pruning and all that good stuff. I remain with my original question f

Re: LIMIT OFFSET with DB view vs plain SQL

+ pgsql-performance On Thu, Mar 28, 2019 at 6:41 PM Raj Gandhi wrote: > Hi everyone, > > > > I’m using LIMIT offset with DB view. Looks like query planner is applying > the LIMIT for DB view at the end after processing all rows. > > When running same SQL that was us

Re: LIMIT OFFSET with DB view vs plain SQL

8, 2019 at 5:44 PM Raj Gandhi wrote: > > > > + pgsql-performance > > > > On Thu, Mar 28, 2019 at 6:41 PM Raj Gandhi > wrote: > >> > >> Hi everyone, > >> > >> > >> > >> I’m using LIMIT offset with DB view. Looks like que

Re: LIMIT OFFSET with DB view vs plain SQL

Any other idea how to resolve the performance issue with the database view? On Fri, Mar 29, 2019 at 7:38 PM Raj Gandhi wrote: > Merlin, I tried the hack you suggested but that didn't work. Planner used > the same path. > > The same query works much faster when using the raw S

Re: LIMIT OFFSET with DB view vs plain SQL

response_body text > ) > as $$ > > > Query example: > > select * from foo(100, 50); > > > On Apr 1, 2019, at 9:56 AM, Raj Gandhi wrote: > > Any other idea how to resolve the performance issue with the database view? > > On Fri, Mar 29, 2019 at 7:38 PM Ra

Re: Slow query on V12.

Hi, Can you check by vacuum analyze the database. And run the query. **Remember don't use Vacuum full. On Tue, 24 Sep 2019, 12:07 am Luís Roberto Weck, < luisrobe...@siscobra.com.br> wrote: > Hi! > > Recently I've been looking for bloat in my databases and found a query to > show which table

Query went slow all of sudden. ON V 11.3

Hi All, All of sudden the query went slow before the query was executing in 30- 35 sec now even after 30 mins i am not getting any result. later I have dropped a table ( t_meners) and recreated it and again it started working very fast. is there way to find what happen on that why is not any is

Re: Query went slow all of sudden. ON V 11.3

Hi Justin, Its been executing for 35 + mins due to statement time out its getting canceled. Yes temp_10032019020721_4470table index is there on cr column. On Fri, Oct 4, 2019 at 6:50 PM Justin Pryzby wrote: > On Fri, Oct 04, 2019 at 03:52:26PM +0530, nikhil raj wrote: > &g

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

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

Performance issue

27;Y'::bpchar) AND (has_last_name = 'Y'::bpchar) AND (has_first_name = 'Y'::bpchar))" "Rows Removed by Filter: 7709177" " -> Index Scan using pk_entity_proxy_id on test2 table1 (cost=0.56..1867677.94 rows=40071417 width=8) (actual time=0.008..363534.437 rows=40077727 loops=1)" "Filter: ((tax_bnkrpt_dcsd_ind = 'N'::bpchar) AND (govt_prison_ind = 'N'::bpchar) AND ((cstmr_prspct_ind)::text = 'Prospect'::text))" "Rows Removed by Filter: 94756" "Planning time: 0.400 ms" "Execution time: 442688.523 ms" Server config: PostgreSQL v10.11 RAM: 380GB vCore: 32 Shared_buffers: 65GB work_mem:104857kB maintenance_work_mem:256MB effective_cache_size: 160GB https://dba.stackexchange.com/questions/269138/postgresql-server-running-very-slow-at-minimal-work-load Thanks, Raj

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."

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 +, 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

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

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

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: 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

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

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

| 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

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: 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

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

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 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

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 |

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;

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

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: 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 +, Nagara

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: 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

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"

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"

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"

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"

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,

issue partition scan

x27;) ); If I use AND instead of OR, it's doing partition & index scan; otherwise, it's a full scan. Can you please provide suggestions? For DDL structure Postgres 11 | db<>fiddle  | | | | Postgres 11 | db<>fiddle Free online SQL environment for experimenting and sharing. | | | Thanks,Raj

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

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

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

te table t4 partition of t for values from ('2020-03-01') to ('2020-04-01');alter table t4  add constraint uk3 unique  (id,pid);create table t3 partition of t for values from ('2020-04-01') to ('2020-05-01');alter table t3  add constraint uk4 unique  (id,p

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

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

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: 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

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