Re: Row estimates for empty tables

pá 24. 7. 2020 v 16:38 odesílatel Christophe Pettus <xof(at)thebuild(dot)com>
napsal:

>
>
> > On Jul 24, 2020, at 06:48, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >
> > There's certainly not a lot besides tradition to justify the exact
> > numbers used in this case.
>
> Since we already special-case parent tables for partition sets, would a
> storage parameter that lets you either tell the planner "no, really, zero
> is reasonable here" or sets a minimum number of rows to plan for be
> reasonable? I happened to get bit by this tracking down an issue where
> several tables in a large query had zero rows, and the planner's assumption
> of a few pages worth caused some sub-optimal plans. The performance hit
> wasn't huge, but they were being joined to some *very* large tables, and
> the differences added up.
>

I did this patch ten years ago. GoodData application
https://www.gooddata.com/ uses Postgres lot, and this application stores
some results in tables (as guard against repeated calculations). Lot of
these tables have zero or one row.

Although we ran an ANALYZE over all tables - the queries on empty tables
had very bad plans, and I had to fix it by this patch. Another company uses
a fake one row in table - so there is no possibility to have a really empty
table.

It is an issue for special, not typical applications (this situation is
typical for some OLAP patterns) - it is not too often - but some clean
solution (instead hacking postgres) can be nice.

Regards

Pavel

> --
> -- Christophe Pettus
> xof(at)thebuild(dot)com
>
>
>
>