On Mon, Feb 11, 2019 at 6:00 PM github kran <githubk...@gmail.com> wrote:
> > > On Mon, Feb 11, 2019 at 3:29 PM Michael Lewis <mle...@entrata.com> wrote: > >> Are default statistics target the same on both prod and AWS? Have you >> analyzed all tables being used in this query to ensure stats are up proper? >> If the optimizer is choosing a different plan, then the stats must be >> different IMO. >> >> >> *Michael Lewis | Software Engineer* >> *Entrata* >> > > > Thanks for your reply I have verified few of the tables and their default > statistics target and they seem to be same but is there anything in > particular you want me to look at it to differentiate Prod and Non prod > databases ?. ( Also the DB instance size is same but there is little > more data in the Non prod Aurora RDS instance compared to Prod instance). > > Query used. >> select * from pg_stats where tablename = 'tableName' >> >> >> On Mon, Feb 11, 2019 at 2:15 PM github kran <githubk...@gmail.com> wrote: >> >>> Hi Postgres Team, >>> We are using AWS Aurora RDS Postgres DB 9.6.8 Engine. We recently >>> deleted few million rows from the database and ran into a issue in one of >>> our dev account where the >>> DB was not normal after this deletion. We did re index, vacuuming entire >>> database but we couldnt bring it to the same state as earlier. So next >>> steps we deleted the database and >>> recreated the database by copying the snapshot from a production >>> instance. Further did vacumming, re-index on the database. >>> >>> After this now the dev database seems to be in a better state than >>> earlier but we are seeing few of our DB calls are taking more than 1 minute >>> when we are fetching data and we observed >>> this is because the query plan was executing a hash join as part of the >>> query whereas a similar query on prod instance is not doing any hash join >>> and is returning faster. >>> >>> Also we did not want to experiment by modifing the DB settings by doing >>> enable_hash_join to off or random_page_count to 1 as we dont have these >>> settings in Prod instance. >>> >>> Note: >>> The partition table sizes we have here is between 40 GB to 75 GB and >>> this is our normal size range, we have a new partition table for every 7 >>> days. >>> >>> Appreciate your ideas on what we could be missing and what we can >>> correct here to reduce the query latency. >>> >>> Thanks >>> githubKran >>> >>
