Re: Accounting for between table correlation

On Fri, Jan 15, 2021 at 12:27 PM Michael Lewis <mlewis(at)entrata(dot)com> wrote:

> On Fri, Jan 15, 2021 at 10:22 AM Alexander Stoddard <
> alexander(dot)stoddard(at)gmail(dot)com> wrote:
>
>> The 'fast plans' use parallel seq scans. The 'slow plans' is using index
>> scans. It appears a good query plan correctly predicts it should be bulk
>> processing the tables but bad ones get fooled into trashing (hard disk, not
>> SSD) by mispredicting too few rows to join between the tables.
>>
>
> How many tables are involved?
>

The queries are complex, multiple joins to 10 plus tables, although most
are to tiny enum type lookup tables. I believe it is the join between the
two large tables that I have described that causes the issue, and that
seems to be reflected in the different strategies in the plans. For my own
learning and to clarify the problem I probably will have to try and
reproduce the behavior in a test case.

> Are you sure it is stats getting updated causing the change in behavior?
>
No I'm not sure, could something else flip a plan after an ANALYZE?
Differing performance of multiple runs of the same query could be due
caching etc. but that would be a timing difference without a change in
query plan. The output plans I see are radically different and correlate
with large magnitude performance changes.

> Are you hitting the genetic optimizer?
>

I am doing nothing to specify the optimizer. Do I have configurable options
in that regard? I was unaware of them.

Thank you,
Alex

On Fri, Jan 15, 2021 at 12:27 PM Michael Lewis <mlewis(at)entrata(dot)com> wrote:

> On Fri, Jan 15, 2021 at 10:22 AM Alexander Stoddard <
> alexander(dot)stoddard(at)gmail(dot)com> wrote:
>
>> The 'fast plans' use parallel seq scans. The 'slow plans' is using index
>> scans. It appears a good query plan correctly predicts it should be bulk
>> processing the tables but bad ones get fooled into trashing (hard disk, not
>> SSD) by mispredicting too few rows to join between the tables.
>>
>
> How many tables are involved? Are you sure it is stats getting updated
> causing the change in behavior? Are you hitting the genetic optimizer?
>