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.