From: | Ron <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Accounting for between table correlation |
Date: | 2021-01-15 15:23:58 |
Message-ID: | b804bb4e-8f7b-d391-8827-a82b137321e8@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Atul Kumar | 2021-01-15 15:29:12 | Re: Accounting for between table correlation |
Previous Message | Alexander Stoddard | 2021-01-15 15:19:45 | Accounting for between table correlation |