Re: Postgresql RDS DB Latency Chossing Hash join Plan

On Tue, Feb 12, 2019 at 12:33 PM Michael Lewis <mlewis(at)entrata(dot)com> wrote:

> Did you update the stats by running ANALYZE on the tables involved, or
> perhaps the entire database on the 'Non prod Aurora RDS instance'? Can you
> share the two execution plans?
>
> *Michael Lewis | Software Engineer*
> *Entrata*
> *c: **619.370.8697 <619-370-8697>*
>

Here is the plan for both of the DB instances.

>
>
> On Tue, Feb 12, 2019 at 11:27 AM github kran <githubkran(at)gmail(dot)com> wrote:
>
>>
>>
>> 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
>>>>>>
>>>>>