On Thu, Jul 18, 2024 at 2:38 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> James Coleman <jtc331@gmail.com> writes:
> > The plan generated by the planner changed suddenly one morning this
> > week, and in a very surprising way: the innermost scan (of "objects")
> > started choosing a seq scan, despite the cost from that node being
> > very high and an index scan being possible
>
> That looks weird to me too, but given the number of tables involved
> I wonder what you have join_collapse_limit/from_collapse_limit set
> to. If those are smaller than the query's relation count it could
> be that this is an artifact of optimization of a join subproblem.
> However, if it's the very same query you've been generating all along,
> this theory doesn't really explain the sudden change of plan choice.
Those gucs are both set to 8, so...that could be a factor, except that
as you noted if that's not a variable that's changing during the
flip-flop of plans, then it's hard to make that the explanation. See
below for more on this.
> Also, even granting the bad-peephole-optimization theory, it seems
> like the best path for the objects table alone would still have been
> the index scan, so I'm confused too.
I'm glad I'm not the only one.
> What nondefault planner settings
> have you got? ("EXPLAIN (SETTINGS)" would help with answering that
> accurately.)
Settings: max_parallel_workers = '24', maintenance_io_concurrency =
'1', effective_cache_size = '264741832kB', work_mem = '16MB',
random_page_cost = '1', search_path = 'object_shard,public'
Of those I'd expect random_page_cost to steer it towards the index
scan rather than away from it. The others don't seem to me like they
should effect which path would be the best one for the objects table
alone.
> Are you really sure nothing changed about what the ORM is emitting?
Yes, aside from the fact that application code didn't change, we
reproduced the problem by restoring a physical snapshot of the
database and were able to get the bad plan and then get it to change
to the good plan by analyzing object_audits. Additionally on the live
production database (well, a read replica anyway) today it'd switched
back to the bad plan, and then an hour layer it'd swapped back again
for the same exact query.
Thanks,
James Coleman