2 * Copyright (c) 2011 Teodor Sigaev <teodor@sigaev.ru>
5 * Redistribution and use in source and binary forms, with or without
6 * modification, are permitted provided that the following conditions
8 * 1. Redistributions of source code must retain the above copyright
9 * notice, this list of conditions and the following disclaimer.
10 * 2. Redistributions in binary form must reproduce the above copyright
11 * notice, this list of conditions and the following disclaimer in the
12 * documentation and/or other materials provided with the distribution.
13 * 3. Neither the name of the author nor the names of any co-contributors
14 * may be used to endorse or promote products derived from this software
15 * without specific prior written permission.
17 * THIS SOFTWARE IS PROVIDED BY CONTRIBUTORS ``AS IS'' AND ANY EXPRESS
18 * OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
19 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
20 * ARE DISCLAIMED. IN NO EVENT SHALL CONTRIBUTORS BE LIABLE FOR ANY
21 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
22 * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE
23 * GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
24 * INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER
25 * IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR
26 * OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN
27 * IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
33 #include "catalog/namespace.h"
34 #include "commands/vacuum.h"
35 #include "executor/executor.h"
36 #include "nodes/nodes.h"
37 #include "nodes/parsenodes.h"
38 #include "storage/bufmgr.h"
39 #include "utils/builtins.h"
40 #include "utils/hsearch.h"
41 #include "utils/memutils.h"
42 #include "utils/lsyscache.h"
43 #include "utils/guc.h"
44 #if PG_VERSION_NUM >= 90200
45 #include "catalog/pg_class.h"
46 #include "nodes/primnodes.h"
47 #include "tcop/utility.h"
48 #include "utils/rel.h"
49 #include "utils/relcache.h"
50 #include "utils/timestamp.h"
51 #if PG_VERSION_NUM >= 90500
52 #include "nodes/makefuncs.h"
56 #ifdef PG_MODULE_MAGIC
60 static bool online_analyze_enable = true;
61 static bool online_analyze_verbose = true;
62 static double online_analyze_scale_factor = 0.1;
63 static int online_analyze_threshold = 50;
64 static int online_analyze_capacity_threshold = 100000;
65 static double online_analyze_min_interval = 10000;
66 static int online_analyze_lower_limit = 0;
68 static ExecutorEnd_hook_type oldExecutorEndHook = NULL;
69 #if PG_VERSION_NUM >= 90200
70 static ProcessUtility_hook_type oldProcessUtilityHook = NULL;
76 OATT_PERSISTENT = 0x01,
77 OATT_TEMPORARY = 0x02,
79 } OnlineAnalyzeTableType;
81 static const struct config_enum_entry online_analyze_table_type_options[] =
83 {"all", OATT_ALL, false},
84 {"persistent", OATT_PERSISTENT, false},
85 {"temporary", OATT_TEMPORARY, false},
86 {"none", OATT_NONE, false},
90 static int online_analyze_table_type = (int)OATT_ALL;
92 typedef struct TableList {
98 static TableList excludeTables = {0, NULL, NULL};
99 static TableList includeTables = {0, NULL, NULL};
101 typedef struct OnlineAnalyzeTableStat {
104 PgStat_Counter n_tuples;
105 PgStat_Counter changes_since_analyze;
106 TimestampTz autovac_analyze_timestamp;
107 TimestampTz analyze_timestamp;
108 } OnlineAnalyzeTableStat;
110 static MemoryContext onlineAnalyzeMemoryContext = NULL;
111 static HTAB *relstats = NULL;
113 static void relstatsInit(void);
116 oid_cmp(const void *a, const void *b)
118 if (*(Oid*)a == *(Oid*)b)
120 return (*(Oid*)a > *(Oid*)b) ? 1 : -1;
124 tableListAssign(const char * newval, bool doit, TableList *tbl)
133 rawname = pstrdup(newval);
135 if (!SplitIdentifierString(rawname, ',', &namelist))
140 nOids = list_length(namelist);
141 newOids = malloc(sizeof(Oid) * (nOids+1));
143 elog(ERROR,"could not allocate %d bytes",
144 (int)(sizeof(Oid) * (nOids+1)));
149 char *curname = (char *) lfirst(l);
150 #if PG_VERSION_NUM >= 90200
151 Oid relOid = RangeVarGetRelid(makeRangeVarFromNameList(
152 stringToQualifiedNameList(curname)), NoLock, true);
154 Oid relOid = RangeVarGetRelid(makeRangeVarFromNameList(
155 stringToQualifiedNameList(curname)), true);
158 if (relOid == InvalidOid)
160 #if PG_VERSION_NUM >= 90100
163 elog(WARNING,"'%s' does not exist", curname);
166 else if ( get_rel_relkind(relOid) != RELKIND_RELATION )
168 #if PG_VERSION_NUM >= 90100
171 elog(WARNING,"'%s' is not an table", curname);
176 newOids[i++] = relOid;
185 tbl->tables = newOids;
186 if (tbl->nTables > 1)
187 qsort(tbl->tables, tbl->nTables, sizeof(tbl->tables[0]), oid_cmp);
203 #if PG_VERSION_NUM >= 90100
205 excludeTablesCheck(char **newval, void **extra, GucSource source)
209 val = (char*)tableListAssign(*newval, false, &excludeTables);
221 excludeTablesAssign(const char *newval, void *extra)
223 tableListAssign(newval, true, &excludeTables);
227 includeTablesCheck(char **newval, void **extra, GucSource source)
231 val = (char*)tableListAssign(*newval, false, &includeTables);
243 includeTablesAssign(const char *newval, void *extra)
245 tableListAssign(newval, true, &excludeTables);
248 #else /* PG_VERSION_NUM < 90100 */
251 excludeTablesAssign(const char * newval, bool doit, GucSource source)
253 return tableListAssign(newval, doit, &excludeTables);
257 includeTablesAssign(const char * newval, bool doit, GucSource source)
259 return tableListAssign(newval, doit, &includeTables);
265 tableListShow(TableList *tbl)
271 len = 1 /* \0 */ + tbl->nTables * (2 * NAMEDATALEN + 2 /* ', ' */ + 1 /* . */);
272 ptr = val = palloc(len);
274 for(i=0; i<tbl->nTables; i++)
276 char *relname = get_rel_name(tbl->tables[i]);
277 Oid nspOid = get_rel_namespace(tbl->tables[i]);
278 char *nspname = get_namespace_name(nspOid);
280 if ( relname == NULL || nspOid == InvalidOid || nspname == NULL )
283 ptr += snprintf(ptr, len - (ptr - val), "%s%s.%s",
292 excludeTablesShow(void)
294 return tableListShow(&excludeTables);
298 includeTablesShow(void)
300 return tableListShow(&includeTables);
304 matchOid(TableList *tbl, Oid oid)
306 Oid *StopLow = tbl->tables,
307 *StopHigh = tbl->tables + tbl->nTables,
310 /* Loop invariant: StopLow <= val < StopHigh */
311 while (StopLow < StopHigh)
313 StopMiddle = StopLow + ((StopHigh - StopLow) >> 1);
315 if (*StopMiddle == oid)
317 else if (*StopMiddle < oid)
318 StopLow = StopMiddle + 1;
320 StopHigh = StopMiddle;
326 #if PG_VERSION_NUM >= 90500
328 makeRangeVarFromOid(Oid relOid)
331 get_namespace_name(get_rel_namespace(relOid)),
332 get_rel_name(relOid),
340 makeAnalyze(Oid relOid, CmdType operation, int64 naffected)
342 TimestampTz now = GetCurrentTimestamp();
344 OnlineAnalyzeTableType reltype;
347 OnlineAnalyzeTableStat *rstat,
349 PgStat_StatTabEntry *tabentry = NULL;
351 if (relOid == InvalidOid)
355 /* return if there is no changes */
357 else if (naffected < 0)
358 /* number if affected rows is unknown */
361 rel = RelationIdGetRelation(relOid);
362 if (rel->rd_rel->relkind != RELKIND_RELATION)
369 #if PG_VERSION_NUM >= 90100
370 (rel->rd_rel->relpersistence == RELPERSISTENCE_TEMP)
372 (rel->rd_istemp || rel->rd_islocaltemp)
374 ? OATT_TEMPORARY : OATT_PERSISTENT;
379 * includeTables overwrites excludeTables
381 switch(online_analyze_table_type)
384 if (get_rel_relkind(relOid) != RELKIND_RELATION ||
385 (matchOid(&excludeTables, relOid) == true &&
386 matchOid(&includeTables, relOid) == false))
390 if (get_rel_relkind(relOid) != RELKIND_RELATION ||
391 matchOid(&includeTables, relOid) == false)
395 case OATT_PERSISTENT:
398 * skip analyze if relation's type doesn't not match
399 * online_analyze_table_type
401 if ((online_analyze_table_type & reltype) == 0 ||
402 matchOid(&excludeTables, relOid) == true)
404 if (matchOid(&includeTables, relOid) == false)
411 * Do not store data about persistent table in local memory because we
412 * could not track changes of them: they could be changed by another
413 * backends. So always get a pgstat table entry.
415 if (reltype == OATT_TEMPORARY)
416 rstat = hash_search(relstats, &relOid, HASH_ENTER, &found);
418 rstat = &dummyrstat; /* found == false for following if */
420 if (found == false || rstat->rereadStat == true || naffected == 0)
425 MemSet(rstat, 0, sizeof(*rstat));
426 rstat->tableid = relOid;
428 Assert(rstat->tableid == relOid);
430 tabentry = pgstat_fetch_stat_tabentry(relOid);
434 rstat->n_tuples = tabentry->n_dead_tuples + tabentry->n_live_tuples;
435 rstat->changes_since_analyze =
436 #if PG_VERSION_NUM >= 90000
437 tabentry->changes_since_analyze;
439 tabentry->n_live_tuples + tabentry->n_dead_tuples -
440 tabentry->last_anl_tuples;
442 rstat->autovac_analyze_timestamp =
443 tabentry->autovac_analyze_timestamp;
444 rstat->analyze_timestamp = tabentry->analyze_timestamp;
445 rstat->rereadStat = false;
450 rstat->rereadStat = true;
455 /* do not analyze too often, if both stamps are exceeded the go */
456 TimestampDifferenceExceeds(rstat->analyze_timestamp, now, online_analyze_min_interval) &&
457 TimestampDifferenceExceeds(rstat->autovac_analyze_timestamp, now, online_analyze_min_interval) &&
458 /* do not analyze too small tables */
459 rstat->n_tuples + rstat->changes_since_analyze + naffected > online_analyze_lower_limit &&
460 /* be in sync with relation_needs_vacanalyze */
461 ((double)(rstat->changes_since_analyze + naffected)) >=
462 online_analyze_scale_factor * ((double)rstat->n_tuples) +
463 (double)online_analyze_threshold))
465 #if PG_VERSION_NUM < 90500
468 VacuumParams vacstmt;
470 TimestampTz startStamp, endStamp;
473 memset(&startStamp, 0, sizeof(startStamp)); /* keep compiler quiet */
475 memset(&vacstmt, 0, sizeof(vacstmt));
477 vacstmt.freeze_min_age = -1;
478 vacstmt.freeze_table_age = -1; /* ??? */
480 #if PG_VERSION_NUM < 90500
481 vacstmt.type = T_VacuumStmt;
482 vacstmt.relation = NULL;
483 vacstmt.va_cols = NIL;
484 #if PG_VERSION_NUM >= 90000
485 vacstmt.options = VACOPT_ANALYZE;
486 if (online_analyze_verbose)
487 vacstmt.options |= VACOPT_VERBOSE;
489 vacstmt.vacuum = vacstmt.full = false;
490 vacstmt.analyze = true;
491 vacstmt.verbose = online_analyze_verbose;
494 vacstmt.multixact_freeze_min_age = -1;
495 vacstmt.multixact_freeze_table_age = -1;
496 vacstmt.log_min_duration = -1;
499 if (online_analyze_verbose)
500 startStamp = GetCurrentTimestamp();
503 #if PG_VERSION_NUM < 90500
505 #if PG_VERSION_NUM >= 90018
508 , GetAccessStrategy(BAS_VACUUM)
509 #if (PG_VERSION_NUM >= 90000) && (PG_VERSION_NUM < 90004)
513 makeRangeVarFromOid(relOid),
514 VACOPT_ANALYZE | ((online_analyze_verbose) ? VACOPT_VERBOSE : 0),
515 &vacstmt, NULL, true, GetAccessStrategy(BAS_VACUUM)
519 if (online_analyze_verbose)
524 endStamp = GetCurrentTimestamp();
525 TimestampDifference(startStamp, endStamp, &secs, µsecs);
526 elog(INFO, "analyze \"%s\" took %.02f seconds",
527 get_rel_name(relOid),
528 ((double)secs) + ((double)microsecs)/1.0e6);
531 rstat->autovac_analyze_timestamp = now;
532 rstat->changes_since_analyze = 0;
533 rstat->rereadStat = true;
535 /* update last analyze timestamp in local memory of backend */
538 tabentry->analyze_timestamp = now;
539 tabentry->changes_since_analyze = 0;
542 /* force reload stat for new table */
544 pgstat_clear_snapshot();
549 #if PG_VERSION_NUM >= 90000
551 tabentry->changes_since_analyze += naffected;
553 rstat->changes_since_analyze += naffected;
556 /* Reset local cache if we are over limit */
557 if (hash_get_num_entries(relstats) > online_analyze_capacity_threshold)
561 extern PGDLLIMPORT void onlineAnalyzeHooker(QueryDesc *queryDesc);
563 onlineAnalyzeHooker(QueryDesc *queryDesc)
565 int64 naffected = -1;
567 if (queryDesc->estate)
568 naffected = queryDesc->estate->es_processed;
570 if (online_analyze_enable && queryDesc->plannedstmt &&
571 (queryDesc->operation == CMD_INSERT ||
572 queryDesc->operation == CMD_UPDATE ||
573 queryDesc->operation == CMD_DELETE
574 #if PG_VERSION_NUM < 90200
575 || (queryDesc->operation == CMD_SELECT &&
576 queryDesc->plannedstmt->intoClause)
580 #if PG_VERSION_NUM < 90200
581 if (queryDesc->operation == CMD_SELECT)
583 Oid relOid = RangeVarGetRelid(queryDesc->plannedstmt->intoClause->rel, true);
585 makeAnalyze(relOid, queryDesc->operation, naffected);
589 if (queryDesc->plannedstmt->resultRelations &&
590 queryDesc->plannedstmt->rtable)
594 foreach(l, queryDesc->plannedstmt->resultRelations)
596 int n = lfirst_int(l);
597 RangeTblEntry *rte = list_nth(queryDesc->plannedstmt->rtable, n-1);
599 if (rte->rtekind == RTE_RELATION)
600 makeAnalyze(rte->relid, queryDesc->operation, naffected);
605 if (oldExecutorEndHook)
606 oldExecutorEndHook(queryDesc);
608 standard_ExecutorEnd(queryDesc);
611 #if PG_VERSION_NUM >= 90200
613 onlineAnalyzeHookerUtility(Node *parsetree, const char *queryString,
614 #if PG_VERSION_NUM >= 90300
615 ProcessUtilityContext context, ParamListInfo params,
617 ParamListInfo params, bool isTopLevel,
619 DestReceiver *dest, char *completionTag) {
620 RangeVar *tblname = NULL;
622 if (IsA(parsetree, CreateTableAsStmt) && ((CreateTableAsStmt*)parsetree)->into)
623 tblname = (RangeVar*)copyObject(((CreateTableAsStmt*)parsetree)->into->rel);
625 if (oldProcessUtilityHook)
626 oldProcessUtilityHook(parsetree, queryString,
627 #if PG_VERSION_NUM >= 90300
632 dest, completionTag);
634 standard_ProcessUtility(parsetree, queryString,
635 #if PG_VERSION_NUM >= 90300
640 dest, completionTag);
643 Oid tblOid = RangeVarGetRelid(tblname, NoLock, true);
645 makeAnalyze(tblOid, CMD_INSERT, -1);
656 MemSet(&hash_ctl, 0, sizeof(hash_ctl));
658 hash_ctl.hash = oid_hash;
659 flags |= HASH_FUNCTION;
661 if (onlineAnalyzeMemoryContext)
663 Assert(relstats != NULL);
664 MemoryContextReset(onlineAnalyzeMemoryContext);
668 Assert(relstats == NULL);
669 onlineAnalyzeMemoryContext =
670 AllocSetContextCreate(CacheMemoryContext,
671 "online_analyze storage context",
672 #if PG_VERSION_NUM < 90600
673 ALLOCSET_DEFAULT_MINSIZE,
674 ALLOCSET_DEFAULT_INITSIZE,
675 ALLOCSET_DEFAULT_MAXSIZE
677 ALLOCSET_DEFAULT_SIZES
682 hash_ctl.hcxt = onlineAnalyzeMemoryContext;
683 flags |= HASH_CONTEXT;
685 hash_ctl.keysize = sizeof(Oid);
687 hash_ctl.entrysize = sizeof(OnlineAnalyzeTableStat);
690 relstats = hash_create("online_analyze storage", 1024, &hash_ctl, flags);
699 oldExecutorEndHook = ExecutorEnd_hook;
701 ExecutorEnd_hook = onlineAnalyzeHooker;
703 #if PG_VERSION_NUM >= 90200
704 oldProcessUtilityHook = ProcessUtility_hook;
706 ProcessUtility_hook = onlineAnalyzeHookerUtility;
710 DefineCustomBoolVariable(
711 "online_analyze.enable",
712 "Enable on-line analyze",
713 "Enables analyze of table directly after insert/update/delete/select into",
714 &online_analyze_enable,
715 #if PG_VERSION_NUM >= 80400
716 online_analyze_enable,
719 #if PG_VERSION_NUM >= 80400
721 #if PG_VERSION_NUM >= 90100
729 DefineCustomBoolVariable(
730 "online_analyze.verbose",
731 "Verbosity of on-line analyze",
732 "Make ANALYZE VERBOSE after table's changes",
733 &online_analyze_verbose,
734 #if PG_VERSION_NUM >= 80400
735 online_analyze_verbose,
738 #if PG_VERSION_NUM >= 80400
740 #if PG_VERSION_NUM >= 90100
748 DefineCustomRealVariable(
749 "online_analyze.scale_factor",
750 "fraction of table size to start on-line analyze",
751 "fraction of table size to start on-line analyze",
752 &online_analyze_scale_factor,
753 #if PG_VERSION_NUM >= 80400
754 online_analyze_scale_factor,
759 #if PG_VERSION_NUM >= 80400
761 #if PG_VERSION_NUM >= 90100
769 DefineCustomIntVariable(
770 "online_analyze.threshold",
771 "min number of row updates before on-line analyze",
772 "min number of row updates before on-line analyze",
773 &online_analyze_threshold,
774 #if PG_VERSION_NUM >= 80400
775 online_analyze_threshold,
780 #if PG_VERSION_NUM >= 80400
782 #if PG_VERSION_NUM >= 90100
790 DefineCustomIntVariable(
791 "online_analyze.capacity_threshold",
792 "Max local cache table capacity",
793 "Max local cache table capacity",
794 &online_analyze_capacity_threshold,
795 #if PG_VERSION_NUM >= 80400
796 online_analyze_capacity_threshold,
801 #if PG_VERSION_NUM >= 80400
803 #if PG_VERSION_NUM >= 90100
811 DefineCustomRealVariable(
812 "online_analyze.min_interval",
813 "minimum time interval between analyze call (in milliseconds)",
814 "minimum time interval between analyze call (in milliseconds)",
815 &online_analyze_min_interval,
816 #if PG_VERSION_NUM >= 80400
817 online_analyze_min_interval,
822 #if PG_VERSION_NUM >= 80400
824 #if PG_VERSION_NUM >= 90100
832 DefineCustomEnumVariable(
833 "online_analyze.table_type",
834 "Type(s) of table for online analyze: all(default), persistent, temporary, none",
836 &online_analyze_table_type,
837 #if PG_VERSION_NUM >= 80400
838 online_analyze_table_type,
840 online_analyze_table_type_options,
842 #if PG_VERSION_NUM >= 80400
844 #if PG_VERSION_NUM >= 90100
852 DefineCustomStringVariable(
853 "online_analyze.exclude_tables",
854 "List of tables which will not online analyze",
856 &excludeTables.tableStr,
857 #if PG_VERSION_NUM >= 80400
862 #if PG_VERSION_NUM >= 90100
871 DefineCustomStringVariable(
872 "online_analyze.include_tables",
873 "List of tables which will online analyze",
875 &includeTables.tableStr,
876 #if PG_VERSION_NUM >= 80400
881 #if PG_VERSION_NUM >= 90100
890 DefineCustomIntVariable(
891 "online_analyze.lower_limit",
892 "min number of rows in table to analyze",
893 "min number of rows in table to analyze",
894 &online_analyze_lower_limit,
895 #if PG_VERSION_NUM >= 80400
896 online_analyze_lower_limit,
901 #if PG_VERSION_NUM >= 80400
903 #if PG_VERSION_NUM >= 90100
917 ExecutorEnd_hook = oldExecutorEndHook;
918 #if PG_VERSION_NUM >= 90200
919 ProcessUtility_hook = oldProcessUtilityHook;
922 if (excludeTables.tables)
923 free(excludeTables.tables);
924 if (includeTables.tables)
925 free(includeTables.tables);
927 excludeTables.tables = includeTables.tables = NULL;
928 excludeTables.nTables = includeTables.nTables = 0;