so 25. 7. 2020 v 0:34 odesÃlatel Tom Lane <t...@sss.pgh.pa.us> napsal:
> [ redirecting to -hackers ]
>
> I wrote:
> > The core issue here is "how do we know whether the table is likely to
> stay
> > empty?". I can think of a couple of more or less klugy solutions:
>
For these special cases is probably possible to ensure ANALYZE before any
SELECT. When the table is created, then it is analyzed, and after that it
is published and used for SELECT. Usually this table is not modified ever.
Because it is a special case, then it is not necessarily too sophisticated
a solution. But for built in solution it can be designed more goneral
> > 1. Arrange to send out a relcache inval when adding the first page to
> > a table, and then remove the planner hack for disbelieving relpages = 0.
> > I fear this'd be a mess from a system structural standpoint, but it might
> > work fairly transparently.
>
> I experimented with doing this. It's not hard to code, if you don't mind
> having RelationGetBufferForTuple calling CacheInvalidateRelcache. I'm not
> sure whether that code path might cause any long-term problems, but it
> seems to work OK right now. However, this solution causes massive
> "failures" in the regression tests as a result of plans changing. I'm
> sure that's partly because we use so many small tables in the tests.
> Nonetheless, it's not promising from the standpoint of not causing
> unexpected problems in the real world.
>
> > 2. Establish the convention that vacuuming or analyzing an empty table
> > is what you do to tell the system that this state is going to persist.
> > That's more or less what the existing comments in plancat.c envision,
> > but we never made a definition for how the occurrence of that event
> > would be recorded in the catalogs, other than setting relpages > 0.
> > Rather than adding another pg_class column, I'm tempted to say that
> > vacuum/analyze should set relpages to a minimum of 1, even if the
> > relation has zero pages.
>
> I also tried this, and it seems a lot more promising: no existing
> regression test cases change. So perhaps we should do the attached
> or something like it.
>
I am sending a patch that is years used in GoodData.
I am not sure if the company uses 0 or 1, but I can ask.
Regards
Pavel
> regards, tom lane
>
>
diff --git a/src/backend/access/table/tableam.c b/src/backend/access/table/tableam.c
index c814733b22..d9ca9904e1 100644
--- a/src/backend/access/table/tableam.c
+++ b/src/backend/access/table/tableam.c
@@ -33,6 +33,7 @@
/* GUC variables */
char *default_table_access_method = DEFAULT_TABLE_ACCESS_METHOD;
bool synchronize_seqscans = true;
+int fake_pages = 10;
/* ----------------------------------------------------------------------------
@@ -591,10 +592,10 @@ table_block_relation_estimate_size(Relation rel, int32 *attr_widths,
* Totally empty parent tables are quite common, so we should be willing
* to believe that they are empty.
*/
- if (curpages < 10 &&
+ if (curpages < fake_pages &&
relpages == 0 &&
!rel->rd_rel->relhassubclass)
- curpages = 10;
+ curpages = fake_pages;
/* report estimated # pages */
*pages = curpages;
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 11b3f050bc..6954274c28 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -2195,6 +2195,17 @@ static struct config_int ConfigureNamesInt[] =
NULL, NULL, NULL
},
+ {
+ {"fake_pages", PGC_USERSET, QUERY_TUNING_OTHER,
+ gettext_noop("Sets a number of pages for planner when relation has no pages."),
+ NULL,
+ GUC_UNIT_BLOCKS
+ },
+ &fake_pages,
+ 10, 0, INT_MAX,
+ NULL, NULL, NULL
+ },
+
{
{"max_standby_archive_delay", PGC_SIGHUP, REPLICATION_STANDBY,
gettext_noop("Sets the maximum delay before canceling queries when a hot standby server is processing archived WAL data."),
diff --git a/src/include/access/tableam.h b/src/include/access/tableam.h
index eb18739c36..f997f9dbc4 100644
--- a/src/include/access/tableam.h
+++ b/src/include/access/tableam.h
@@ -30,6 +30,8 @@
extern char *default_table_access_method;
extern bool synchronize_seqscans;
+extern int fake_pages;
+
struct BulkInsertStateData;
struct IndexInfo;
