Re: Need optimization in query

Hi Team, Does anybody tried to have a look at it and would like to suggest any optimisations? Thanks On Sat, Jun 11, 2022 at 12:48 AM Shubham Mittal wrote: > Hi Team. > > *I have a use case to get the result as follows:* > > 1. asin_ymm is never null. > 2. If there is more t

Need optimization in query

Hi Team. *I have a use case to get the result as follows:* 1. asin_ymm is never null. 2. If there is more than 1 entry for an asin_ymm with both null and non-null submodelId, I should return rows with non-null submodelId only, otherwise if there is no submodelid present for a asin_ymm, then retur

PGBouncer logs explanation required

Hi Team, I get below logs in pgbouncer.. when I configure query timeout in spring boot application as below: SessionImplementor session = entityManager.unwrap(SessionImplementor.class); Connection connection = session.connection(); connection.createStatement().execute("SET LOCAL statement_timeout

Query Timeout not working using jpa

Hi Team, Can anyone help in setting the query timeout in postgres 12 using spring data jpa?? Currently using below code : query.setHint("javax.persistence.query.timeout", 1); Thanks & Regards, Shubham

Query time related to limit clause

Hi Team, *I have shared execution times of two queries below:* *I need to find only the first row matching the criteria , but limit 1 is taking more time than limit 15 or more.. If any one can tell an explanation for this and how I can achieve the same in less time.* explain analyze SELECT * fr

Re: Query takes around 15 to 20 min over 20Lakh rows

Hi Tom/David Could you please help me getting started to optimise this query?? Thanks & Regards Shubham mittal On Tue, Sep 7, 2021, 8:57 PM Michael Lewis wrote: > Have you ever used this site to visualize the explain plan and spot bad > estimates and slow nodes? https://explain.de

Re: Query takes around 15 to 20 min over 20Lakh rows

20 Lakh is the current no of rows in the task table.. on which the query is executed.. On Mon, Sep 6, 2021, 11:44 PM Matthias Apitz wrote: > > What does the term 'over 20Lakh rows' mean? Thanks > > matthias > -- > Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ > +49-176-3890

Query takes around 15 to 20 min over 20Lakh rows

Hi , *Please help in optimizing this query. I need to actually generate reports daily using this query.. It takes almost 15 to 20 min to execute this query due to joins.. * *Here common_details is a jsonB column.* SELECT T.order_id, ( ( T.common_details -> 'commonDetails' :: text ) -> 'bun

Re: Use case stuck due to Partitioning

Hi Michael, Yes I have a btree index on the *modified_date* column currently. SELECT * FROM partitioned_table where A ='Value' AND created_date >= '2021-03-01 08:16:13.589' and created_date <= '2021-04-02 08:16:13.589' ORDER BY viewpriority desc OFFSET 0 ROWS FETCH NEXT 200 ROWS ONLY; Here

Use case stuck due to Partitioning

Hi All, Please find my use case below and suggest the appropriate solution: We have done declarative partitioning over our table A based on some key and further range sub partitioned each partition on the basis of createddate range of 90 days. In the search query to display last 90 day data , I

How to generate file from postgres data

Hi Team, I want to execute some queries on postgres and generate the reports containing the resultset on client side for user in the most efficient way. The data can be of 1 gb also. Could you please help. Thanks and regards, Shubham

Need help on query optimization

Hi Team, I am trying to execute the below query and getting below explain plan in postgres . Please provide any inputs on the same , on how to optimize the same. Here B is a ltree column, E is a jsonb column. EXPLAIN ANALYZE SELECT * FROM A where ( B <@ 'INDIA' ) AND C = 'D' AND CAST ( E->'F'