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@entrata.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?