ec86901b4e857865074792cebfa79be9d53aca87
[online_analyze.git] / online_analyze.c
1 /*
2  * Copyright (c) 2011 Teodor Sigaev <teodor@sigaev.ru>
3  * All rights reserved.
4  *
5  * Redistribution and use in source and binary forms, with or without
6  * modification, are permitted provided that the following conditions
7  * are met:
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.
16  *
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.
28  */
29
30 #include "postgres.h"
31
32 #include "pgstat.h"
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"
42 #if PG_VERSION_NUM >= 90200
43 #include "catalog/pg_class.h"
44 #include "nodes/primnodes.h"
45 #include "tcop/utility.h"
46 #include "utils/rel.h"
47 #include "utils/relcache.h"
48 #include "utils/timestamp.h"
49 #endif
50
51 #ifdef PG_MODULE_MAGIC
52 PG_MODULE_MAGIC;
53 #endif
54
55 static bool online_analyze_enable = true;
56 static bool online_analyze_verbose = true;
57 static double online_analyze_scale_factor = 0.1;
58 static int online_analyze_threshold = 50;
59 static double online_analyze_min_interval = 10000;
60
61 static ExecutorEnd_hook_type oldExecutorEndHook = NULL;
62 #if PG_VERSION_NUM >= 90200
63 static ProcessUtility_hook_type oldProcessUtilityHook = NULL;
64 #endif
65
66 typedef enum 
67 {
68         OATT_ALL                = 0x03,
69         OATT_PERSISTENT = 0x01,
70         OATT_TEMPORARY  = 0x02,
71         OATT_NONE               = 0x00
72 } OnlineAnalyzeTableType;
73
74 static const struct config_enum_entry online_analyze_table_type_options[] = 
75 {
76         {"all", OATT_ALL, false},
77         {"persistent", OATT_PERSISTENT, false},
78         {"temporary", OATT_TEMPORARY, false},
79         {"none", OATT_NONE, false},
80         {NULL, 0, false},
81 };
82
83 static int online_analyze_table_type = (int)OATT_ALL;
84
85 typedef struct TableList {
86         int             nTables;
87         Oid             *tables;
88         char    *tableStr;
89 } TableList;
90
91 static TableList excludeTables = {0, NULL, NULL};
92 static TableList includeTables = {0, NULL, NULL};
93
94 static int
95 oid_cmp(const void *a, const void *b)
96 {
97         if (*(Oid*)a == *(Oid*)b)
98                 return 0;
99         return (*(Oid*)a > *(Oid*)b) ? 1 : -1;
100 }
101
102 static const char *
103 tableListAssign(const char * newval, bool doit, TableList *tbl)
104 {
105         char       *rawname;
106         List       *namelist;
107         ListCell   *l;
108         Oid         *newOids = NULL;
109         int         nOids = 0,
110                                 i = 0;
111
112         rawname = pstrdup(newval);
113
114         if (!SplitIdentifierString(rawname, ',', &namelist))
115                 goto cleanup;
116
117         if (doit)
118         {
119                 nOids = list_length(namelist);
120                 newOids = malloc(sizeof(Oid) * (nOids+1));
121                 if (!newOids)
122                         elog(ERROR,"could not allocate %d bytes", (int)(sizeof(Oid) * (nOids+1)));
123         }
124
125         foreach(l, namelist)
126         {
127                 char        *curname = (char *) lfirst(l);
128 #if PG_VERSION_NUM >= 90200
129                 Oid         relOid = RangeVarGetRelid(makeRangeVarFromNameList(stringToQualifiedNameList(curname)), 
130                                                                                                 NoLock, true);
131 #else
132                 Oid         relOid = RangeVarGetRelid(makeRangeVarFromNameList(stringToQualifiedNameList(curname)), 
133                                                                                                 true);
134 #endif
135
136                 if (relOid == InvalidOid)
137                 {
138 #if PG_VERSION_NUM >= 90100
139                         if (doit == false)
140 #endif
141                         elog(WARNING,"'%s' does not exist", curname);
142                         continue;
143                 }
144                 else if ( get_rel_relkind(relOid) != RELKIND_RELATION )
145                 {
146 #if PG_VERSION_NUM >= 90100
147                         if (doit == false)
148 #endif
149                                 elog(WARNING,"'%s' is not an table", curname);
150                         continue;
151                 }
152                 else if (doit)
153                 {
154                         newOids[i++] = relOid;
155                 }
156         }
157
158         if (doit)
159         {
160                 tbl->nTables = i;
161                 if (tbl->tables)
162                         free(tbl->tables);
163                 tbl->tables = newOids;
164                 if (tbl->nTables > 1)
165                         qsort(tbl->tables, tbl->nTables, sizeof(tbl->tables[0]), oid_cmp);
166         }
167
168         pfree(rawname);
169         list_free(namelist);
170
171         return newval;
172
173 cleanup:
174         if (newOids)
175                 free(newOids);
176         pfree(rawname);
177         list_free(namelist);
178         return NULL;
179 }
180
181 #if PG_VERSION_NUM >= 90100
182 static bool
183 excludeTablesCheck(char **newval, void **extra, GucSource source)
184 {
185         char *val;
186
187         val = (char*)tableListAssign(*newval, false, &excludeTables);
188
189         if (val)
190         {
191                 *newval = val;
192                 return true;
193         }
194
195         return false;
196 }
197
198 static void
199 excludeTablesAssign(const char *newval, void *extra)
200 {
201         tableListAssign(newval, true, &excludeTables);
202 }
203
204 static bool
205 includeTablesCheck(char **newval, void **extra, GucSource source)
206 {
207         char *val;
208
209         val = (char*)tableListAssign(*newval, false, &includeTables);
210
211         if (val)
212         {
213                 *newval = val;
214                 return true;
215         }
216
217         return false;
218 }
219
220 static void
221 includeTablesAssign(const char *newval, void *extra)
222 {
223         tableListAssign(newval, true, &excludeTables);
224 }
225
226 #else /* PG_VERSION_NUM < 90100 */ 
227
228 static const char *
229 excludeTablesAssign(const char * newval, bool doit, GucSource source)
230 {
231         return tableListAssign(newval, doit, &excludeTables);
232 }
233
234 static const char *
235 includeTablesAssign(const char * newval, bool doit, GucSource source)
236 {
237         return tableListAssign(newval, doit, &includeTables);
238 }
239
240 #endif
241
242 static const char*
243 tableListShow(TableList *tbl)
244 {
245         char    *val, *ptr;
246         int     i,
247                         len;
248
249         len = 1 /* \0 */ + tbl->nTables * (2 * NAMEDATALEN + 2 /* ', ' */ + 1 /* . */);
250         ptr = val = palloc(len);
251         *ptr ='\0';
252         for(i=0; i<tbl->nTables; i++)
253         {
254                 char    *relname = get_rel_name(tbl->tables[i]);
255                 Oid     nspOid = get_rel_namespace(tbl->tables[i]);
256                 char    *nspname = get_namespace_name(nspOid);
257
258                 if ( relname == NULL || nspOid == InvalidOid || nspname == NULL )
259                         continue;
260
261                 ptr += snprintf(ptr, len - (ptr - val), "%s%s.%s",
262                                                                                                         (i==0) ? "" : ", ",
263                                                                                                         nspname, relname);
264         }
265
266         return val;
267 }
268
269 static const char*
270 excludeTablesShow(void)
271 {
272         return tableListShow(&excludeTables);
273 }
274
275 static const char*
276 includeTablesShow(void)
277 {
278         return tableListShow(&includeTables);
279 }
280
281 static bool
282 matchOid(TableList *tbl, Oid oid)
283 {
284         Oid     *StopLow = tbl->tables,
285                 *StopHigh = tbl->tables + tbl->nTables,
286                 *StopMiddle;
287
288         /* Loop invariant: StopLow <= val < StopHigh */
289         while (StopLow < StopHigh)
290         {
291                 StopMiddle = StopLow + ((StopHigh - StopLow) >> 1);
292
293                 if (*StopMiddle == oid)
294                         return true;
295                 else  if (*StopMiddle < oid)
296                         StopLow = StopMiddle + 1;
297                 else
298                         StopHigh = StopMiddle;
299         }
300
301         return false;
302 }
303
304 static void
305 makeAnalyze(Oid relOid, CmdType operation, uint32 naffected)
306 {
307         PgStat_StatTabEntry             *tabentry;
308         TimestampTz                     now = GetCurrentTimestamp();
309
310         if (relOid == InvalidOid)
311                 return;
312
313         if (get_rel_relkind(relOid) != RELKIND_RELATION)
314                 return;
315
316         tabentry = pgstat_fetch_stat_tabentry(relOid);
317
318 #if PG_VERSION_NUM >= 90000
319 #define changes_since_analyze(t)        ((t)->changes_since_analyze)
320 #else
321 #define changes_since_analyze(t)        ((t)->n_live_tuples + (t)->n_dead_tuples - (t)->last_anl_tuples)
322 #endif
323
324         if (    
325                 tabentry == NULL /* a new table */ ||
326                 (
327                         /* do not analyze too often, if both stamps are exceeded the go */
328                         TimestampDifferenceExceeds(tabentry->analyze_timestamp, now, online_analyze_min_interval) && 
329                         TimestampDifferenceExceeds(tabentry->autovac_analyze_timestamp, now, online_analyze_min_interval) &&
330                         /* be in sync with relation_needs_vacanalyze */
331                         ((double)(changes_since_analyze(tabentry) + naffected)) >=
332                                 online_analyze_scale_factor * ((double)(tabentry->n_dead_tuples + tabentry->n_live_tuples)) + 
333                                         (double)online_analyze_threshold
334                 )
335         )
336         {
337                 VacuumStmt                              vacstmt;
338                 TimestampTz                             startStamp, endStamp;
339
340                 memset(&startStamp, 0, sizeof(startStamp)); /* keep compiler quiet */
341
342                 /*
343                  * includeTables overwrites excludeTables
344                  */
345                 switch(online_analyze_table_type)
346                 {
347                         case OATT_ALL:
348                                 if (matchOid(&excludeTables, relOid) == true && matchOid(&includeTables, relOid) == false)
349                                         return;
350                                 break;
351                         case OATT_NONE:
352                                 if (matchOid(&includeTables, relOid) == false)
353                                         return;
354                                 break;
355                         case OATT_TEMPORARY:
356                         case OATT_PERSISTENT:
357                         default:
358                                 {
359                                         Relation                                rel;
360                                         OnlineAnalyzeTableType  reltype;
361
362                                         rel = RelationIdGetRelation(relOid);
363                                         reltype = 
364 #if PG_VERSION_NUM >= 90100
365                                                 (rel->rd_rel->relpersistence == RELPERSISTENCE_TEMP)
366 #else
367                                                 (rel->rd_istemp || rel->rd_islocaltemp)
368 #endif
369                                                         ? OATT_TEMPORARY : OATT_PERSISTENT;
370                                         RelationClose(rel);
371
372                                         /*
373                                          * skip analyze if relation's type doesn't not match online_analyze_table_type
374                                          */
375                                         if ((online_analyze_table_type & reltype) == 0 || matchOid(&excludeTables, relOid) == true)
376                                         {
377                                                 if (matchOid(&includeTables, relOid) == false)
378                                                         return;
379                                         }
380                                 }
381                                 break;
382                 }
383
384                 vacstmt.type = T_VacuumStmt;
385                 vacstmt.freeze_min_age = -1;
386                 vacstmt.freeze_table_age = -1; /* ??? */
387                 vacstmt.relation = NULL;
388                 vacstmt.va_cols = NIL;
389
390 #if PG_VERSION_NUM >= 90000
391                 vacstmt.options = VACOPT_ANALYZE;
392                 if (online_analyze_verbose)
393                         vacstmt.options |= VACOPT_VERBOSE;
394 #else
395                 vacstmt.vacuum = vacstmt.full = false;
396                 vacstmt.analyze = true;
397                 vacstmt.verbose = online_analyze_verbose;
398 #endif
399
400                 if (online_analyze_verbose)
401                         startStamp = GetCurrentTimestamp();
402
403                 analyze_rel(relOid, &vacstmt, GetAccessStrategy(BAS_VACUUM)
404 #if (PG_VERSION_NUM < 90004) && (PG_VERSION_NUM >= 90000)
405                         , true
406 #endif
407                 );
408
409                 if (online_analyze_verbose)
410                 {
411                         long    secs;
412                         int             microsecs;
413
414                         endStamp = GetCurrentTimestamp();
415                         TimestampDifference(startStamp, endStamp, &secs, &microsecs);
416                         elog(INFO, "analyze \"%s\" took %.02f seconds", 
417                                 get_rel_name(relOid), ((double)secs) + ((double)microsecs)/1.0e6);
418                 }
419
420
421                 if (tabentry == NULL)
422                 {
423                         /* new table */
424                         pgstat_clear_snapshot();
425                 }
426                 else
427                 {
428                         /* update last analyze timestamp in local memory of backend */
429                         tabentry->analyze_timestamp = now;
430                 }
431         }
432 #if PG_VERSION_NUM >= 90000
433         else if (tabentry != NULL)
434         {
435                 tabentry->changes_since_analyze += naffected;
436         }
437 #endif
438 }
439
440 extern PGDLLIMPORT void onlineAnalyzeHooker(QueryDesc *queryDesc);
441 void
442 onlineAnalyzeHooker(QueryDesc *queryDesc) 
443 {
444         uint32  naffected = 0;
445
446         if (queryDesc->estate)
447                 naffected = queryDesc->estate->es_processed;    
448
449         if (online_analyze_enable && queryDesc->plannedstmt &&
450                         (queryDesc->operation == CMD_INSERT || 
451                          queryDesc->operation == CMD_UPDATE ||
452                          queryDesc->operation == CMD_DELETE
453 #if PG_VERSION_NUM < 90200
454                          || (queryDesc->operation == CMD_SELECT && queryDesc->plannedstmt->intoClause)
455 #endif
456                          ))
457         {
458 #if PG_VERSION_NUM < 90200
459                 if (queryDesc->operation == CMD_SELECT)
460                 {
461                         Oid     relOid = RangeVarGetRelid(queryDesc->plannedstmt->intoClause->rel, true);
462
463                         makeAnalyze(relOid, queryDesc->operation, naffected);
464                 }
465                 else 
466 #endif
467                 if (queryDesc->plannedstmt->resultRelations &&
468                                  queryDesc->plannedstmt->rtable)
469                 {
470                         ListCell        *l;
471
472                         foreach(l, queryDesc->plannedstmt->resultRelations)
473                         {
474                                 int                     n = lfirst_int(l);
475                                 RangeTblEntry   *rte = list_nth(queryDesc->plannedstmt->rtable, n-1);
476                 
477                                 if (rte->rtekind == RTE_RELATION)
478                                         makeAnalyze(rte->relid, queryDesc->operation, naffected);
479                         }
480                 }
481         }
482
483         if (oldExecutorEndHook)
484                 oldExecutorEndHook(queryDesc);
485         else
486                 standard_ExecutorEnd(queryDesc);
487 }
488
489 #if PG_VERSION_NUM >= 90200
490 static void
491 onlineAnalyzeHookerUtility(Node *parsetree, const char *queryString, 
492                                                         ParamListInfo params, bool isTopLevel,
493                                                         DestReceiver *dest, char *completionTag) {
494         RangeVar        *tblname = NULL;
495
496         if (IsA(parsetree, CreateTableAsStmt) && ((CreateTableAsStmt*)parsetree)->into)
497                 tblname = (RangeVar*)copyObject(((CreateTableAsStmt*)parsetree)->into->rel);
498
499         if (oldProcessUtilityHook)
500                 oldProcessUtilityHook(parsetree, queryString, params, isTopLevel, dest, completionTag);
501         else
502                 standard_ProcessUtility(parsetree, queryString, params, isTopLevel, dest, completionTag);
503
504         if (tblname) {
505                 Oid     tblOid = RangeVarGetRelid(tblname, NoLock, true);
506
507                 makeAnalyze(tblOid, CMD_INSERT, 0); 
508         }
509 }
510 #endif
511
512 void _PG_init(void);
513 void
514 _PG_init(void)
515 {
516         oldExecutorEndHook = ExecutorEnd_hook;
517
518         ExecutorEnd_hook = onlineAnalyzeHooker;
519
520 #if PG_VERSION_NUM >= 90200
521         oldProcessUtilityHook = ProcessUtility_hook;
522
523         ProcessUtility_hook = onlineAnalyzeHookerUtility;
524 #endif
525
526
527         DefineCustomBoolVariable(
528                 "online_analyze.enable",
529                 "Enable on-line analyze",
530                 "Enables analyze of table directly after insert/update/delete/select into",
531                 &online_analyze_enable,
532 #if PG_VERSION_NUM >= 80400
533                 online_analyze_enable,
534 #endif
535                 PGC_USERSET,
536 #if PG_VERSION_NUM >= 80400
537                 GUC_NOT_IN_SAMPLE,
538 #if PG_VERSION_NUM >= 90100
539                 NULL,
540 #endif
541 #endif
542                 NULL,
543                 NULL
544         );
545
546         DefineCustomBoolVariable(
547                 "online_analyze.verbose",
548                 "Verbosity of on-line analyze",
549                 "Make ANALYZE VERBOSE after table's changes",
550                 &online_analyze_verbose,
551 #if PG_VERSION_NUM >= 80400
552                 online_analyze_verbose,
553 #endif
554                 PGC_USERSET,
555 #if PG_VERSION_NUM >= 80400
556                 GUC_NOT_IN_SAMPLE,
557 #if PG_VERSION_NUM >= 90100
558                 NULL,
559 #endif
560 #endif
561                 NULL,
562                 NULL
563         );
564
565     DefineCustomRealVariable(
566                 "online_analyze.scale_factor",
567                 "fraction of table size to start on-line analyze",
568                 "fraction of table size to start on-line analyze",
569                 &online_analyze_scale_factor,
570 #if PG_VERSION_NUM >= 80400
571                 online_analyze_scale_factor,
572 #endif
573                 0.0,
574                 1.0,
575                 PGC_USERSET,
576 #if PG_VERSION_NUM >= 80400
577                 GUC_NOT_IN_SAMPLE,
578 #if PG_VERSION_NUM >= 90100
579                 NULL,
580 #endif
581 #endif
582                 NULL,
583                 NULL
584         );
585
586     DefineCustomIntVariable(
587                 "online_analyze.threshold",
588                 "min number of row updates before on-line analyze",
589                 "min number of row updates before on-line analyze",
590                 &online_analyze_threshold,
591 #if PG_VERSION_NUM >= 80400
592                 online_analyze_threshold,
593 #endif
594                 0,
595                 0x7fffffff,
596                 PGC_USERSET,
597 #if PG_VERSION_NUM >= 80400
598                 GUC_NOT_IN_SAMPLE,
599 #if PG_VERSION_NUM >= 90100
600                 NULL,
601 #endif
602 #endif
603                 NULL,
604                 NULL
605         );
606
607     DefineCustomRealVariable(
608                 "online_analyze.min_interval",
609                 "minimum time interval between analyze call (in milliseconds)",
610                 "minimum time interval between analyze call (in milliseconds)",
611                 &online_analyze_scale_factor,
612 #if PG_VERSION_NUM >= 80400
613                 online_analyze_min_interval,
614 #endif
615                 0.0,
616                 1e30,
617                 PGC_USERSET,
618 #if PG_VERSION_NUM >= 80400
619                 GUC_NOT_IN_SAMPLE,
620 #if PG_VERSION_NUM >= 90100
621                 NULL,
622 #endif
623 #endif
624                 NULL,
625                 NULL
626         );
627
628         DefineCustomEnumVariable(
629                 "online_analyze.table_type",
630                 "Type(s) of table for online analyze: all(default), persistent, temporary, none",
631                 NULL,
632                 &online_analyze_table_type,
633 #if PG_VERSION_NUM >= 80400
634                 online_analyze_table_type,
635 #endif
636                 online_analyze_table_type_options,
637                 PGC_USERSET,
638 #if PG_VERSION_NUM >= 80400
639         GUC_NOT_IN_SAMPLE,
640 #if PG_VERSION_NUM >= 90100
641                 NULL,
642 #endif
643 #endif
644                 NULL,
645                 NULL
646         );
647
648     DefineCustomStringVariable(
649                 "online_analyze.exclude_tables",
650                 "List of tables which will not online analyze",
651                 NULL,
652                 &excludeTables.tableStr,
653 #if PG_VERSION_NUM >= 80400
654                 "",
655 #endif
656                 PGC_USERSET,
657                 0,
658 #if PG_VERSION_NUM >= 90100
659                 excludeTablesCheck,
660                 excludeTablesAssign,
661 #else
662                 excludeTablesAssign,
663 #endif
664                 excludeTablesShow
665         );
666
667     DefineCustomStringVariable(
668                 "online_analyze.include_tables",
669                 "List of tables which will online analyze",
670                 NULL,
671                 &includeTables.tableStr,
672 #if PG_VERSION_NUM >= 80400
673                 "",
674 #endif
675                 PGC_USERSET,
676                 0,
677 #if PG_VERSION_NUM >= 90100
678                 includeTablesCheck,
679                 includeTablesAssign,
680 #else
681                 includeTablesAssign,
682 #endif
683                 includeTablesShow
684         );
685 }
686
687 void _PG_fini(void);
688 void
689 _PG_fini(void)
690 {
691         ExecutorEnd_hook = oldExecutorEndHook;
692 #if PG_VERSION_NUM >= 90200
693         ProcessUtility_hook = oldProcessUtilityHook;
694 #endif
695
696         if (excludeTables.tables)
697                 free(excludeTables.tables);
698         if (includeTables.tables)
699                 free(includeTables.tables);
700
701         excludeTables.tables = includeTables.tables = NULL;
702         excludeTables.nTables = includeTables.nTables = 0;
703 }