pb with join plan

Поиск
Список
Период
Сортировка
От Marc Millas
Тема pb with join plan
Дата
Msg-id CADX_1aYjA-xZcg+CTq7m++WxjZ7HvHA5=hnhedKNF0TypK6j1Q@mail.gmail.com
обсуждение исходный текст
Ответы Re: pb with join plan
Список pgsql-general
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

В списке pgsql-general по дате отправления:

Предыдущее
От: Atul Kumar
Дата:
Сообщение: strange behavior of .pgpass file
Следующее
От: Adrian Klaver
Дата:
Сообщение: Re: strange behavior of .pgpass file