Re: Accounting for between table correlation

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.