forked from ossc-db/pg_plan_advsr
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathpg_plan_advsr.c
2222 lines (1899 loc) · 62.6 KB
/
pg_plan_advsr.c
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
/*-------------------------------------------------------------------------
*
* pg_plan_advsr.c
* Automatic plan tuning by correcting row count estimation errors
* using a feedback loop between planner and executor.
*
* Copyright (c) 2019-2024, NIPPON TELEGRAPH AND TELEPHONE CORPORATION
*
* IDENTIFICATION
* pg_plan_advsr.c
*
*-------------------------------------------------------------------------
*/
#include "postgres.h"
#include "parser/analyze.h"
#include "parser/parsetree.h"
#include "executor/executor.h"
#include "tcop/utility.h"
#include "nodes/nodeFuncs.h"
#include "nodes/extensible.h"
#include "commands/explain.h"
#include "commands/prepare.h"
#include "common/md5.h"
#include "mb/pg_wchar.h"
#include "miscadmin.h"
#include "utils/builtins.h"
#include "utils/ruleutils.h"
#include "access/hash.h"
#include "utils/lsyscache.h"
#include "catalog/namespace.h"
#include "utils/builtins.h"
#include "access/htup_details.h"
#include "catalog/indexing.h"
#include "access/xact.h"
#include "utils/varlena.h"
#include "catalog/pg_extension.h"
#include "utils/fmgroids.h"
#include "optimizer/cost.h"
#include "libpq-int.h"
#if PG_VERSION_NUM >= 110000
#include "utils/rel.h"
#endif /* PG_VERSION_NUM */
#if PG_VERSION_NUM >= 120000
#include "access/table.h"
#include "optimizer/optimizer.h"
#endif /* PG_VERSION_NUM */
/* came from pg_hint_plan */
#include "normalize_query.h"
/* came from pg_store_plans */
#include "pgsp_json.h"
/* "table_open/close" ware "heap_open/close" before PG12 */
#if PG_VERSION_NUM < 120000
#define table_open(relationId, lockmode) heap_open(relationId, lockmode)
#define table_close(relation, lockmode) heap_close(relation, lockmode)
#endif /* PG_VERSION_NUM */
PG_MODULE_MAGIC;
bool isExplain;
static StringInfo explain_query;
/* for leading hint */
typedef struct LeadingContext
{
StringInfo lead_str;
ExplainState *es;
} LeadingContext;
/* scan method/join method/leading hints */
static StringInfo scan_str;
static StringInfo join_str;
static StringInfo rows_str;
LeadingContext *leadcxt;
/* In PostgreSQL 11, queryid becomes a uint64 internally. */
#if PG_VERSION_NUM >= 110000
typedef uint64 queryid_t;
#define PGSP_NO_QUERYID UINT64CONST(0)
#else
typedef uint32 queryid_t;
#define PGSP_NO_QUERYID 0
#endif
/*
* For PG13 or below, pgsp_queryid is made by hash_query with Desc->sourceText.
* For PG14 and above, it uses compute_query_id created in core.
*/
static queryid_t pgsp_queryid;
/* hash value made by normalized_plan */
static uint32 pgsp_planid;
/* estimated/actual rows number */
static double est_rows;
static double act_rows;
static double diff_rows_join; /* = act_rows - est_rows */
static double diff_rows_scan;
static double total_diff_rows_join;
static double total_diff_rows_scan;
static double diff_ratio_join;
static double diff_ratio_scan;
static double max_diff_ratio_join;
static double max_diff_ratio_scan;
/* counters */
static int scan_cnt;
static int join_cnt;
static int rows_cnt;
/* application name */
static char *aplname;
/* This is made by generate_normalized_query in post_parse_analyze_hook */
char *normalized_query;
/* Current nesting depth of ExecutorRun+ProcessUtility calls */
static int nested_level = 0;
#define run_pg_plan_advsr() \
(isExplain == true && nested_level == 0 && pg_plan_advsr_is_enabled)
/* GUC variables */
/* enable / disabe pg_plan_advsr */
static bool pg_plan_advsr_is_enabled;
#define pg_plan_advsr_enabled() \
pg_plan_advsr_is_enabled && (nested_level == 0)
/* enable / disable quiet mode */
static bool pg_plan_advsr_is_quieted;
/* enable / disable creating hints evenif query is EXPLAIN without ANALYZE option */
static bool pg_plan_advsr_widely;
/* Saved hook values in case of unload */
static post_parse_analyze_hook_type prev_post_parse_analyze_hook = NULL;
static ProcessUtility_hook_type prev_ProcessUtility_hook = NULL;
static ExplainOneQuery_hook_type prev_ExplainOneQuery_hook = NULL;
static ExecutorStart_hook_type prev_ExecutorStart_hook = NULL;
static ExecutorRun_hook_type prev_ExecutorRun_hook = NULL;
static ExecutorFinish_hook_type prev_ExecutorFinish_hook = NULL;
static ExecutorEnd_hook_type prev_ExecutorEnd_hook = NULL;
void _PG_init(void);
void _PG_fini(void);
PG_FUNCTION_INFO_V1(pg_plan_advsr_enable_feedback);
Datum pg_plan_advsr_enable_feedback(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(pg_plan_advsr_disable_feedback);
Datum pg_plan_advsr_disable_feedback(PG_FUNCTION_ARGS);
/* Hook functions for pg_plan_advsr */
static void pg_plan_advsr_post_parse_analyze_hook(ParseState *pstate, Query *query
#if PG_VERSION_NUM < 140000
);
#else
, JumbleState *jstate);
#endif /* PG_VERSION_NUM */
static void pg_plan_advsr_ProcessUtility_hook(PlannedStmt *pstmt,
const char *queryString,
#if PG_VERSION_NUM >= 140000
bool readOnlyTree,
#endif /* PG_VERSION_NUM */
ProcessUtilityContext context,
ParamListInfo params, QueryEnvironment *queryEnv,
DestReceiver *dest,
#if PG_VERSION_NUM < 130000
char *completionTag);
#else
QueryCompletion *qc);
#endif /* PG_VERSION_NUM */
static void pg_plan_advsr_ExplainOneQuery_hook(Query *query,
int cursorOptions,
IntoClause *into,
ExplainState *es,
const char *queryString,
ParamListInfo params,
QueryEnvironment *queryEnv);
static void pg_plan_advsr_ExecutorStart_hook(QueryDesc *queryDesc, int eflags);
static void pg_plan_advsr_ExecutorRun_hook(QueryDesc *queryDesc, ScanDirection direction,
uint64 count, bool execute_once);
static void pg_plan_advsr_ExecutorFinish_hook(QueryDesc *queryDesc);
static void pg_plan_advsr_ExecutorEnd_hook(QueryDesc *queryDesc);
/* Utility functions */
static bool pg_plan_advsr_query_walker(Node *parsetree
#if PG_VERSION_NUM < 160000
);
#else
,void *context);
#endif /* PG_VERSION_NUM */
#if PG_VERSION_NUM < 140000
/* This function came from pg_hint_plan.c */
static const char *get_query_string(ParseState *pstate, Query *query, Query **jumblequery);
#endif /* PG_VERSION_NUM */
/* entry point of pg_plan_advsr */
void pg_plan_advsr_ExplainPrintPlan(ExplainState *es, QueryDesc *queryDesc);
void CreateScanJoinRowsHints(PlanState *planstate, List *ancestors,
const char *relationship, const char *plan_name,
ExplainState *es);
/* This function called by planstate_tree_walker for creating Leading Hints */
bool CreateLeadingHint(PlanState *planstate, LeadingContext * lead);
void store_info_to_tables(double totaltime, const char *sourcetext); /* store query, hints
* and diff to tables */
/* these functions based on explain.c */
bool ExplainPreScanNode(PlanState *planstate, Bitmapset **rels_used);
bool pg_plan_advsr_planstate_tree_walker(PlanState *planstate,
bool (*walker) (),
void *context);
void pg_plan_advsr_ExplainScanTarget(Scan *plan, ExplainState *es);
void pg_plan_advsr_ExplainTargetRel(Plan *plan, Index rti, ExplainState *es);
char *get_relnames(ExplainState *es, Relids relids);
char *get_target_relname(Index rti, ExplainState *es);
/* inspired from pg_store_plans.c */
uint32 create_pgsp_planid(QueryDesc *queryDesc);
#if PG_VERSION_NUM < 140000
/* came from pg_store_plans.c */
static uint32 hash_query(const char *query);
#endif /* PG_VERSION_NUM */
/* replace all before strings to after strings in buf strings */
void replaceAll(char *buf, const char *before, const char *after);
/* calculate the difference between estimated rows and actual rows */
double get_diff_rows(double est_rows, double act_rows);
double get_diff_ratio(double est_rows, double act_rows);
/* plan_repo.plan_history */
#define Natts_plan_history 17
#define Anum_plan_history_id 1 /* serial */
#define Anum_plan_history_norm_query_hash 2 /* text */
#define Anum_plan_history_pgsp_queryid 3 /* bigint */
#define Anum_plan_history_pgsp_planid 4 /* bigint */
#define Anum_plan_history_execution_time 5 /* double precision */
#define Anum_plan_history_rows_hint 6 /* text */
#define Anum_plan_history_scan_hint 7 /* text */
#define Anum_plan_history_join_hint 8 /* text */
#define Anum_plan_history_lead_hint 9 /* text */
#define Anum_plan_history_diff_of_scans 10 /* double precision */
#define Anum_plan_history_max_diff_ratio_scan 11 /* double precision */
#define Anum_plan_history_diff_of_joins 12 /* double precision */
#define Anum_plan_history_max_diff_ratio_join 13 /* double precision */
#define Anum_plan_history_scan_cnt 14 /* int */
#define Anum_plan_history_join_cnt 15 /* int */
#define Anum_plan_history_application_name 16 /* text */
#define Anum_plan_history_timestamp 17 /* timestamp */
/* plan_repo.norm_queries */
#define Natts_norm_queries 2
#define Anum_norm_queries_norm_query_hash 1 /* text */
#define Anum_norm_queries_norm_query_string 2 /* text */
/* plan_repo.raw_queries */
#define Natts_raw_queries 4
#define Anum_raw_queries_norm_query_hash 1 /* text */
#define Anum_raw_queries_raw_query_id 2 /* serial */
#define Anum_raw_queries_raw_query_string 3 /* text */
#define Anum_raw_queries_timestamp 4 /* timestamp */
/* hint_plan.hints */
#define Natts_hints 4
#define Anum_hints_id 1 /* serial */
#define Anum_hints_norm_query_string 2 /* text */
#define Anum_hints_application_name 3 /* text */
#define Anum_hints_hints 4 /* text */
static Oid extensionOwner(void);
static Oid resolveRelationId(text *relationName, bool missingOk);
static uint64 getNextVal(const char *sequence);
static bool insertPlanHistory(const char *norm_query_hash, const uint64 pgsp_queryid, const uint64 pgsp_planid,
const double execution_time, const char *rows_hint, const char *scan_hint,
const char *join_hint, const char *lead_hint,
const double diff_of_scans, const double max_diff_ratio_scan,
const double diff_of_joins, const double max_diff_ratio_join,
const int scan_cnt, const int join_cnt, char *application_name);
static bool insertNormQueries(const char *norm_query_hash, const char *norm_query_string);
static bool insertRawQueries(const char *raw_query_hash, const char *raw_query_string);
static void selectHints(const char *norm_query_string, const char *application_name, StringInfo prev_rows_hint);
static bool deleteHints(const char *norm_query_string, const char *application_name);
static bool insertHints(const char *norm_query_string, const char *application_name, const char *hints);
/*
* Return pg_plan_advsr owner's Oid.
*/
static Oid
extensionOwner(void)
{
Relation relation = NULL;
SysScanDesc scandesc;
ScanKeyData entry[1];
HeapTuple extensionTuple = NULL;
Form_pg_extension extensionForm = NULL;
Oid extensionOwner;
relation = table_open(ExtensionRelationId, AccessShareLock);
ScanKeyInit(&entry[0], Anum_pg_extension_extname,
BTEqualStrategyNumber, F_NAMEEQ,
CStringGetDatum("pg_plan_advsr"));
scandesc = systable_beginscan(relation, ExtensionNameIndexId, true,
NULL, 1, entry);
extensionTuple = systable_getnext(scandesc);
if (HeapTupleIsValid(extensionTuple))
{
extensionForm = (Form_pg_extension) GETSTRUCT(extensionTuple);
if (!superuser_arg(extensionForm->extowner))
ereport(ERROR, (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
errmsg("pg_plan_advsr extension needs to be owned by superuser")));
extensionOwner = extensionForm->extowner;
Assert(OidIsValid(extensionOwner));
}
else
ereport(ERROR, (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
errmsg("pg_plan_advsr extension not loaded")));
systable_endscan(scandesc);
table_close(relation, AccessShareLock);
return extensionOwner;
}
/*
* Return relationname's Oid
*/
static Oid
resolveRelationId(text *relationName, bool missingOk)
{
List *relationNameList = NIL;
RangeVar *relation = NULL;
Oid relationId = InvalidOid;
relationNameList = textToQualifiedNameList(relationName);
relation = makeRangeVarFromNameList(relationNameList);
relationId = RangeVarGetRelid(relation, NoLock, missingOk);
return relationId;
}
/*
* Get nextVal of the specified sequence
*/
static uint64
getNextVal(const char *sequence)
{
Oid sequenceId = InvalidOid;
Datum sequenceIdDatum = 0;
Oid savedUserId = InvalidOid;
int savedSecurityContext = 0;
Datum nextValDatum = 0;
sequenceId = resolveRelationId(cstring_to_text(sequence), false);
sequenceIdDatum = ObjectIdGetDatum(sequenceId);
GetUserIdAndSecContext(&savedUserId, &savedSecurityContext);
SetUserIdAndSecContext(extensionOwner(), SECURITY_LOCAL_USERID_CHANGE);
nextValDatum = DirectFunctionCall1(nextval_oid, sequenceIdDatum);
SetUserIdAndSecContext(savedUserId, savedSecurityContext);
return DatumGetInt64(nextValDatum);
}
/*
* Insert a row into plan_repo.plan_history table.
*/
static bool
insertPlanHistory(const char *norm_query_hash, const uint64 pgsp_queryid, const uint64 pgsp_planid,
const double execution_time, const char *rows_hint, const char *scan_hint,
const char *join_hint, const char *lead_hint,
const double diff_of_scans, const double max_diff_ratio_scan,
const double diff_of_joins, const double max_diff_ratio_join,
const int scan_cnt, const int join_cnt, char *application_name)
{
Relation rel = NULL;
TupleDesc tupleDescriptor = NULL;
HeapTuple heapTuple = NULL;
Datum values[Natts_plan_history];
bool isNulls[Natts_plan_history];
Oid relationId = get_relname_relid("plan_history", LookupExplicitNamespace("plan_repo", true));
if (relationId == InvalidOid)
return false;
/* form new shard tuple */
memset(values, 0, sizeof(values));
memset(isNulls, false, sizeof(isNulls));
values[Anum_plan_history_id - 1] = Int64GetDatum(getNextVal("plan_repo.plan_history_id_seq"));
isNulls[Anum_plan_history_id - 1] = false;
values[Anum_plan_history_norm_query_hash - 1] = CStringGetTextDatum(norm_query_hash);
isNulls[Anum_plan_history_norm_query_hash - 1] = (norm_query_hash == NULL) ? true : false;
#if PG_VERSION_NUM >= 140000
values[Anum_plan_history_pgsp_queryid - 1] = Int64GetDatum(pgsp_queryid);
#else
values[Anum_plan_history_pgsp_queryid - 1] = Int32GetDatum(pgsp_queryid);
#endif /* PG_VERSION_NUM */
isNulls[Anum_plan_history_pgsp_queryid - 1] = false;
values[Anum_plan_history_pgsp_planid - 1] = Int64GetDatum(pgsp_planid);
isNulls[Anum_plan_history_pgsp_planid - 1] = false;
values[Anum_plan_history_execution_time - 1] = Float8GetDatum(execution_time);
isNulls[Anum_plan_history_execution_time - 1] = false;
values[Anum_plan_history_rows_hint - 1] = CStringGetTextDatum(rows_hint);
isNulls[Anum_plan_history_rows_hint - 1] = (rows_hint == NULL) ? true : false;
values[Anum_plan_history_scan_hint - 1] = CStringGetTextDatum(scan_hint);
isNulls[Anum_plan_history_scan_hint - 1] = (scan_hint == NULL) ? true : false;
values[Anum_plan_history_join_hint - 1] = CStringGetTextDatum(join_hint);
isNulls[Anum_plan_history_join_hint - 1] = (join_hint == NULL) ? true : false;
values[Anum_plan_history_lead_hint - 1] = CStringGetTextDatum(lead_hint);
isNulls[Anum_plan_history_lead_hint - 1] = (lead_hint == NULL) ? true : false;
values[Anum_plan_history_diff_of_scans - 1] = Float8GetDatum(diff_of_scans);
isNulls[Anum_plan_history_diff_of_scans - 1] = false;
values[Anum_plan_history_max_diff_ratio_scan - 1] = Float8GetDatum(max_diff_ratio_scan);
isNulls[Anum_plan_history_max_diff_ratio_scan - 1] = false;
values[Anum_plan_history_diff_of_joins - 1] = Float8GetDatum(diff_of_joins);
isNulls[Anum_plan_history_diff_of_joins - 1] = false;
values[Anum_plan_history_max_diff_ratio_join - 1] = Float8GetDatum(max_diff_ratio_join);
isNulls[Anum_plan_history_max_diff_ratio_join - 1] = false;
values[Anum_plan_history_scan_cnt - 1] = Int32GetDatum(scan_cnt);
isNulls[Anum_plan_history_scan_cnt - 1] = false;
values[Anum_plan_history_join_cnt - 1] = Int32GetDatum(join_cnt);
isNulls[Anum_plan_history_join_cnt - 1] = false;
values[Anum_plan_history_application_name - 1] = CStringGetTextDatum(application_name);
isNulls[Anum_plan_history_application_name - 1] = (application_name == NULL) ? true : false;
values[Anum_plan_history_timestamp - 1] = TimestampGetDatum(GetCurrentTimestamp());
isNulls[Anum_plan_history_timestamp - 1] = false;
rel = table_open(relationId, RowExclusiveLock);
if (rel == NULL)
return false;
tupleDescriptor = RelationGetDescr(rel);
heapTuple = heap_form_tuple(tupleDescriptor, values, isNulls);
CatalogTupleInsert(rel, heapTuple);
CommandCounterIncrement();
table_close(rel, NoLock);
return true;
}
/*
* Insert a row into plan_repo.norm_queries table.
*/
static bool
insertNormQueries(const char *norm_query_hash, const char *norm_query_string)
{
Relation rel = NULL;
TupleDesc tupleDescriptor = NULL;
HeapTuple heapTuple = NULL;
Datum values[Natts_norm_queries];
bool isNulls[Natts_norm_queries];
Oid relationId = get_relname_relid("norm_queries", LookupExplicitNamespace("plan_repo", true));
if (relationId == InvalidOid)
return false;
/* form new shard tuple */
memset(values, 0, sizeof(values));
memset(isNulls, false, sizeof(isNulls));
values[Anum_norm_queries_norm_query_hash - 1] = CStringGetTextDatum(norm_query_hash);
isNulls[Anum_norm_queries_norm_query_hash - 1] = (norm_query_hash == NULL) ? true : false;
values[Anum_norm_queries_norm_query_string - 1] = CStringGetTextDatum(norm_query_string);
isNulls[Anum_norm_queries_norm_query_string - 1] = (norm_query_string == NULL) ? true : false;
rel = table_open(relationId, RowExclusiveLock);
if (rel == NULL)
return false;
tupleDescriptor = RelationGetDescr(rel);
heapTuple = heap_form_tuple(tupleDescriptor, values, isNulls);
CatalogTupleInsert(rel, heapTuple);
CommandCounterIncrement();
table_close(rel, NoLock);
return true;
}
/*
* Insert a row into plan_repo.raw_queries table.
*/
static bool
insertRawQueries(const char *raw_query_hash, const char *raw_query_string)
{
Relation rel = NULL;
TupleDesc tupleDescriptor = NULL;
HeapTuple heapTuple = NULL;
Datum values[Natts_raw_queries];
bool isNulls[Natts_raw_queries];
Oid relationId = get_relname_relid("raw_queries", LookupExplicitNamespace("plan_repo", true));
if (relationId == InvalidOid)
return false;
/* form new shard tuple */
memset(values, 0, sizeof(values));
memset(isNulls, false, sizeof(isNulls));
values[Anum_raw_queries_norm_query_hash - 1] = CStringGetTextDatum(raw_query_hash);
isNulls[Anum_raw_queries_norm_query_hash - 1] = (raw_query_hash == NULL) ? true : false;
values[Anum_raw_queries_raw_query_id - 1] = Int64GetDatum(getNextVal("plan_repo.raw_queries_raw_query_id_seq"));
isNulls[Anum_raw_queries_raw_query_id - 1] = false;
values[Anum_raw_queries_raw_query_string - 1] = CStringGetTextDatum(raw_query_string);
isNulls[Anum_raw_queries_raw_query_string - 1] = (raw_query_string == NULL) ? true : false;
values[Anum_raw_queries_timestamp - 1] = TimestampGetDatum(GetCurrentTimestamp());
isNulls[Anum_raw_queries_timestamp - 1] = false;
rel = table_open(relationId, RowExclusiveLock);
if (rel == NULL)
return false;
tupleDescriptor = RelationGetDescr(rel);
heapTuple = heap_form_tuple(tupleDescriptor, values, isNulls);
CatalogTupleInsert(rel, heapTuple);
CommandCounterIncrement();
table_close(rel, NoLock);
return true;
}
/*
* Fetch rows from hint_plan.hints table and append the rows to prev_rows_hint.
*/
static void
selectHints(const char *norm_query_string, const char *application_name, StringInfo prev_rows_hint)
{
Relation rel = NULL;
Oid relationId = get_relname_relid("hints",
LookupExplicitNamespace("hint_plan", true));
ScanKeyData scanKey[2];
SysScanDesc scanDescriptor = NULL;
int scanKeyCount = 2;
bool indexOK = true;
HeapTuple heapTuple = NULL;
if (relationId == InvalidOid)
return;
rel = table_open(relationId, AccessShareLock);
if (rel == NULL)
return;
ScanKeyInit(&scanKey[0], Anum_hints_norm_query_string,
BTEqualStrategyNumber, F_TEXTEQ, CStringGetTextDatum(norm_query_string));
ScanKeyInit(&scanKey[1], Anum_hints_application_name,
BTEqualStrategyNumber, F_TEXTEQ, CStringGetTextDatum(application_name));
scanDescriptor = systable_beginscan(rel,
get_relname_relid("hints_norm_and_app",
LookupExplicitNamespace("hint_plan", true)),
indexOK, NULL, scanKeyCount, scanKey);
heapTuple = systable_getnext(scanDescriptor);
while (HeapTupleIsValid(heapTuple))
{
TupleDesc tupleDescriptor = RelationGetDescr(rel);
bool isNullArray[Natts_hints];
Datum datumArray[Natts_hints];
heap_deform_tuple(heapTuple, tupleDescriptor, datumArray, isNullArray);
if (!isNullArray[Anum_hints_hints - 1])
appendStringInfo(prev_rows_hint, "%s", TextDatumGetCString(datumArray[Anum_hints_hints - 1]));
heapTuple = systable_getnext(scanDescriptor);
}
systable_endscan(scanDescriptor);
table_close(rel, NoLock);
}
/*
* Delete a row from hint_plan.hints
*/
static bool
deleteHints(const char *norm_query_string, const char *application_name)
{
Relation rel = NULL;
Oid relationId = get_relname_relid("hints",
LookupExplicitNamespace("hint_plan", true));
ScanKeyData scanKey[2];
SysScanDesc scanDescriptor = NULL;
int scanKeyCount = 2;
bool indexOK = true;
HeapTuple heapTuple = NULL;
if (relationId == InvalidOid)
return false;
rel = table_open(relationId, AccessShareLock);
if (rel == NULL)
return false;
ScanKeyInit(&scanKey[0], Anum_hints_norm_query_string,
BTEqualStrategyNumber, F_TEXTEQ, CStringGetTextDatum(norm_query_string));
ScanKeyInit(&scanKey[1], Anum_hints_application_name,
BTEqualStrategyNumber, F_TEXTEQ, CStringGetTextDatum(application_name));
scanDescriptor = systable_beginscan(rel,
get_relname_relid("hints_norm_and_app",
LookupExplicitNamespace("hint_plan", true)),
indexOK, NULL, scanKeyCount, scanKey);
heapTuple = systable_getnext(scanDescriptor);
while (HeapTupleIsValid(heapTuple))
{
TupleDesc tupleDescriptor = RelationGetDescr(rel);
bool isNullArray[Natts_hints];
Datum datumArray[Natts_hints];
heap_deform_tuple(heapTuple, tupleDescriptor, datumArray, isNullArray);
CatalogTupleDelete(rel, &heapTuple->t_self);
CommandCounterIncrement();
heapTuple = systable_getnext(scanDescriptor);
}
systable_endscan(scanDescriptor);
table_close(rel, NoLock);
return true;
}
/*
* Insert a row into hint_plan.hints table.
*/
static bool
insertHints(const char *norm_query_string, const char *application_name, const char *hints)
{
Relation rel = NULL;
TupleDesc tupleDescriptor = NULL;
HeapTuple heapTuple = NULL;
Datum values[Natts_hints];
bool isNulls[Natts_hints];
Oid relationId = get_relname_relid("hints", LookupExplicitNamespace("hint_plan", true));
if (relationId == InvalidOid)
return false;
/* form new shard tuple */
memset(values, 0, sizeof(values));
memset(isNulls, false, sizeof(isNulls));
values[Anum_hints_id - 1] = Int64GetDatum(getNextVal("hint_plan.hints_id_seq"));
isNulls[Anum_hints_id - 1] = false;
values[Anum_hints_norm_query_string - 1] = CStringGetTextDatum(norm_query_string);
isNulls[Anum_hints_norm_query_string - 1] = (norm_query_string == NULL) ? true : false;
values[Anum_hints_application_name - 1] = CStringGetTextDatum(application_name);
isNulls[Anum_hints_application_name - 1] = (application_name == NULL) ? true : false;
values[Anum_hints_hints - 1] = CStringGetTextDatum(hints);
isNulls[Anum_hints_hints - 1] = (hints == NULL) ? true : false;
rel = table_open(relationId, RowExclusiveLock);
if (rel == NULL)
return false;
tupleDescriptor = RelationGetDescr(rel);
heapTuple = heap_form_tuple(tupleDescriptor, values, isNulls);
CatalogTupleInsert(rel, heapTuple);
CommandCounterIncrement();
table_close(rel, NoLock);
return true;
}
/* Install hooks */
void
_PG_init(void)
{
List *lib_list;
ListCell *l;
int i = 0;
if (process_shared_preload_libraries_in_progress == true)
{
SplitIdentifierString(shared_preload_libraries_string, ',', &lib_list);
foreach(l, lib_list)
{
char *curname = (char *) lfirst(l);
if (strcmp(curname, "pg_hint_plan") == 0)
i++;
else if (strcmp(curname, "pg_store_plans") == 0)
i++;
}
if (i != 2)
{
ereport(ERROR,
(errmsg("pg_hint_plan and/or pg_store_plans are not loaded."),
errhint("pg_plan_advsr needs them. You can check shared_preload_libraries!")));
}
pfree(lib_list);
}
prev_post_parse_analyze_hook = post_parse_analyze_hook;
post_parse_analyze_hook = pg_plan_advsr_post_parse_analyze_hook;
prev_ProcessUtility_hook = ProcessUtility_hook;
ProcessUtility_hook = pg_plan_advsr_ProcessUtility_hook;
prev_ExplainOneQuery_hook = ExplainOneQuery_hook;
ExplainOneQuery_hook = pg_plan_advsr_ExplainOneQuery_hook;
prev_ExecutorStart_hook = ExecutorStart_hook;
ExecutorStart_hook = pg_plan_advsr_ExecutorStart_hook;
prev_ExecutorRun_hook = ExecutorRun_hook;
ExecutorRun_hook = pg_plan_advsr_ExecutorRun_hook;
prev_ExecutorFinish_hook = ExecutorFinish_hook;
ExecutorFinish_hook = pg_plan_advsr_ExecutorFinish_hook;
prev_ExecutorEnd_hook = ExecutorEnd_hook;
ExecutorEnd_hook = pg_plan_advsr_ExecutorEnd_hook;
DefineCustomBoolVariable("pg_plan_advsr.enabled",
"Enable / Disable pg_plan_advsr",
NULL,
&pg_plan_advsr_is_enabled,
true,
PGC_USERSET,
0,
NULL,
NULL,
NULL);
DefineCustomBoolVariable("pg_plan_advsr.quieted",
"Enable / Disable pg_plan_advsr's quiet mode",
NULL,
&pg_plan_advsr_is_quieted,
false,
PGC_USERSET,
0,
NULL,
NULL,
NULL);
DefineCustomBoolVariable("pg_plan_advsr.widely",
"pg_plan_advsr works with EXPLAIN command without ANALYZE option",
NULL,
&pg_plan_advsr_widely,
false,
PGC_USERSET,
0,
NULL,
NULL,
NULL);
}
/* Uninstall hooks. */
void
_PG_fini(void)
{
post_parse_analyze_hook = prev_post_parse_analyze_hook;
ProcessUtility_hook = prev_ProcessUtility_hook;
ExecutorStart_hook = prev_ExecutorStart_hook;
ExecutorRun_hook = prev_ExecutorRun_hook;
ExecutorFinish_hook = prev_ExecutorFinish_hook;
ExecutorEnd_hook = prev_ExecutorEnd_hook;
}
/*
* Enable feedback loop.
*
* Following GUC parameters will became "ON" when this function executed.
* pg_plan_advsr.enabled
* pg_hint_plan.debug_print
* pg_hint_plan.enable_hint_table
*/
Datum
pg_plan_advsr_enable_feedback(PG_FUNCTION_ARGS)
{
elog(DEBUG3, "execute pg_plan_advsr_enable_feedback");
(void) set_config_option("pg_plan_advsr.enabled", "ON",
PGC_USERSET, PGC_S_OVERRIDE,
GUC_ACTION_SAVE, true, 0, false);
(void) set_config_option("pg_hint_plan.enable_hint_table", "ON",
PGC_USERSET, PGC_S_OVERRIDE,
GUC_ACTION_SAVE, true, 0, false);
(void) set_config_option("pg_hint_plan.debug_print", "ON",
PGC_USERSET, PGC_S_OVERRIDE,
GUC_ACTION_SAVE, true, 0, false);
PG_RETURN_VOID();
}
/*
* Disable feedback loop
*/
Datum
pg_plan_advsr_disable_feedback(PG_FUNCTION_ARGS)
{
elog(DEBUG3, "execute pg_plan_advsr_disable_feedback");
(void) set_config_option("pg_plan_advsr.enabled", "ON",
PGC_USERSET, PGC_S_OVERRIDE,
GUC_ACTION_SAVE, true, 0, false);
(void) set_config_option("pg_hint_plan.enable_hint_table", "OFF",
PGC_USERSET, PGC_S_OVERRIDE,
GUC_ACTION_SAVE, true, 0, false);
(void) set_config_option("pg_hint_plan.debug_print", "OFF",
PGC_USERSET, PGC_S_OVERRIDE,
GUC_ACTION_SAVE, true, 0, false);
PG_RETURN_VOID();
}
/* To get normalized query like a pg_hint_plan.c */
static void
pg_plan_advsr_post_parse_analyze_hook(ParseState *pstate, Query *query
#if PG_VERSION_NUM < 140000
)
#else
, JumbleState *jstate)
#endif /* PG_VERSION_NUM */
{
const char *query_str;
if (prev_post_parse_analyze_hook)
prev_post_parse_analyze_hook(pstate, query
#if PG_VERSION_NUM < 140000
);
#else
, jstate);
#endif /* PG_VERSION_NUM */
/* Create normalized query for later use */
if (pg_plan_advsr_enabled())
{
int query_len;
#if PG_VERSION_NUM < 140000
pgssJumbleState jstate;
Query *jumblequery;
#endif /* PG_VERSION_NUM */
elog(DEBUG1, "##pg_plan_advsr_post_parse_analyze_hook start ##");
#if PG_VERSION_NUM < 140000
query_str = get_query_string(pstate, query, &jumblequery);
if (query_str && jumblequery)
{
/*
* XXX: normalizing code is copied from pg_stat_statements.c, so
* be careful to PostgreSQL's version up.
*/
jstate.jumble = (unsigned char *) palloc(JUMBLE_SIZE);
jstate.jumble_len = 0;
jstate.clocations_buf_size = 32;
jstate.clocations = (pgssLocationLen *)
palloc(jstate.clocations_buf_size * sizeof(pgssLocationLen));
jstate.clocations_count = 0;
JumbleQuery(&jstate, jumblequery);
/*
* Normalize the query string by replacing constants with '?'
*/
/*
* Search hint string which is stored keyed by query string and
* application name. The query string is normalized to allow
* fuzzy matching.
*
* Adding 1 byte to query_len ensures that the returned string has
* a terminating NULL.
*/
query_len = strlen(query_str) + 1;
normalized_query =
generate_normalized_query(&jstate, query_str,
query->stmt_location,
&query_len,
GetDatabaseEncoding());
}
#endif
#if PG_VERSION_NUM >= 140000
query_str = pstate->p_sourcetext;
if (!jstate)
#endif
#if PG_VERSION_NUM >= 160000
jstate = JumbleQuery(query);
#elif PG_VERSION_NUM >= 140000
jstate = JumbleQuery(query, query_str);
#endif
#if PG_VERSION_NUM >= 140000
if (!jstate)
return;
query_len = strlen(query_str) + 1;
normalized_query =
generate_normalized_query(jstate, query_str, 0, &query_len);
#endif /* PG_VERSION_NUM */
elog(DEBUG1, "##pg_plan_advsr_post_parse_analyze_hook end ##");
}
}
/*
* This function setup the "isExplain" flag.
* If this flag is true, we can create hints for given query.
*/
static void
pg_plan_advsr_ProcessUtility_hook(PlannedStmt *pstmt,
const char *queryString,
#if PG_VERSION_NUM >= 140000
bool readOnlyTree,
#endif /* PG_VERSION_NUM */
ProcessUtilityContext context,
ParamListInfo params,
QueryEnvironment *queryEnv,
DestReceiver *dest,
#if PG_VERSION_NUM < 130000
char *completionTag)
#else
QueryCompletion *qc)
#endif /* PG_VERSION_NUM */
{
isExplain = query_or_expression_tree_walker((Node *) pstmt,
pg_plan_advsr_query_walker,
NULL,
0);
if (isExplain)
{
elog(DEBUG1, "queryString: %s", queryString);
explain_query = makeStringInfo();
appendStringInfo(explain_query, "%s", queryString);
}
if (prev_ProcessUtility_hook)
prev_ProcessUtility_hook(
pstmt,
queryString,
#if PG_VERSION_NUM >= 140000
readOnlyTree,
#endif /* PG_VERSION_NUM */
context,
params,
queryEnv,
dest,
#if PG_VERSION_NUM < 130000
completionTag);
#else
qc);
#endif /* PG_VERSION_NUM */
else
standard_ProcessUtility(
pstmt,
queryString,
#if PG_VERSION_NUM >= 140000
readOnlyTree,
#endif /* PG_VERSION_NUM */
context,
params,
queryEnv,
dest,
#if PG_VERSION_NUM < 130000
completionTag);
#else
qc);
#endif /* PG_VERSION_NUM */
}
static void pg_plan_advsr_ExplainOneQuery_hook(Query *query,
int cursorOptions,
IntoClause *into,