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/lsyscache.h"
41 #include "utils/guc.h"
43 #ifdef PG_MODULE_MAGIC
47 static bool online_analyze_enable = true;
48 static bool online_analyze_verbose = true;
49 static double online_analyze_scale_factor = 0.1;
50 static int online_analyze_threshold = 50;
51 static double online_analyze_min_interval = 10000;
56 OATT_PERSISTENT = 0x01,
57 OATT_TEMPORARY = 0x02,
59 } OnlyneAnalyzeTableType;
61 static const struct config_enum_entry online_analyze_table_type_options[] =
63 {"all", OATT_ALL, false},
64 {"persistent", OATT_PERSISTENT, false},
65 {"temporary", OATT_TEMPORARY, false},
66 {"none", OATT_NONE, false},
70 static int online_analyze_table_type = (int)OATT_ALL;
72 typedef struct TableList {
78 static TableList excludeTables = {0, NULL, NULL};
79 static TableList includeTables = {0, NULL, NULL};
82 oid_cmp(const void *a, const void *b)
84 if (*(Oid*)a == *(Oid*)b)
86 return (*(Oid*)a > *(Oid*)b) ? 1 : -1;
90 tableListAssign(const char * newval, bool doit, TableList *tbl)
99 rawname = pstrdup(newval);
101 if (!SplitIdentifierString(rawname, ',', &namelist))
106 nOids = list_length(namelist);
107 newOids = malloc(sizeof(Oid) * (nOids+1));
109 elog(ERROR,"could not allocate %d bytes", (int)(sizeof(Oid) * (nOids+1)));
114 char *curname = (char *) lfirst(l);
115 Oid relOid = RangeVarGetRelid(makeRangeVarFromNameList(stringToQualifiedNameList(curname)), true);
117 if (relOid == InvalidOid)
119 #if PG_VERSION_NUM >= 90100
122 elog(WARNING,"'%s' does not exist", curname);
125 else if ( get_rel_relkind(relOid) != RELKIND_RELATION )
127 #if PG_VERSION_NUM >= 90100
130 elog(WARNING,"'%s' is not an table", curname);
135 newOids[i++] = relOid;
144 tbl->tables = newOids;
145 if (tbl->nTables > 1)
146 qsort(tbl->tables, tbl->nTables, sizeof(tbl->tables[0]), oid_cmp);
162 #if PG_VERSION_NUM >= 90100
164 excludeTablesCheck(char **newval, void **extra, GucSource source)
168 val = (char*)tableListAssign(*newval, false, &excludeTables);
180 excludeTablesAssign(const char *newval, void *extra)
182 tableListAssign(newval, true, &excludeTables);
186 includeTablesCheck(char **newval, void **extra, GucSource source)
190 val = (char*)tableListAssign(*newval, false, &includeTables);
202 includeTablesAssign(const char *newval, void *extra)
204 tableListAssign(newval, true, &excludeTables);
207 #else /* PG_VERSION_NUM < 90100 */
210 excludeTablesAssign(const char * newval, bool doit, GucSource source)
212 return tableListAssign(newval, doit, &excludeTables);
216 includeTablesAssign(const char * newval, bool doit, GucSource source)
218 return tableListAssign(newval, doit, &includeTables);
224 tableListShow(TableList *tbl)
230 len = 1 /* \0 */ + tbl->nTables * (2 * NAMEDATALEN + 2 /* ', ' */ + 1 /* . */);
231 ptr = val = palloc(len);
233 for(i=0; i<tbl->nTables; i++)
235 char *relname = get_rel_name(tbl->tables[i]);
236 Oid nspOid = get_rel_namespace(tbl->tables[i]);
237 char *nspname = get_namespace_name(nspOid);
239 if ( relname == NULL || nspOid == InvalidOid || nspname == NULL )
242 ptr += snprintf(ptr, len - (ptr - val), "%s%s.%s",
251 excludeTablesShow(void)
253 return tableListShow(&excludeTables);
257 includeTablesShow(void)
259 return tableListShow(&includeTables);
263 matchOid(TableList *tbl, Oid oid)
265 Oid *StopLow = tbl->tables,
266 *StopHigh = tbl->tables + tbl->nTables,
269 /* Loop invariant: StopLow <= val < StopHigh */
270 while (StopLow < StopHigh)
272 StopMiddle = StopLow + ((StopHigh - StopLow) >> 1);
274 if (*StopMiddle == oid)
276 else if (*StopMiddle < oid)
277 StopLow = StopMiddle + 1;
279 StopHigh = StopMiddle;
285 static ExecutorEnd_hook_type oldhook = NULL;
288 makeAnalyze(Oid relOid, CmdType operation, uint32 naffected)
290 PgStat_StatTabEntry *tabentry;
291 TimestampTz now = GetCurrentTimestamp();
293 if (relOid == InvalidOid)
296 tabentry = pgstat_fetch_stat_tabentry(relOid);
298 #if PG_VERSION_NUM >= 90000
299 #define changes_since_analyze(t) ((t)->changes_since_analyze)
301 #define changes_since_analyze(t) ((t)->n_live_tuples + (t)->n_dead_tuples - (t)->last_anl_tuples)
305 tabentry == NULL /* a new table */ ||
307 /* do not analyze too often, if both stamps are exceeded the go */
308 TimestampDifferenceExceeds(tabentry->analyze_timestamp, now, online_analyze_min_interval) &&
309 TimestampDifferenceExceeds(tabentry->autovac_analyze_timestamp, now, online_analyze_min_interval) &&
310 /* be in sync with relation_needs_vacanalyze */
311 ((double)(changes_since_analyze(tabentry) + naffected)) >=
312 online_analyze_scale_factor * ((double)(tabentry->n_dead_tuples + tabentry->n_live_tuples)) +
313 (double)online_analyze_threshold
318 TimestampTz startStamp, endStamp;
321 * includeTables overwrites excludeTables
323 switch(online_analyze_table_type)
326 if (matchOid(&excludeTables, relOid) == true && matchOid(&includeTables, relOid) == false)
330 if (matchOid(&includeTables, relOid) == false)
334 case OATT_PERSISTENT:
338 OnlyneAnalyzeTableType reltype;
340 rel = RelationIdGetRelation(relOid);
342 #if PG_VERSION_NUM >= 90100
343 (rel->rd_rel->relpersistence == RELPERSISTENCE_TEMP)
345 (rel->rd_istemp || rel->rd_islocaltemp)
347 ? OATT_TEMPORARY : OATT_PERSISTENT;
351 * skip analyze if relation's type doesn't not match online_analyze_table_type
353 if ((online_analyze_table_type & reltype) == 0 || matchOid(&excludeTables, relOid) == true)
355 if (matchOid(&includeTables, relOid) == false)
362 vacstmt.type = T_VacuumStmt;
363 vacstmt.freeze_min_age = -1;
364 vacstmt.freeze_table_age = -1; /* ??? */
365 vacstmt.relation = NULL;
366 vacstmt.va_cols = NIL;
368 #if PG_VERSION_NUM >= 90000
369 vacstmt.options = VACOPT_ANALYZE;
370 if (online_analyze_verbose)
371 vacstmt.options |= VACOPT_VERBOSE;
373 vacstmt.vacuum = vacstmt.full = false;
374 vacstmt.analyze = true;
375 vacstmt.verbose = online_analyze_verbose;
378 if (online_analyze_verbose)
379 startStamp = GetCurrentTimestamp();
381 analyze_rel(relOid, &vacstmt, GetAccessStrategy(BAS_VACUUM)
382 #if (PG_VERSION_NUM < 90004) && (PG_VERSION_NUM >= 90000)
387 if (online_analyze_verbose)
392 endStamp = GetCurrentTimestamp();
393 TimestampDifference(startStamp, endStamp, &secs, µsecs);
394 elog(INFO, "analyze \"%s\" took %.02f seconds",
395 get_rel_name(relOid), ((double)secs) + ((double)microsecs)/1.0e6);
399 if (tabentry == NULL)
402 pgstat_clear_snapshot();
406 /* update last analyze timestamp in local memory of backend */
407 tabentry->analyze_timestamp = now;
410 #if PG_VERSION_NUM >= 90000
411 else if (tabentry != NULL)
413 tabentry->changes_since_analyze += naffected;
418 extern PGDLLIMPORT void onlineAnalyzeHooker(QueryDesc *queryDesc);
420 onlineAnalyzeHooker(QueryDesc *queryDesc)
422 uint32 naffected = 0;
424 if (queryDesc->estate)
425 naffected = queryDesc->estate->es_processed;
427 if (online_analyze_enable && queryDesc->plannedstmt &&
428 (queryDesc->operation == CMD_INSERT ||
429 queryDesc->operation == CMD_UPDATE ||
430 queryDesc->operation == CMD_DELETE ||
431 (queryDesc->operation == CMD_SELECT && queryDesc->plannedstmt->intoClause)))
433 if (queryDesc->plannedstmt->intoClause)
435 Oid relOid = RangeVarGetRelid(queryDesc->plannedstmt->intoClause->rel, true);
437 makeAnalyze(relOid, queryDesc->operation, naffected);
439 else if (queryDesc->plannedstmt->resultRelations &&
440 queryDesc->plannedstmt->rtable)
444 foreach(l, queryDesc->plannedstmt->resultRelations)
446 int n = lfirst_int(l);
447 RangeTblEntry *rte = list_nth(queryDesc->plannedstmt->rtable, n-1);
449 if (rte->rtekind == RTE_RELATION)
450 makeAnalyze(rte->relid, queryDesc->operation, naffected);
456 (*oldhook)(queryDesc);
458 standard_ExecutorEnd(queryDesc);
465 oldhook = ExecutorEnd_hook;
467 ExecutorEnd_hook = onlineAnalyzeHooker;
469 DefineCustomBoolVariable(
470 "online_analyze.enable",
471 "Enable on-line analyze",
472 "Enables analyze of table directly after insert/update/delete/select into",
473 &online_analyze_enable,
474 #if PG_VERSION_NUM >= 80400
475 online_analyze_enable,
478 #if PG_VERSION_NUM >= 80400
480 #if PG_VERSION_NUM >= 90100
488 DefineCustomBoolVariable(
489 "online_analyze.verbose",
490 "Verbosity of on-line analyze",
491 "Make ANALYZE VERBOSE after table's changes",
492 &online_analyze_verbose,
493 #if PG_VERSION_NUM >= 80400
494 online_analyze_verbose,
497 #if PG_VERSION_NUM >= 80400
499 #if PG_VERSION_NUM >= 90100
507 DefineCustomRealVariable(
508 "online_analyze.scale_factor",
509 "fraction of table size to start on-line analyze",
510 "fraction of table size to start on-line analyze",
511 &online_analyze_scale_factor,
512 #if PG_VERSION_NUM >= 80400
513 online_analyze_scale_factor,
518 #if PG_VERSION_NUM >= 80400
520 #if PG_VERSION_NUM >= 90100
528 DefineCustomIntVariable(
529 "online_analyze.threshold",
530 "min number of row updates before on-line analyze",
531 "min number of row updates before on-line analyze",
532 &online_analyze_threshold,
533 #if PG_VERSION_NUM >= 80400
534 online_analyze_threshold,
539 #if PG_VERSION_NUM >= 80400
541 #if PG_VERSION_NUM >= 90100
549 DefineCustomRealVariable(
550 "online_analyze.min_interval",
551 "minimum time interval between analyze call (in milliseconds)",
552 "minimum time interval between analyze call (in milliseconds)",
553 &online_analyze_scale_factor,
554 #if PG_VERSION_NUM >= 80400
555 online_analyze_min_interval,
560 #if PG_VERSION_NUM >= 80400
562 #if PG_VERSION_NUM >= 90100
570 DefineCustomEnumVariable(
571 "online_analyze.table_type",
572 "Type(s) of table for onlyne analyze: all(default), persistent, temporary, none",
574 &online_analyze_table_type,
575 #if PG_VERSION_NUM >= 80400
576 online_analyze_table_type,
578 online_analyze_table_type_options,
580 #if PG_VERSION_NUM >= 80400
582 #if PG_VERSION_NUM >= 90100
590 DefineCustomStringVariable(
591 "online_analyze.exclude_tables",
592 "List of tables which will not online analyze",
594 &excludeTables.tableStr,
595 #if PG_VERSION_NUM >= 80400
600 #if PG_VERSION_NUM >= 90100
609 DefineCustomStringVariable(
610 "online_analyze.include_tables",
611 "List of tables which will online analyze",
613 &includeTables.tableStr,
614 #if PG_VERSION_NUM >= 80400
619 #if PG_VERSION_NUM >= 90100
633 ExecutorEnd_hook = oldhook;
635 if (excludeTables.tables)
636 free(excludeTables.tables);
637 if (includeTables.tables)
638 free(includeTables.tables);
640 excludeTables.tables = includeTables.tables = NULL;
641 excludeTables.nTables = includeTables.nTables = 0;