On Fri, Jan 15, 2021 at 12:27 PM Michael Lewis <mle...@entrata.com> wrote:
> On Fri, Jan 15, 2021 at 10:22 AM Alexander Stoddard < > alexander.stodd...@gmail.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 <mle...@entrata.com> wrote: > On Fri, Jan 15, 2021 at 10:22 AM Alexander Stoddard < > alexander.stodd...@gmail.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? >
