El lun, 4 mar 2024 a la(s) 7:50 p.m., Greg Sabino Mullane (htamfids@xxxxxxxxx) escribió:
On Mon, Mar 4, 2024 at 2:14 PM Chema <chema@xxxxxxxxxxxxx> 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 itemsYou 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.
