Re: Optimizing count(), but Explain estimates wildly off

Поиск
Список
Период
Сортировка
От Chema
Тема Re: Optimizing count(), but Explain estimates wildly off
Дата
Msg-id CALdEsqOAfcvzvZ47VBbsuqSOoAo8dDa8HtLTVspB7YptdGPP8A@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Optimizing count(), but Explain estimates wildly off  (Greg Sabino Mullane <htamfids@gmail.com>)
Ответы Re: Optimizing count(), but Explain estimates wildly off  (Greg Sabino Mullane <htamfids@gmail.com>)
Список pgsql-performance
Дерево обсуждения


El lun, 4 mar 2024 a la(s) 7:50 p.m., Greg Sabino Mullane (htamfids@gmail.com) escribió:
On Mon, Mar 4, 2024 at 2:14 PM Chema <chema@interneta.org> wrote:
There's one JSON column in each table with a couple fields, and a column with long texts  in Items.
and earlier indicated the query was:
Select * from tenders inner join items
 
You do not want to do a "select star" on both tables unless you 100% need every single column and plan to actively do something with it. Especially true for large text and json columns. Also, use jsonb not json.
Tuples aren't really that long in avg (300 bytes for Tenders,  twice as much for Items).  In any case, the Select * was to be used with Explain to obtain an estimated row count instantly from stats, as described in my first email, but even raising stats to 5k in relevant columns has not improved the planner's estimates, which are off by almost 1M, and there's been no suggestion of what could cause that.

Googlin' once again, though, this SO answer implies that that might actually be the normal for anything but the simplest queries:

Depending on the complexity of your query, this number may become less and less accurate. In fact, in my application, as we added joins and complex conditions, it became so inaccurate it was completely worthless, even to know how within a power of 100 how many rows we'd have returned, so we had to abandon that strategy.

But if your query is simple enough that Pg can predict within some reasonable margin of error how many rows it will return, it may work for you.

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

Предыдущее
От: Marc Millas
Дата:
Сообщение: Re: Separate 100 M spatial data in 100 tables VS one big table
Следующее
От: Greg Sabino Mullane
Дата:
Сообщение: Re: Optimizing count(), but Explain estimates wildly off