online_analyze.c

   1 #include "postgres.h"
   2
   3 #include "pgstat.h"
   4 #include "catalog/namespace.h"
   5 #include "commands/vacuum.h"
   6 #include "executor/executor.h"
   7 #include "nodes/nodes.h"
   8 #include "nodes/parsenodes.h"
   9 #include "storage/bufmgr.h"
  10 #include "utils/lsyscache.h"
  11 #include "utils/guc.h"
  12
  13 #ifdef PG_MODULE_MAGIC
  14 PG_MODULE_MAGIC;
  15 #endif
  16
  17 static bool online_analyze_enable = true;
  18 static bool online_analyze_verbose = true;
  19 static double online_analyze_scale_factor = 0.1;
  20 static int online_analyze_threshold = 50;
  21 static double online_analyze_min_interval = 10000;
  22
  23 static ExecutorEnd_hook_type oldhook = NULL;
  24
  25 static void
  26 makeAnalyze(Oid relOid, CmdType operation, uint32 naffected)
  27 {
  28         PgStat_StatTabEntry             *tabentry;
  29         TimestampTz                     now = GetCurrentTimestamp();
  30
  31         if (relOid == InvalidOid)
  32                 return;
  33
  34         tabentry = pgstat_fetch_stat_tabentry(relOid);
  35
  36 #if PG_VERSION_NUM >= 90000
  37 #define changes_since_analyze(t)        ((t)->changes_since_analyze)
  38 #else
  39 #define changes_since_analyze(t)        ((t)->n_live_tuples + (t)->n_dead_tuples - (t)->last_anl_tuples)
  40 #endif
  41
  42         if (
  43                 tabentry == NULL /* a new table */ ||
  44                 (
  45                         /* do not analyze too often, if both stamps are exceeded the go */
  46                         TimestampDifferenceExceeds(tabentry->analyze_timestamp, now, online_analyze_min_interval) &&
  47                         TimestampDifferenceExceeds(tabentry->autovac_analyze_timestamp, now, online_analyze_min_interval) &&
  48                         /* be in sync with relation_needs_vacanalyze */
  49                         ((double)(changes_since_analyze(tabentry) + naffected)) >=
  50                                 online_analyze_scale_factor * ((double)(tabentry->n_dead_tuples + tabentry->n_live_tuples)) +
  51                                         (double)online_analyze_threshold
  52                 )
  53         )
  54         {
  55                 VacuumStmt                              vacstmt;
  56                 TimestampTz                             startStamp, endStamp;
  57
  58                 vacstmt.type = T_VacuumStmt;
  59                 vacstmt.freeze_min_age = -1;
  60                 vacstmt.freeze_table_age = -1; /* ??? */
  61                 vacstmt.relation = NULL;
  62                 vacstmt.va_cols = NIL;
  63
  64 #if PG_VERSION_NUM >= 90000
  65                 vacstmt.options = VACOPT_ANALYZE;
  66                 if (online_analyze_verbose)
  67                         vacstmt.options |= VACOPT_VERBOSE;
  68 #else
  69                 vacstmt.vacuum = vacstmt.full = false;
  70                 vacstmt.analyze = true;
  71                 vacstmt.verbose = online_analyze_verbose;
  72 #endif
  73
  74                 if (online_analyze_verbose)
  75                         startStamp = GetCurrentTimestamp();
  76
  77                 analyze_rel(relOid, &vacstmt, GetAccessStrategy(BAS_VACUUM)
  78 #if (PG_VERSION_NUM < 90004) && (PG_VERSION_NUM >= 90000)
  79                         , true
  80 #endif
  81                 );
  82
  83                 if (online_analyze_verbose)
  84                 {
  85                         long    secs;
  86                         int             microsecs;
  87
  88                         endStamp = GetCurrentTimestamp();
  89                         TimestampDifference(startStamp, endStamp, &secs, &microsecs);
  90                         elog(INFO, "analyze \"%s\" took %.02f seconds", get_rel_name(relOid), ((double)secs) + ((double)microsecs)/1.0e6);
  91                 }
  92
  93
  94                 if (tabentry == NULL)
  95                 {
  96                         /* new table */
  97                         pgstat_clear_snapshot();
  98                 }
  99                 else
 100                 {
 101                         /* update last analyze timestamp in local memory of backend */
 102                         tabentry->analyze_timestamp = now;
 103                 }
 104         }
 105 #if PG_VERSION_NUM >= 90000
 106         else if (tabentry != NULL)
 107         {
 108                 tabentry->changes_since_analyze += naffected;
 109         }
 110 #endif
 111 }
 112
 113 extern PGDLLIMPORT void onlineAnalyzeHooker(QueryDesc *queryDesc);
 114 void
 115 onlineAnalyzeHooker(QueryDesc *queryDesc)
 116 {
 117         uint32  naffected = 0;
 118
 119         if (queryDesc->estate)
 120                 naffected = queryDesc->estate->es_processed;
 121
 122         if (online_analyze_enable && queryDesc->plannedstmt &&
 123                         (queryDesc->operation == CMD_INSERT ||
 124                          queryDesc->operation == CMD_UPDATE ||
 125                          queryDesc->operation == CMD_DELETE ||
 126                          (queryDesc->operation == CMD_SELECT && queryDesc->plannedstmt->intoClause)))
 127         {
 128                 if (queryDesc->plannedstmt->intoClause)
 129                 {
 130                         Oid     relOid = RangeVarGetRelid(queryDesc->plannedstmt->intoClause->rel, true);
 131
 132                         makeAnalyze(relOid, queryDesc->operation, naffected);
 133                 }
 134                 else if (queryDesc->plannedstmt->resultRelations &&
 135                                  queryDesc->plannedstmt->rtable)
 136                 {
 137                         ListCell        *l;
 138
 139                         foreach(l, queryDesc->plannedstmt->resultRelations)
 140                         {
 141                                 int                     n = lfirst_int(l);
 142                                 RangeTblEntry   *rte = list_nth(queryDesc->plannedstmt->rtable, n-1);
 143
 144                                 if (rte->rtekind == RTE_RELATION)
 145                                         makeAnalyze(rte->relid, queryDesc->operation, naffected);
 146                         }
 147                 }
 148         }
 149
 150         if (oldhook)
 151                 (*oldhook)(queryDesc);
 152         else
 153                 standard_ExecutorEnd(queryDesc);
 154 }
 155
 156 void _PG_init(void);
 157 void
 158 _PG_init(void)
 159 {
 160         oldhook = ExecutorEnd_hook;
 161
 162         ExecutorEnd_hook = onlineAnalyzeHooker;
 163
 164         DefineCustomBoolVariable(
 165                 "online_analyze.enable",
 166                 "Enable on-line analyze",
 167                 "Enables analyze of table directly after insert/update/delete/select into",
 168                 &online_analyze_enable,
 169 #if PG_VERSION_NUM >= 80400
 170                 online_analyze_enable,
 171 #endif
 172                 PGC_USERSET,
 173 #if PG_VERSION_NUM >= 80400
 174                 GUC_NOT_IN_SAMPLE,
 175 #if PG_VERSION_NUM >= 90100
 176                 NULL,
 177 #endif
 178 #endif
 179                 NULL,
 180                 NULL
 181         );
 182
 183         DefineCustomBoolVariable(
 184                 "online_analyze.verbose",
 185                 "Verbosity of on-line analyze",
 186                 "Make ANALYZE VERBOSE after table's changes",
 187                 &online_analyze_verbose,
 188 #if PG_VERSION_NUM >= 80400
 189                 online_analyze_verbose,
 190 #endif
 191                 PGC_USERSET,
 192 #if PG_VERSION_NUM >= 80400
 193                 GUC_NOT_IN_SAMPLE,
 194 #if PG_VERSION_NUM >= 90100
 195                 NULL,
 196 #endif
 197 #endif
 198                 NULL,
 199                 NULL
 200         );
 201
 202     DefineCustomRealVariable(
 203                 "online_analyze.scale_factor",
 204                 "fraction of table size to start on-line analyze",
 205                 "fraction of table size to start on-line analyze",
 206                 &online_analyze_scale_factor,
 207 #if PG_VERSION_NUM >= 80400
 208                 online_analyze_scale_factor,
 209 #endif
 210                 0.0,
 211                 1.0,
 212                 PGC_USERSET,
 213 #if PG_VERSION_NUM >= 80400
 214                 GUC_NOT_IN_SAMPLE,
 215 #if PG_VERSION_NUM >= 90100
 216                 NULL,
 217 #endif
 218 #endif
 219                 NULL,
 220                 NULL
 221         );
 222
 223     DefineCustomIntVariable(
 224                 "online_analyze.threshold",
 225                 "min number of row updates before on-line analyze",
 226                 "min number of row updates before on-line analyze",
 227                 &online_analyze_threshold,
 228 #if PG_VERSION_NUM >= 80400
 229                 online_analyze_threshold,
 230 #endif
 231                 0,
 232                 0x7fffffff,
 233                 PGC_USERSET,
 234 #if PG_VERSION_NUM >= 80400
 235                 GUC_NOT_IN_SAMPLE,
 236 #if PG_VERSION_NUM >= 90100
 237                 NULL,
 238 #endif
 239 #endif
 240                 NULL,
 241                 NULL
 242         );
 243
 244     DefineCustomRealVariable(
 245                 "online_analyze.min_interval",
 246                 "minimum time interval between analyze call (in milliseconds)",
 247                 "minimum time interval between analyze call (in milliseconds)",
 248                 &online_analyze_scale_factor,
 249 #if PG_VERSION_NUM >= 80400
 250                 online_analyze_min_interval,
 251 #endif
 252                 0.0,
 253                 1e30,
 254                 PGC_USERSET,
 255 #if PG_VERSION_NUM >= 80400
 256                 GUC_NOT_IN_SAMPLE,
 257 #if PG_VERSION_NUM >= 90100
 258                 NULL,
 259 #endif
 260 #endif
 261                 NULL,
 262                 NULL
 263         );
 264
 265 }
 266
 267 void _PG_fini(void);
 268 void
 269 _PG_fini(void)
 270 {
 271         ExecutorEnd_hook = oldhook;
 272 }