Re: Accounting for between table correlation

Hi Alexander,

As per Ron, you are not supposed to ask your questions here.

As According to him, we should keep on doing research on internet rather
than asking for support directly even you have done enough research and
until unless “Ron” won’t be satisfied you have to do keep on researching.

Regards
Atul

On Friday, January 15, 2021, Ron <ronljohnsonjr(at)gmail(dot)com> wrote:

> On 1/15/21 9:19 AM, Alexander Stoddard wrote:
>
>> I am having ongoing trouble with a pair of tables, the design of which is
>> beyond my control.
>>
>> There is a 'primary' table with hundreds of millions of rows. There is
>> then a 'subclass' table ~ 10% of the primary which has additional fields.
>> The tables logically share a primary key field (although that is not
>> annotated in the schema).
>>
>> Membership of the subclass table has high correlation with fields in the
>> primary table - it is very much not random. It seems query plans where the
>> two tables are joined are 'unstable'. Over time very different plans can
>> result even for unchanged queries and some plans are exceedingly
>> inefficient.
>>
>> I think what is going on is that the query planner assumes many fewer
>> rows are going to join to the subtable than actually do (because of the
>> strong correlation).
>>
>> Can anyone offer any advice on dealing with this scenario (or better
>> diagnosing it)?
>>
>
> Do the tables get analyzed on a regular basis?
>
> --
> Angular momentum makes the world go 'round.
>
>
>