Accounting for between table correlation

From: Alexander Stoddard <alexander(dot)stoddard(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Accounting for between table correlation
Date: 2021-01-15 15:19:45
Message-ID: CADDNc-B=spVE0FjJEOUEsGo+ChvvkPjyLZk+MV-YHz80Fo4BCw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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)?

Thank you,
Alex

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ron 2021-01-15 15:23:58 Re: Accounting for between table correlation
Previous Message Thomas Kellerer 2021-01-15 15:17:28 Re: upgrade postgres 9.5 to 9.6