On Tue, Feb 12, 2019 at 7:07 AM github kran <githubkran(at)gmail(dot)com> wrote:
>
>
> On Mon, Feb 11, 2019 at 6:00 PM github kran <githubkran(at)gmail(dot)com> wrote:
>
>>
>>
>> On Mon, Feb 11, 2019 at 3:29 PM Michael Lewis <mlewis(at)entrata(dot)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 <githubkran(at)gmail(dot)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
>>>>
>>>