Re: unoptimized nested loops

Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
> On Tue, May 31, 2022 at 4:04 PM Tim Kelly <gtkelly(at)dialectronics(dot)com> wrote:
>> I do not see evidence that the nested loop is trying to reduce overhead
>> by using the smaller set. It seems to want to scan on data first either
>> way.

> The planner probably doesn't know which one is smaller.

There is not a lot of daylight between the cost estimates for
"a nestloop-join b" and "b nestloop-join a", if we're considering
plain seqscans on both tables and all else is equal. It tends to
come down to factors like which one is more densely populated.

As best I can tell, the issue Tim's unhappy about is not so
much the use of a nestloop as the lack of use of any index.
But "string like '%foo%'" is not at all optimizable with a
btree index. You might be able to get somewhere with a
pg_trgm GIN or GIST index.

regards, tom lane