pá 24. 7. 2020 v 16:38 odesílatel Christophe Pettus <xof@xxxxxxxxxxxx> napsal:
> On Jul 24, 2020, at 06:48, Tom Lane <tgl@xxxxxxxxxxxxx> 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@xxxxxxxxxxxx
