Re: Help me in reducing the CPU cost for the high cost query below, as it is hitting production seriously!!

Hi Justin, Please find the below explain plan link. Link: https://explain.depesz.com/s/owE Any help is appreciated. Thanks in Advance. Regards, Pavan -- Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html

Re: Help me in reducing the CPU cost for the high cost query below, as it is hitting production seriously!!

Hi Justin, >How big is the table ? And curract_state_isfinal_app_idx ? >Have these been reindexed (or pg_repacked) recently? The size of the table 'tms_workflow_history' is 7600Kb(which is pretty small). Yes those indexes were dropped and recreated. >It looks like you resolved the bad estima

Re: Help me in reducing the CPU cost for the high cost query below, as it is hitting production seriously!!

Hi Matthew, Yeah and you said right!. I have analyzed the entire database and also created appropriate indexes for the columns used in WHERE/JOIN clauses. Okay I will just provide the fourth union part of the query which you can analyze easier(this not that big). Please find the query part. And

Re: Help me in reducing the CPU cost for the high cost query below, as it is hitting production seriously!!

Hi all/Justin, As said, created index on the res_users.res_employee_id and the below link is the explain plan result. Link: https://explain.depesz.com/s/hoct . And the cost of Previous query is 92,129 and the cost of current modified query after creating the above said index is 91,462. But go

Re: Help me in reducing the CPU cost for the high cost query below, as it is hitting production seriously!!

Hi Justin, Please find the output of explain(analyze,buffers) for the whole query in the below link. Link: https://explain.depesz.com/s/dNkb Thanks in Advance! Regards, Pavan -- Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050

Re: Help me in reducing the CPU cost for the high cost query below, as it is hitting production seriously!!

Hi all, Thank you so much for your valuable responses.Tried every aspect which you have said for my sub-query. I hoped a better decrease in cost for my main query. But yes it decreased but not to a great extent. What I felt is to provide the main query and the associated table definitions in the

Re: Help me in reducing the CPU cost for the high cost query below, as it is hitting production seriously!!

Hi all, Thank you so much for your valuable responses.Tried every aspect which you have said for my sub-query. I hoped a better decrease in cost for my main query. But yes it decreased but not to a great extent. What I felt is to provide the main query and the associated table definitions in the

Re: Help me in reducing the CPU cost for the high cost query below, as it is hitting production seriously!!

Hi David, Thank you so much for your valuable inputs. Is there anything that I need to look from Indexes perspective or Join order ?? Kindly let me know if it can be tuned further. Thank you very much. Regards, Pavan -- Sent from: http://www.postgresql-archive.org/PostgreSQL-performanc

Re: Help me in reducing the CPU cost for the high cost query below, as it is hitting production seriously!!

Hi abbas, Thank you so much. I've got this query from my development team asking to improve its performance. Now I got pretty much clear idea of it. And it will be the final extent to which we can tune the performance right? If there is still a way give me some tips to enhance the query perfor

Re: Help me in reducing the CPU cost for the high cost query below, as it is hitting production seriously!!

Hi Abbas, Thanks for your valuable suggestions. To my surprise I got the same output as what I have executed before. But unfortunately I'm unable to understand the logic of the code, in specific what is base 60 number? The used data type for "effort_hours" column is 'double precision'. Kindly

Re: Help me in reducing the CPU cost for the high cost query below, as it is hitting production seriously!!

Hi mlunon, A great thanks for your timely response. And yes it worked when I rewritten the query. The query got enhanced with approximate of 1000 planner seeks. You can find it from the explain plan below: amp_test=# explain select sum ( CASE MOD(cast(effort_hours as decimal),1) WHEN 0.4

Help me in reducing the CPU cost for the high cost query below, as it is hitting production seriously!!

Hi all, Hope my mail finds you in good time. I had a problem with a query which is hitting the production seriously. The below is the sub part of the query for which I cannot reduce the CPU cost. Please check and verify whether I'm doing wrong or whether that type index type suits it or not. K

Re: 8.2 Autovacuum BUG ?

Hi all, Regarding this archive log generation found one observation. A table named abc_table id found to be archived every 9'th and 39'th minute. We are able to find number of tuples deleted from the pg_stat_user_tables view. But to my wonder the number of tuple inserts are shown 0. How can

Re: 8.2 Autovacuum BUG ?

Hello all, Will a sudden restart(stop/start) of a postgres database will generate this huge WAL? Regards, Pavan -- Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html

Re: 8.2 Autovacuum BUG ?

Hi Álvaro Herrera, Please find the corresponding output: *1).select name, setting, source, sourcefile, sourceline from pg_settings where name like '%vacuum%'; * -[ RECORD 1 ] name | autovacuum setting| on source | configuration file sourcefile

Re: 8.2 Autovacuum BUG ?

Hello all, One more interesting observation made by me. I have ran the below query(s) on production: SELECT relname, age(relfrozenxid) as xid_age, pg_size_pretty(pg_table_size(oid)) as table_size FROM pg_class WHERE relkind = 'r' and pg_table_size(oid) > 1073741824 ORDER BY age(re

Re: need help on memory allocation

Then we should find like if there are any idle sessions with uncommitted transactions. Those might be the culprits. Regards, Pavan -- Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html

Re: 8.2 Autovacuum BUG ?

Hi Claudio, We didn't configure any replication to our production server. Which strace are you talking about? We did a keen observation that only at the time 9'th minute of the hour and 39'th minute of the hour the so called archive logs are generated even when nobody is connecting from applicati

Re: need help on memory allocation

Hi Rambabu, If you are finding some sessions then of course your database is perfectly alright. As sessions won't consume any memory. Kindly specify the issue briefly. Regards, Pavan -- Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html

Re: 8.2 Autovacuum BUG ?

Hello, Is there any way to check, how many transactions happened till date from the point the database created and started accepting transactions ? The reason for this doubt is to find whether my database has crossed 2 million transactions or not. Strangely had an interesting observation, when

Re: 8.2 Autovacuum BUG ?

Hi Andreas, Yes I'm facing problem because of this huge WAL(archive log) generation. As it is seriously consuming a lot of disk space almost close to 50GB per day even if the DML's don't have that impact in this WAL generation. Previously the archive_log size is nearly 2 to 3 GB a day. Now with t

Re: 8.2 Autovacuum BUG ?

Hello Sergi, The size of the database is 24GB. The output of the above query is : datid | datname | procpid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start |xact_start| query_start

Re: 8.2 Autovacuum BUG ?

Hi Pallav, I'm currently on PostgreSQL 9.1. Everything was fine till Dec 27th 2017. But to my wonder archive logs started to increase from December 28th 2017 till date. The configuration parameters were default and everything in the past was fine with default configuration parameters. I'm facing