pb with join plan

Hi,

I have a postgres 14 on linux with a 15 TB db, with 20 cores and 150GB
RAM, all nvme ssd. . Currently one user :-)

A join between 2 big tables and then another join with a smaller third one
takes less than 1 minute and provides a result of 15 M lines. Fine.

if I do add a third join, with a 30k lines table, with a simple equality as
join clause it does work almost as fast. explain analyze indicates 45 sec.
same if i do the very same with another equality clause. explain analyze
indicates 140 sec.

But if I do the same with clause one OR clause 2, I have to kill the
request after an hour, seeing the filesystem showing more than 140 Mb of
increased usage.

Looking at the explain plan with one clause or the 2 ORed, there are
changes in the plan (of course)
with the fastest clause the estimated cost is 3 700 000 and with the a bit
slower one 3 900 000.
with both ORed, the estimated cost is 16 000 000. To me it does sound a bit
strange, as ORing the join clauses should add times, but not more (so so)

So, before providing the 3 explain plans (I must anonymize everything, so
somewhat boring) I would like to know if there is some obvious thing I am
missing.
all tables have been vacuum analyzed.

thanks

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com