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
