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

El lun, 4 mar 2024 a la(s) 7:50 p.m., Greg Sabino Mullane (
htamfids(at)gmail(dot)com) escribió:

> On Mon, Mar 4, 2024 at 2:14 PM Chema <chema(at)interneta(dot)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
<https://stackoverflow.com/a/7943283/564148> 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.