pg_hint_plan.c

   1 /*-------------------------------------------------------------------------
   2  *
   3  * pg_hint_plan.c
   4  *              Track statement execution in current/last transaction.
   5  *
   6  * Copyright (c) 2011, PostgreSQL Global Development Group
   7  *
   8  * IDENTIFICATION
   9  *        contrib/pg_hint_plan/pg_hint_plan.c
  10  *
  11  *-------------------------------------------------------------------------
  12  */
  13 #include "postgres.h"
  14 #include "fmgr.h"
  15 #include "nodes/print.h"
  16 #include "utils/elog.h"
  17 #include "utils/builtins.h"
  18 #include "utils/memutils.h"
  19 #include "optimizer/cost.h"
  20 #include "optimizer/joininfo.h"
  21 #include "optimizer/pathnode.h"
  22 #include "optimizer/paths.h"
  23
  24 #ifdef PG_MODULE_MAGIC
  25 PG_MODULE_MAGIC;
  26 #endif
  27
  28 #define HASH_ENTRIES 201
  29
  30 enum
  31 {
  32         ENABLE_SEQSCAN          = 0x01,
  33         ENABLE_INDEXSCAN        = 0x02,
  34         ENABLE_BITMAPSCAN       = 0x04,
  35         ENABLE_TIDSCAN          = 0x08,
  36         ENABLE_NESTLOOP         = 0x10,
  37         ENABLE_MERGEJOIN        = 0x20,
  38         ENABLE_HASHJOIN         = 0x40
  39 } TYPE_BITS;
  40
  41 typedef struct tidlist
  42 {
  43         int nrels;
  44         Oid *oids;
  45 } TidList;
  46
  47 typedef struct hash_entry
  48 {
  49         TidList tidlist;
  50         unsigned char enforce_mask;
  51         struct hash_entry *next;
  52 } HashEntry;
  53
  54 static HashEntry *hashent[HASH_ENTRIES];
  55 #ifdef NOT_USED
  56 static bool (*org_cost_hook)(CostHookType type, PlannerInfo *root, Path *path1, Path *path2);
  57 #endif
  58 static bool print_log = false;
  59 static bool tweak_enabled = true;
  60
  61 /* Module callbacks */
  62 void            _PG_init(void);
  63 void            _PG_fini(void);
  64 Datum           pg_add_hint(PG_FUNCTION_ARGS);
  65 Datum           pg_clear_hint(PG_FUNCTION_ARGS);
  66 Datum       pg_dump_hint(PG_FUNCTION_ARGS);
  67 Datum           pg_enable_hint(bool arg, bool *isnull);
  68 Datum           pg_enable_log(bool arg, bool *isnull);
  69
  70 #ifdef NOT_USED
  71 static char *rels_str(PlannerInfo *root, Path *path);
  72 static void dump_rels(char *label, PlannerInfo *root, Path *path, bool found, bool enabled);
  73 static void dump_joinrels(char *label, PlannerInfo *root, Path *inpath, Path *outpath, bool found, bool enabled);
  74 static bool my_cost_hook(CostHookType type, PlannerInfo *root, Path *path1, Path *path2);
  75 #endif
  76 static void free_hashent(HashEntry *head);
  77 static unsigned int calc_hash(TidList *tidlist);
  78 #ifdef NOT_USED
  79 static HashEntry *search_ent(TidList *tidlist);
  80 #endif
  81
  82 /* Join Method Hints */
  83 typedef struct RelIdInfo
  84 {
  85         Index           relid;
  86         Oid                     oid;
  87         Alias      *eref;
  88 } RelIdInfo;
  89
  90 typedef struct JoinHint
  91 {
  92         int                             nrels;
  93         List               *relidinfos;
  94         Relids                  joinrelids;
  95         unsigned char   enforce_mask;
  96 } JoinHint;
  97
  98 typedef struct GucVariables
  99 {
 100         bool    enable_seqscan;
 101         bool    enable_indexscan;
 102         bool    enable_bitmapscan;
 103         bool    enable_tidscan;
 104         bool    enable_sort;
 105         bool    enable_hashagg;
 106         bool    enable_nestloop;
 107         bool    enable_material;
 108         bool    enable_mergejoin;
 109         bool    enable_hashjoin;
 110 } GucVariables;
 111
 112 static void backup_guc(GucVariables *backup);
 113 static void restore_guc(GucVariables *backup);
 114 static void set_guc(unsigned char enforce_mask);
 115 static void build_join_hints(PlannerInfo *root, int level, List *initial_rels);
 116 static RelOptInfo *my_make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2);
 117 static RelOptInfo *my_join_search(PlannerInfo *root, int levels_needed,
 118                                                           List *initial_rels);
 119 static void my_join_search_one_level(PlannerInfo *root, int level);
 120 static void make_rels_by_clause_joins(PlannerInfo *root, RelOptInfo *old_rel, ListCell *other_rels);
 121 static void make_rels_by_clauseless_joins(PlannerInfo *root, RelOptInfo *old_rel, ListCell *other_rels);
 122 static bool has_join_restriction(PlannerInfo *root, RelOptInfo *rel);
 123
 124 static join_search_hook_type org_join_search = NULL;
 125 static List **join_hint_level = NULL;
 126
 127 PG_FUNCTION_INFO_V1(pg_add_hint);
 128 PG_FUNCTION_INFO_V1(pg_clear_hint);
 129
 130 /*
 131  * Module load callbacks
 132  */
 133 void
 134 _PG_init(void)
 135 {
 136         int i;
 137
 138 #ifdef NOT_USED
 139         org_cost_hook = cost_hook;
 140         cost_hook = my_cost_hook;
 141 #endif
 142         org_join_search = join_search_hook;
 143         join_search_hook = my_join_search;
 144
 145         for (i = 0 ; i < HASH_ENTRIES ; i++)
 146                 hashent[i] = NULL;
 147 }
 148
 149 /*
 150  * Module unload callback
 151  */
 152 void
 153 _PG_fini(void)
 154 {
 155         int i;
 156
 157 #ifdef NOT_USED
 158         cost_hook = org_cost_hook;
 159 #endif
 160         join_search_hook = org_join_search;
 161
 162         for (i = 0 ; i < HASH_ENTRIES ; i++)
 163         {
 164                 free_hashent(hashent[i]);
 165                 hashent[i] = NULL;
 166
 167         }
 168 }
 169
 170 #ifdef NOT_USED
 171 char *rels_str(PlannerInfo *root, Path *path)
 172 {
 173         char buf[4096];
 174         int relid;
 175         int first = 1;
 176         Bitmapset *tmpbms;
 177
 178         if (path->pathtype == T_Invalid) return strdup("");
 179
 180         tmpbms = bms_copy(path->parent->relids);
 181
 182         buf[0] = 0;
 183         while ((relid = bms_first_member(tmpbms)) >= 0)
 184         {
 185                 char idbuf[8];
 186                 snprintf(idbuf, sizeof(idbuf), first ? "%d" : ", %d",
 187                                  root->simple_rte_array[relid]->relid);
 188                 if (strlen(buf) + strlen(idbuf) < sizeof(buf))
 189                         strcat(buf, idbuf);
 190                 first = 0;
 191         }
 192
 193         return strdup(buf);
 194 }
 195 #endif
 196
 197 static int oidsortcmp(const void *a, const void *b)
 198 {
 199         const Oid oida = *((const Oid *)a);
 200         const Oid oidb = *((const Oid *)b);
 201
 202         return oida - oidb;
 203 }
 204
 205 #ifdef NOT_USED
 206 static TidList *maketidlist(PlannerInfo *root, Path *path1, Path *path2)
 207 {
 208         int relid;
 209         Path *paths[2] = {path1, path2};
 210         int i;
 211         int j = 0;
 212         int nrels = 0;
 213         TidList *ret = (TidList *)malloc(sizeof(TidList));
 214
 215         for (i = 0 ; i < 2 ; i++)
 216         {
 217                 if (paths[i] != NULL)
 218                         nrels += bms_num_members(paths[i]->parent->relids);
 219         }
 220
 221         ret->nrels = nrels;
 222         ret->oids = (Oid *)malloc(nrels * sizeof(Oid));
 223
 224         for (i = 0 ; i < 2 ; i++)
 225         {
 226                 Bitmapset *tmpbms;
 227
 228                 if (paths[i] == NULL) continue;
 229
 230                 tmpbms= bms_copy(paths[i]->parent->relids);
 231
 232                 while ((relid = bms_first_member(tmpbms)) >= 0)
 233                         ret->oids[j++] = root->simple_rte_array[relid]->relid;
 234         }
 235
 236         if (nrels > 1)
 237                 qsort(ret->oids, nrels, sizeof(Oid), oidsortcmp);
 238
 239         return ret;
 240 }
 241
 242 static void free_tidlist(TidList *tidlist)
 243 {
 244         if (tidlist)
 245         {
 246                 if (tidlist->oids)
 247                         free(tidlist->oids);
 248                 free(tidlist);
 249         }
 250 }
 251
 252 int r = 0;
 253 static void dump_rels(char *label, PlannerInfo *root, Path *path, bool found, bool enabled)
 254 {
 255         char *relsstr;
 256
 257         if (!print_log) return;
 258         relsstr = rels_str(root, path);
 259         ereport(INFO, (errmsg_internal("SCAN: %04d: %s for relation %s (%s, %s)\n",
 260                                                                   r++, label, relsstr,
 261                                                                   found ? "found" : "not found",
 262                                                                   enabled ? "enabled" : "disabled")));
 263         free(relsstr);
 264 }
 265
 266 int J = 0;
 267 void dump_joinrels(char *label, PlannerInfo *root, Path *inpath, Path *outpath,
 268                                    bool found, bool enabled)
 269 {
 270         char *irelstr, *orelstr;
 271
 272         if (!print_log) return;
 273         irelstr = rels_str(root, inpath);
 274         orelstr = rels_str(root, outpath);
 275
 276         ereport(INFO, (errmsg_internal("JOIN: %04d: %s for relation ((%s),(%s)) (%s, %s)\n",
 277                                                                   J++, label, irelstr, orelstr,
 278                                                                   found ? "found" : "not found",
 279                                                                   enabled ? "enabled" : "disabled")));
 280         free(irelstr);
 281         free(orelstr);
 282 }
 283
 284
 285 bool my_cost_hook(CostHookType type, PlannerInfo *root, Path *path1, Path *path2)
 286 {
 287         TidList *tidlist;
 288         HashEntry *ent;
 289         bool ret = false;
 290
 291         if (!tweak_enabled)
 292         {
 293                 switch (type)
 294                 {
 295                         case COSTHOOK_seqscan:
 296                                 return enable_seqscan;
 297                         case COSTHOOK_indexscan:
 298                                 return enable_indexscan;
 299                         case COSTHOOK_bitmapscan:
 300                                 return enable_bitmapscan;
 301                         case COSTHOOK_tidscan:
 302                                 return enable_tidscan;
 303                         case COSTHOOK_nestloop:
 304                                 return enable_nestloop;
 305                         case COSTHOOK_mergejoin:
 306                                 return enable_mergejoin;
 307                         case COSTHOOK_hashjoin:
 308                                 return enable_hashjoin;
 309                         default:
 310                                 ereport(LOG, (errmsg_internal("Unknown cost type")));
 311                                 break;
 312                 }
 313         }
 314         switch (type)
 315         {
 316                 case COSTHOOK_seqscan:
 317                         tidlist = maketidlist(root, path1, path2);
 318                         ent = search_ent(tidlist);
 319                         free_tidlist(tidlist);
 320                         ret = (ent ? (ent->enforce_mask & ENABLE_SEQSCAN) :
 321                                    enable_seqscan);
 322                         dump_rels("cost_seqscan", root, path1, ent != NULL, ret);
 323                         return ret;
 324                 case COSTHOOK_indexscan:
 325                         tidlist = maketidlist(root, path1, path2);
 326                         ent = search_ent(tidlist);
 327                         free_tidlist(tidlist);
 328                         ret = (ent ? (ent->enforce_mask & ENABLE_INDEXSCAN) :
 329                                    enable_indexscan);
 330                         dump_rels("cost_indexscan", root, path1, ent != NULL, ret);
 331                         return ret;
 332                 case COSTHOOK_bitmapscan:
 333                         if (path1->pathtype != T_BitmapHeapScan)
 334                         {
 335                                 ent = NULL;
 336                                 ret = enable_bitmapscan;
 337                         }
 338                         else
 339                         {
 340                                 tidlist = maketidlist(root, path1, path2);
 341                                 ent = search_ent(tidlist);
 342                                 free_tidlist(tidlist);
 343                                 ret = (ent ? (ent->enforce_mask & ENABLE_BITMAPSCAN) :
 344                                            enable_bitmapscan);
 345                         }
 346                         dump_rels("cost_bitmapscan", root, path1, ent != NULL, ret);
 347
 348                         return ret;
 349                 case COSTHOOK_tidscan:
 350                         tidlist = maketidlist(root, path1, path2);
 351                         ent = search_ent(tidlist);
 352                         free_tidlist(tidlist);
 353                         ret = (ent ? (ent->enforce_mask & ENABLE_TIDSCAN) :
 354                                    enable_tidscan);
 355                         dump_rels("cost_tidscan", root, path1, ent != NULL, ret);
 356                         return ret;
 357                 case COSTHOOK_nestloop:
 358                         tidlist = maketidlist(root, path1, path2);
 359                         ent = search_ent(tidlist);
 360                         free_tidlist(tidlist);
 361                         ret = (ent ? (ent->enforce_mask & ENABLE_NESTLOOP) :
 362                                    enable_nestloop);
 363                         dump_joinrels("cost_nestloop", root, path1, path2,
 364                                                   ent != NULL, ret);
 365                         return ret;
 366                 case COSTHOOK_mergejoin:
 367                         tidlist = maketidlist(root, path1, path2);
 368                         ent = search_ent(tidlist);
 369                         free_tidlist(tidlist);
 370                         ret = (ent ? (ent->enforce_mask & ENABLE_MERGEJOIN) :
 371                                    enable_mergejoin);
 372                         dump_joinrels("cost_mergejoin", root, path1, path2,
 373                                                   ent != NULL, ret);
 374                         return ret;
 375                 case COSTHOOK_hashjoin:
 376                         tidlist = maketidlist(root, path1, path2);
 377                         ent = search_ent(tidlist);
 378                         free_tidlist(tidlist);
 379                         ret = (ent ? (ent->enforce_mask & ENABLE_HASHJOIN) :
 380                                    enable_hashjoin);
 381                         dump_joinrels("cost_hashjoin", root, path1, path2,
 382                                                   ent != NULL, ret);
 383                         return ret;
 384                 default:
 385                         ereport(LOG, (errmsg_internal("Unknown cost type")));
 386                         break;
 387         }
 388
 389         return true;
 390 }
 391 #endif
 392
 393 static void free_hashent(HashEntry *head)
 394 {
 395         HashEntry *next = head;
 396
 397         while (next)
 398         {
 399                 HashEntry *last = next;
 400                 if (next->tidlist.oids != NULL) free(next->tidlist.oids);
 401                 next = next->next;
 402                 free(last);
 403         }
 404 }
 405
 406 static HashEntry *parse_tidlist(char **str)
 407 {
 408         char tidstr[8];
 409         char *p0;
 410         Oid tid[20]; /* ^^; */
 411         int ntids = 0;
 412         int i, len;
 413         HashEntry *ret;
 414
 415         while (isdigit(**str) && ntids < 20)
 416         {
 417                 p0 = *str;
 418                 while (isdigit(**str)) (*str)++;
 419                 len = *str - p0;
 420                 if (len >= 8) return NULL;
 421                 strncpy(tidstr, p0, len);
 422                 tidstr[len] = 0;
 423
 424                 /* Tis 0 is valid? I don't know :-p */
 425                 if ((tid[ntids++] = atoi(tidstr)) == 0) return NULL;
 426
 427                 if (**str == ',') (*str)++;
 428         }
 429
 430         if (ntids > 1)
 431                 qsort(tid, ntids, sizeof(Oid), oidsortcmp);
 432         ret = (HashEntry*)malloc(sizeof(HashEntry));
 433         ret->next = NULL;
 434         ret->enforce_mask = 0;
 435         ret->tidlist.nrels = ntids;
 436         ret->tidlist.oids = (Oid *)malloc(ntids * sizeof(Oid));
 437         for (i = 0 ; i < ntids ; i++)
 438                 ret->tidlist.oids[i] = tid[i];
 439         return ret;
 440 }
 441
 442 static int parse_phrase(HashEntry **head, char **str)
 443 {
 444         char *cmds[]    = {"seq", "index", "nest", "merge", "hash", NULL};
 445         unsigned char masks[] = {ENABLE_SEQSCAN, ENABLE_INDEXSCAN|ENABLE_BITMAPSCAN,
 446                                                    ENABLE_NESTLOOP, ENABLE_MERGEJOIN, ENABLE_HASHJOIN};
 447         char req[12];
 448         int cmd;
 449         HashEntry *ent = NULL;
 450         char *p0;
 451         int len;
 452         bool    not_use = false;
 453
 454         p0 = *str;
 455         while (isalpha(**str) || **str == '_') (*str)++;
 456         len = *str - p0;
 457         if (**str != '(' || len >= 12) return 0;
 458         strncpy(req, p0, len);
 459         req[len] = 0;
 460         if (strncmp("no_", req, 3) == 0)
 461         {
 462                 not_use = true;
 463                 memmove(req, req + 3, len - 3 + 1);
 464         }
 465         for (cmd = 0 ; cmds[cmd] && strcmp(cmds[cmd], req) ; cmd++);
 466         if (cmds[cmd] == NULL) return 0;
 467         (*str)++;
 468         if ((ent = parse_tidlist(str)) == NULL) return 0;
 469         if (*(*str)++ != ')') return 0;
 470         if (**str != 0 && **str != ';') return 0;
 471         if (**str == ';') (*str)++;
 472         ent->enforce_mask = not_use ? ~masks[cmd] : masks[cmd];
 473         ent->next = NULL;
 474         *head = ent;
 475
 476         return 1;
 477 }
 478
 479
 480 static HashEntry* parse_top(char* str)
 481 {
 482         HashEntry *head = NULL;
 483         HashEntry *ent = NULL;
 484
 485         if (!parse_phrase(&head, &str))
 486         {
 487                 free_hashent(head);
 488                 return NULL;
 489         }
 490         ent = head;
 491
 492         while (*str)
 493         {
 494                 if (!parse_phrase(&ent->next, &str))
 495                 {
 496                         free_hashent(head);
 497                         return NULL;
 498                 }
 499                 ent = ent->next;
 500         }
 501
 502         return head;
 503 }
 504
 505 static bool ent_matches(TidList *key, HashEntry *ent2)
 506 {
 507         int i;
 508
 509         if (key->nrels != ent2->tidlist.nrels)
 510                 return 0;
 511
 512         for (i = 0 ; i < key->nrels ; i++)
 513                 if (key->oids[i] != ent2->tidlist.oids[i])
 514                         return 0;
 515
 516         return 1;
 517 }
 518
 519 static unsigned int calc_hash(TidList *tidlist)
 520 {
 521         unsigned int hash = 0;
 522         int i = 0;
 523
 524         for (i = 0 ; i < tidlist->nrels ; i++)
 525         {
 526                 int j = 0;
 527                 for (j = 0 ; j < sizeof(Oid) ; j++)
 528                         hash = hash * 2 + ((tidlist->oids[i] >> (j * 8)) & 0xff);
 529         }
 530
 531         return hash % HASH_ENTRIES;
 532 ;
 533 }
 534
 535 #ifdef NOT_USED
 536 static HashEntry *search_ent(TidList *tidlist)
 537 {
 538         HashEntry *ent;
 539         if (tidlist == NULL) return NULL;
 540
 541         ent = hashent[calc_hash(tidlist)];
 542         while(ent)
 543         {
 544                 if (ent_matches(tidlist, ent))
 545                         return ent;
 546                 ent = ent->next;
 547         }
 548
 549         return NULL;
 550 }
 551 #endif
 552
 553 Datum
 554 pg_add_hint(PG_FUNCTION_ARGS)
 555 {
 556         HashEntry *ret = NULL;
 557         char *str = NULL;
 558         int i = 0;
 559
 560         if (PG_NARGS() < 1)
 561                 ereport(ERROR, (errmsg_internal("No argument")));
 562
 563         str = text_to_cstring(PG_GETARG_TEXT_PP(0));
 564
 565         ret = parse_top(str);
 566
 567         if (ret == NULL)
 568                 ereport(ERROR, (errmsg_internal("Parse Error")));
 569
 570         while (ret)
 571         {
 572                 HashEntry *etmp = NULL;
 573                 HashEntry *next = NULL;
 574                 int hash = calc_hash(&ret->tidlist);
 575                 while (hashent[hash] && ent_matches(&ret->tidlist, hashent[hash]))
 576                 {
 577                         etmp = hashent[hash]->next;
 578                         hashent[hash]->next = NULL;
 579                         free_hashent(hashent[hash]);
 580                         hashent[hash] = etmp;
 581
 582                 }
 583                 etmp = hashent[hash];
 584                 while (etmp && etmp->next)
 585                 {
 586                         if (ent_matches(&ret->tidlist, etmp->next))
 587                         {
 588                                 HashEntry *etmp2 = etmp->next->next;
 589                                 etmp->next->next = NULL;
 590                                 free_hashent(etmp->next);
 591                                 etmp->next = etmp2;
 592                         } else
 593                                 etmp = etmp->next;
 594                 }
 595
 596                 i++;
 597                 next = ret->next;
 598                 ret->next = hashent[hash];
 599                 hashent[hash] = ret;
 600                 ret = next;
 601         }
 602         PG_RETURN_INT32(i);
 603 }
 604
 605 Datum
 606 pg_clear_hint(PG_FUNCTION_ARGS)
 607 {
 608         int i;
 609         int n = 0;
 610
 611         for (i = 0 ; i < HASH_ENTRIES ; i++)
 612         {
 613                 free_hashent(hashent[i]);
 614                 hashent[i] = NULL;
 615                 n++;
 616
 617         }
 618         PG_RETURN_INT32(n);
 619 }
 620
 621 Datum
 622 pg_enable_hint(bool arg, bool *isnull)
 623 {
 624         tweak_enabled = arg;
 625         PG_RETURN_INT32(0);
 626 }
 627
 628 Datum
 629 pg_enable_log(bool arg, bool *isnull)
 630 {
 631         print_log = arg;
 632         PG_RETURN_INT32(0);
 633 }
 634
 635 static int putsbuf(char **p, char *bottom, char *str)
 636 {
 637         while (*p < bottom && *str)
 638         {
 639                 *(*p)++ = *str++;
 640         }
 641
 642         return (*str == 0);
 643 }
 644
 645 static void dump_ent(HashEntry *ent, char **p, char *bottom)
 646 {
 647         static char typesigs[] = "SIBTNMH";
 648         char sigs[sizeof(typesigs)];
 649         int i;
 650
 651         if (!putsbuf(p, bottom, "[(")) return;
 652         for (i = 0 ; i < ent->tidlist.nrels ; i++)
 653         {
 654                 if (i && !putsbuf(p, bottom, ", ")) return;
 655                 if (*p >= bottom) return;
 656                 *p += snprintf(*p, bottom - *p, "%d", ent->tidlist.oids[i]);
 657         }
 658         if (!putsbuf(p, bottom, "), ")) return;
 659         strcpy(sigs, typesigs);
 660         for (i = 0 ; i < 7 ; i++) /* Magic number here! */
 661         {
 662                 if(((1<<i) & ent->enforce_mask) == 0)
 663                         sigs[i] += 'a' - 'A';
 664         }
 665         if (!putsbuf(p, bottom, sigs)) return;
 666         if (!putsbuf(p, bottom, "]")) return;
 667 }
 668
 669 Datum
 670 pg_dump_hint(PG_FUNCTION_ARGS)
 671 {
 672         char buf[16384]; /* ^^; */
 673         char *bottom = buf + sizeof(buf);
 674         char *p = buf;
 675         int i;
 676         int first = 1;
 677
 678         memset(buf, 0, sizeof(buf));
 679         for (i = 0 ; i < HASH_ENTRIES ; i++)
 680         {
 681                 if (hashent[i])
 682                 {
 683                         HashEntry *ent = hashent[i];
 684                         while (ent)
 685                         {
 686                                 if (first)
 687                                         first = 0;
 688                                 else
 689                                         putsbuf(&p, bottom, ", ");
 690
 691                                 dump_ent(ent, &p, bottom);
 692                                 ent = ent->next;
 693                         }
 694                 }
 695         }
 696         if (p >= bottom) p--;
 697         *p = 0;
 698
 699         PG_RETURN_CSTRING(cstring_to_text(buf));
 700 }
 701
 702 /*
 703  * pg_add_hint()で登録した個別のヒントを、使用しやすい構造に変換する。
 704  */
 705 static JoinHint *
 706 set_relids(HashEntry *ent, RelIdInfo **relids, int nrels)
 707 {
 708         int                     i;
 709         int                     j;
 710         JoinHint   *hint;
 711
 712         hint = palloc(sizeof(JoinHint));
 713         hint->joinrelids = NULL;
 714         hint->relidinfos = NIL;
 715
 716         for (i = 0; i < ent->tidlist.nrels; i++)
 717         {
 718                 for (j = 0; j < nrels; j++)
 719                 {
 720                         if (ent->tidlist.oids[i] == relids[j]->oid)
 721                         {
 722                                 hint->relidinfos = lappend(hint->relidinfos, relids[j]);
 723                                 hint->joinrelids =
 724                                         bms_add_member(hint->joinrelids, relids[j]->relid);
 725                                 break;
 726                         }
 727                 }
 728
 729                 if (j == nrels)
 730                 {
 731                         list_free(hint->relidinfos);
 732                         pfree(hint);
 733                         return NULL;
 734                 }
 735         }
 736
 737         hint->nrels = ent->tidlist.nrels;
 738         hint->enforce_mask = ent->enforce_mask;
 739
 740         return hint;
 741 }
 742
 743 /*
 744  * pg_add_hint()で登録したヒントから、今回のクエリで使用するもののみ抽出し、
 745  * 使用しやすい構造に変換する。
 746  */
 747 static void
 748 build_join_hints(PlannerInfo *root, int level, List *initial_rels)
 749 {
 750         int                     i;
 751         int                     nrels;
 752         RelIdInfo **relids;
 753         JoinHint   *hint;
 754
 755         relids = palloc(sizeof(RelIdInfo *) * root->simple_rel_array_size);
 756
 757         // DEBUG  rtekind
 758         if (print_log)
 759         {
 760                 ListCell   *l;
 761                 foreach(l, initial_rels)
 762                 {
 763                         RelOptInfo *rel = (RelOptInfo *) lfirst(l);
 764                         elog_node_display(INFO, "initial_rels", rel, true);
 765                 }
 766                 elog_node_display(INFO, "root", root, true);
 767                 elog(INFO, "%s(simple_rel_array_size:%d, level:%d, query_level:%d, parent_root:%p)",
 768                         __func__, root->simple_rel_array_size, level, root->query_level, root->parent_root);
 769         }
 770
 771         for (i = 0, nrels = 0; i < root->simple_rel_array_size; i++)
 772         {
 773                 if (root->simple_rel_array[i] == NULL)
 774                         continue;
 775
 776                 relids[nrels] = palloc(sizeof(RelIdInfo));
 777
 778                 Assert(i == root->simple_rel_array[i]->relid);
 779
 780                 relids[nrels]->relid = i;
 781                 relids[nrels]->oid = root->simple_rte_array[i]->relid;
 782                 relids[nrels]->eref = root->simple_rte_array[i]->eref;
 783                 //elog(INFO, "%d:%d:%d:%s", i, relids[nrels]->relid, relids[nrels]->oid, relids[nrels]->eref->aliasname);
 784
 785                 nrels++;
 786         }
 787
 788         join_hint_level = palloc0(sizeof(List *) * (root->simple_rel_array_size));
 789
 790         for (i = 0; i < HASH_ENTRIES; i++)
 791         {
 792                 HashEntry *next;
 793
 794                 for (next = hashent[i]; next; next = next->next)
 795                 {
 796                         int     lv;
 797                         if (!(next->enforce_mask & ENABLE_HASHJOIN) &&
 798                                 !(next->enforce_mask & ENABLE_NESTLOOP) &&
 799                                 !(next->enforce_mask & ENABLE_MERGEJOIN))
 800                                 continue;
 801
 802                         if ((hint = set_relids(next, relids, nrels)) == NULL)
 803                                 continue;
 804
 805                         lv = bms_num_members(hint->joinrelids);
 806                         join_hint_level[lv] = lappend(join_hint_level[lv], hint);
 807                 }
 808         }
 809 }
 810
 811 static void
 812 backup_guc(GucVariables *backup)
 813 {
 814         backup->enable_seqscan = enable_seqscan;
 815         backup->enable_indexscan = enable_indexscan;
 816         backup->enable_bitmapscan = enable_bitmapscan;
 817         backup->enable_tidscan = enable_tidscan;
 818         backup->enable_sort = enable_sort;
 819         backup->enable_hashagg = enable_hashagg;
 820         backup->enable_nestloop = enable_nestloop;
 821         backup->enable_material = enable_material;
 822         backup->enable_mergejoin = enable_mergejoin;
 823         backup->enable_hashjoin = enable_hashjoin;
 824 }
 825
 826 static void
 827 restore_guc(GucVariables *backup)
 828 {
 829         enable_seqscan = backup->enable_seqscan;
 830         enable_indexscan = backup->enable_indexscan;
 831         enable_bitmapscan = backup->enable_bitmapscan;
 832         enable_tidscan = backup->enable_tidscan;
 833         enable_sort = backup->enable_sort;
 834         enable_hashagg = backup->enable_hashagg;
 835         enable_nestloop = backup->enable_nestloop;
 836         enable_material = backup->enable_material;
 837         enable_mergejoin = backup->enable_mergejoin;
 838         enable_hashjoin = backup->enable_hashjoin;
 839 }
 840
 841 static void
 842 set_guc(unsigned char enforce_mask)
 843 {
 844         enable_mergejoin = enforce_mask & ENABLE_MERGEJOIN ? true : false;
 845         enable_hashjoin = enforce_mask & ENABLE_HASHJOIN ? true : false;
 846         enable_nestloop = enforce_mask & ENABLE_NESTLOOP ? true : false;
 847 }
 848
 849 /*
 850  * relidビットマスクと一致するヒントを探す
 851  */
 852 static JoinHint *
 853 find_join_hint(Relids joinrelids)
 854 {
 855         List       *join_hint;
 856         ListCell   *l;
 857
 858         join_hint = join_hint_level[bms_num_members(joinrelids)];
 859         foreach(l, join_hint)
 860         {
 861                 JoinHint   *hint = (JoinHint *) lfirst(l);
 862                 if (bms_equal(joinrelids, hint->joinrelids))
 863                         return hint;
 864         }
 865
 866         return NULL;
 867 }
 868
 869 /*
 870  * src/backend/optimizer/path/joinrels.c
 871  * export make_join_rel() をラップする関数
 872  *
 873  * ヒントにしたがって、enabele_* パラメータを変更した上で、make_join_rel()を
 874  * 呼び出す。
 875  */
 876 static RelOptInfo *
 877 my_make_join_rel(PlannerInfo *root, RelOptInfo *rel1, RelOptInfo *rel2)
 878 {
 879         GucVariables    guc;
 880         Relids                  joinrelids;
 881         JoinHint           *hint;
 882         RelOptInfo         *rel;
 883
 884         joinrelids = bms_union(rel1->relids, rel2->relids);
 885         hint = find_join_hint(joinrelids);
 886         bms_free(joinrelids);
 887
 888         if (hint)
 889         {
 890                 backup_guc(&guc);
 891                 set_guc(hint->enforce_mask);
 892         }
 893
 894         rel = make_join_rel(root, rel1, rel2);
 895
 896         if (hint)
 897                 restore_guc(&guc);
 898
 899         return rel;
 900 }
 901
 902 /*
 903  * PostgreSQL 本体から流用した関数
 904  */
 905
 906 /*
 907  * src/backend/optimizer/path/allpaths.c
 908  * export standard_join_search() を流用
 909  *
 910  * 変更箇所
 911  *  build_join_hints() の呼び出しを追加
 912  */
 913 /*
 914  * standard_join_search
 915  *        Find possible joinpaths for a query by successively finding ways
 916  *        to join component relations into join relations.
 917  *
 918  * 'levels_needed' is the number of iterations needed, ie, the number of
 919  *              independent jointree items in the query.  This is > 1.
 920  *
 921  * 'initial_rels' is a list of RelOptInfo nodes for each independent
 922  *              jointree item.  These are the components to be joined together.
 923  *              Note that levels_needed == list_length(initial_rels).
 924  *
 925  * Returns the final level of join relations, i.e., the relation that is
 926  * the result of joining all the original relations together.
 927  * At least one implementation path must be provided for this relation and
 928  * all required sub-relations.
 929  *
 930  * To support loadable plugins that modify planner behavior by changing the
 931  * join searching algorithm, we provide a hook variable that lets a plugin
 932  * replace or supplement this function.  Any such hook must return the same
 933  * final join relation as the standard code would, but it might have a
 934  * different set of implementation paths attached, and only the sub-joinrels
 935  * needed for these paths need have been instantiated.
 936  *
 937  * Note to plugin authors: the functions invoked during standard_join_search()
 938  * modify root->join_rel_list and root->join_rel_hash.  If you want to do more
 939  * than one join-order search, you'll probably need to save and restore the
 940  * original states of those data structures.  See geqo_eval() for an example.
 941  */
 942 static RelOptInfo *
 943 my_join_search(PlannerInfo *root, int levels_needed, List *initial_rels)
 944 {
 945         int                     lev;
 946         RelOptInfo *rel;
 947
 948         /*
 949          * This function cannot be invoked recursively within any one planning
 950          * problem, so join_rel_level[] can't be in use already.
 951          */
 952         Assert(root->join_rel_level == NULL);
 953
 954         /*
 955          * We employ a simple "dynamic programming" algorithm: we first find all
 956          * ways to build joins of two jointree items, then all ways to build joins
 957          * of three items (from two-item joins and single items), then four-item
 958          * joins, and so on until we have considered all ways to join all the
 959          * items into one rel.
 960          *
 961          * root->join_rel_level[j] is a list of all the j-item rels.  Initially we
 962          * set root->join_rel_level[1] to represent all the single-jointree-item
 963          * relations.
 964          */
 965         root->join_rel_level = (List **) palloc0((levels_needed + 1) * sizeof(List *));
 966
 967         root->join_rel_level[1] = initial_rels;
 968
 969         build_join_hints(root, levels_needed, initial_rels);
 970
 971         for (lev = 2; lev <= levels_needed; lev++)
 972         {
 973                 ListCell   *lc;
 974
 975                 /*
 976                  * Determine all possible pairs of relations to be joined at this
 977                  * level, and build paths for making each one from every available
 978                  * pair of lower-level relations.
 979                  */
 980                 my_join_search_one_level(root, lev);
 981
 982                 /*
 983                  * Do cleanup work on each just-processed rel.
 984                  */
 985                 foreach(lc, root->join_rel_level[lev])
 986                 {
 987                         rel = (RelOptInfo *) lfirst(lc);
 988
 989                         /* Find and save the cheapest paths for this rel */
 990                         set_cheapest(rel);
 991
 992 #ifdef OPTIMIZER_DEBUG
 993                         debug_print_rel(root, rel);
 994 #endif
 995                 }
 996         }
 997
 998         /*
 999          * We should have a single rel at the final level.
1000          */
1001         if (root->join_rel_level[levels_needed] == NIL)
1002                 elog(ERROR, "failed to build any %d-way joins", levels_needed);
1003         Assert(list_length(root->join_rel_level[levels_needed]) == 1);
1004
1005         rel = (RelOptInfo *) linitial(root->join_rel_level[levels_needed]);
1006
1007         root->join_rel_level = NULL;
1008
1009         return rel;
1010 }
1011
1012 /*
1013  * src/backend/optimizer/path/joinrels.c
1014  * static join_search_one_level() を流用
1015  *
1016  * 変更箇所
1017  *  make_join_rel() の呼び出しをラップする、my_make_join_rel()の呼び出しに変更
1018  */
1019 /*
1020  * join_search_one_level
1021  *        Consider ways to produce join relations containing exactly 'level'
1022  *        jointree items.  (This is one step of the dynamic-programming method
1023  *        embodied in standard_join_search.)  Join rel nodes for each feasible
1024  *        combination of lower-level rels are created and returned in a list.
1025  *        Implementation paths are created for each such joinrel, too.
1026  *
1027  * level: level of rels we want to make this time
1028  * root->join_rel_level[j], 1 <= j < level, is a list of rels containing j items
1029  *
1030  * The result is returned in root->join_rel_level[level].
1031  */
1032 static void
1033 my_join_search_one_level(PlannerInfo *root, int level)
1034 {
1035         List      **joinrels = root->join_rel_level;
1036         ListCell   *r;
1037         int                     k;
1038
1039         Assert(joinrels[level] == NIL);
1040
1041         /* Set join_cur_level so that new joinrels are added to proper list */
1042         root->join_cur_level = level;
1043
1044         /*
1045          * First, consider left-sided and right-sided plans, in which rels of
1046          * exactly level-1 member relations are joined against initial relations.
1047          * We prefer to join using join clauses, but if we find a rel of level-1
1048          * members that has no join clauses, we will generate Cartesian-product
1049          * joins against all initial rels not already contained in it.
1050          *
1051          * In the first pass (level == 2), we try to join each initial rel to each
1052          * initial rel that appears later in joinrels[1].  (The mirror-image joins
1053          * are handled automatically by make_join_rel.)  In later passes, we try
1054          * to join rels of size level-1 from joinrels[level-1] to each initial rel
1055          * in joinrels[1].
1056          */
1057         foreach(r, joinrels[level - 1])
1058         {
1059                 RelOptInfo *old_rel = (RelOptInfo *) lfirst(r);
1060                 ListCell   *other_rels;
1061
1062                 if (level == 2)
1063                         other_rels = lnext(r);          /* only consider remaining initial
1064                                                                                  * rels */
1065                 else
1066                         other_rels = list_head(joinrels[1]);            /* consider all initial
1067                                                                                                                  * rels */
1068
1069                 if (old_rel->joininfo != NIL || old_rel->has_eclass_joins ||
1070                         has_join_restriction(root, old_rel))
1071                 {
1072                         /*
1073                          * Note that if all available join clauses for this rel require
1074                          * more than one other rel, we will fail to make any joins against
1075                          * it here.  In most cases that's OK; it'll be considered by
1076                          * "bushy plan" join code in a higher-level pass where we have
1077                          * those other rels collected into a join rel.
1078                          *
1079                          * See also the last-ditch case below.
1080                          */
1081                         make_rels_by_clause_joins(root,
1082                                                                           old_rel,
1083                                                                           other_rels);
1084                 }
1085                 else
1086                 {
1087                         /*
1088                          * Oops, we have a relation that is not joined to any other
1089                          * relation, either directly or by join-order restrictions.
1090                          * Cartesian product time.
1091                          */
1092                         make_rels_by_clauseless_joins(root,
1093                                                                                   old_rel,
1094                                                                                   other_rels);
1095                 }
1096         }
1097
1098         /*
1099          * Now, consider "bushy plans" in which relations of k initial rels are
1100          * joined to relations of level-k initial rels, for 2 <= k <= level-2.
1101          *
1102          * We only consider bushy-plan joins for pairs of rels where there is a
1103          * suitable join clause (or join order restriction), in order to avoid
1104          * unreasonable growth of planning time.
1105          */
1106         for (k = 2;; k++)
1107         {
1108                 int                     other_level = level - k;
1109
1110                 /*
1111                  * Since make_join_rel(x, y) handles both x,y and y,x cases, we only
1112                  * need to go as far as the halfway point.
1113                  */
1114                 if (k > other_level)
1115                         break;
1116
1117                 foreach(r, joinrels[k])
1118                 {
1119                         RelOptInfo *old_rel = (RelOptInfo *) lfirst(r);
1120                         ListCell   *other_rels;
1121                         ListCell   *r2;
1122
1123                         /*
1124                          * We can ignore clauseless joins here, *except* when they
1125                          * participate in join-order restrictions --- then we might have
1126                          * to force a bushy join plan.
1127                          */
1128                         if (old_rel->joininfo == NIL && !old_rel->has_eclass_joins &&
1129                                 !has_join_restriction(root, old_rel))
1130                                 continue;
1131
1132                         if (k == other_level)
1133                                 other_rels = lnext(r);  /* only consider remaining rels */
1134                         else
1135                                 other_rels = list_head(joinrels[other_level]);
1136
1137                         for_each_cell(r2, other_rels)
1138                         {
1139                                 RelOptInfo *new_rel = (RelOptInfo *) lfirst(r2);
1140
1141                                 if (!bms_overlap(old_rel->relids, new_rel->relids))
1142                                 {
1143                                         /*
1144                                          * OK, we can build a rel of the right level from this
1145                                          * pair of rels.  Do so if there is at least one usable
1146                                          * join clause or a relevant join restriction.
1147                                          */
1148                                         if (have_relevant_joinclause(root, old_rel, new_rel) ||
1149                                                 have_join_order_restriction(root, old_rel, new_rel))
1150                                         {
1151                                                 (void) my_make_join_rel(root, old_rel, new_rel);
1152                                         }
1153                                 }
1154                         }
1155                 }
1156         }
1157
1158         /*
1159          * Last-ditch effort: if we failed to find any usable joins so far, force
1160          * a set of cartesian-product joins to be generated.  This handles the
1161          * special case where all the available rels have join clauses but we
1162          * cannot use any of those clauses yet.  An example is
1163          *
1164          * SELECT * FROM a,b,c WHERE (a.f1 + b.f2 + c.f3) = 0;
1165          *
1166          * The join clause will be usable at level 3, but at level 2 we have no
1167          * choice but to make cartesian joins.  We consider only left-sided and
1168          * right-sided cartesian joins in this case (no bushy).
1169          */
1170         if (joinrels[level] == NIL)
1171         {
1172                 /*
1173                  * This loop is just like the first one, except we always call
1174                  * make_rels_by_clauseless_joins().
1175                  */
1176                 foreach(r, joinrels[level - 1])
1177                 {
1178                         RelOptInfo *old_rel = (RelOptInfo *) lfirst(r);
1179                         ListCell   *other_rels;
1180
1181                         if (level == 2)
1182                                 other_rels = lnext(r);  /* only consider remaining initial
1183                                                                                  * rels */
1184                         else
1185                                 other_rels = list_head(joinrels[1]);    /* consider all initial
1186                                                                                                                  * rels */
1187
1188                         make_rels_by_clauseless_joins(root,
1189                                                                                   old_rel,
1190                                                                                   other_rels);
1191                 }
1192
1193                 /*----------
1194                  * When special joins are involved, there may be no legal way
1195                  * to make an N-way join for some values of N.  For example consider
1196                  *
1197                  * SELECT ... FROM t1 WHERE
1198                  *       x IN (SELECT ... FROM t2,t3 WHERE ...) AND
1199                  *       y IN (SELECT ... FROM t4,t5 WHERE ...)
1200                  *
1201                  * We will flatten this query to a 5-way join problem, but there are
1202                  * no 4-way joins that join_is_legal() will consider legal.  We have
1203                  * to accept failure at level 4 and go on to discover a workable
1204                  * bushy plan at level 5.
1205                  *
1206                  * However, if there are no special joins then join_is_legal() should
1207                  * never fail, and so the following sanity check is useful.
1208                  *----------
1209                  */
1210                 if (joinrels[level] == NIL && root->join_info_list == NIL)
1211                         elog(ERROR, "failed to build any %d-way joins", level);
1212         }
1213 }
1214
1215 /*
1216  * src/backend/optimizer/path/joinrels.c
1217  * static make_rels_by_clause_joins() を流用
1218  *
1219  * 変更箇所
1220  *  make_join_rel() の呼び出しをラップする、my_make_join_rel()の呼び出しに変更
1221  */
1222 /*
1223  * make_rels_by_clause_joins
1224  *        Build joins between the given relation 'old_rel' and other relations
1225  *        that participate in join clauses that 'old_rel' also participates in
1226  *        (or participate in join-order restrictions with it).
1227  *        The join rels are returned in root->join_rel_level[join_cur_level].
1228  *
1229  * Note: at levels above 2 we will generate the same joined relation in
1230  * multiple ways --- for example (a join b) join c is the same RelOptInfo as
1231  * (b join c) join a, though the second case will add a different set of Paths
1232  * to it.  This is the reason for using the join_rel_level mechanism, which
1233  * automatically ensures that each new joinrel is only added to the list once.
1234  *
1235  * 'old_rel' is the relation entry for the relation to be joined
1236  * 'other_rels': the first cell in a linked list containing the other
1237  * rels to be considered for joining
1238  *
1239  * Currently, this is only used with initial rels in other_rels, but it
1240  * will work for joining to joinrels too.
1241  */
1242 static void
1243 make_rels_by_clause_joins(PlannerInfo *root,
1244                                                   RelOptInfo *old_rel,
1245                                                   ListCell *other_rels)
1246 {
1247         ListCell   *l;
1248
1249         for_each_cell(l, other_rels)
1250         {
1251                 RelOptInfo *other_rel = (RelOptInfo *) lfirst(l);
1252
1253                 if (!bms_overlap(old_rel->relids, other_rel->relids) &&
1254                         (have_relevant_joinclause(root, old_rel, other_rel) ||
1255                          have_join_order_restriction(root, old_rel, other_rel)))
1256                 {
1257                         (void) my_make_join_rel(root, old_rel, other_rel);
1258                 }
1259         }
1260 }
1261
1262 /*
1263  * src/backend/optimizer/path/joinrels.c
1264  * static make_rels_by_clauseless_joins() を流用
1265  *
1266  * 変更箇所
1267  *  make_join_rel() の呼び出しをラップする、my_make_join_rel()の呼び出しに変更
1268  */
1269 /*
1270  * make_rels_by_clauseless_joins
1271  *        Given a relation 'old_rel' and a list of other relations
1272  *        'other_rels', create a join relation between 'old_rel' and each
1273  *        member of 'other_rels' that isn't already included in 'old_rel'.
1274  *        The join rels are returned in root->join_rel_level[join_cur_level].
1275  *
1276  * 'old_rel' is the relation entry for the relation to be joined
1277  * 'other_rels': the first cell of a linked list containing the
1278  * other rels to be considered for joining
1279  *
1280  * Currently, this is only used with initial rels in other_rels, but it would
1281  * work for joining to joinrels too.
1282  */
1283 static void
1284 make_rels_by_clauseless_joins(PlannerInfo *root,
1285                                                           RelOptInfo *old_rel,
1286                                                           ListCell *other_rels)
1287 {
1288         ListCell   *l;
1289
1290         for_each_cell(l, other_rels)
1291         {
1292                 RelOptInfo *other_rel = (RelOptInfo *) lfirst(l);
1293
1294                 if (!bms_overlap(other_rel->relids, old_rel->relids))
1295                 {
1296                         (void) my_make_join_rel(root, old_rel, other_rel);
1297                 }
1298         }
1299 }
1300
1301 /*
1302  * src/backend/optimizer/path/joinrels.c
1303  * static has_join_restriction() を流用
1304  *
1305  * 変更箇所
1306  *  なし
1307  */
1308 /*
1309  * has_join_restriction
1310  *              Detect whether the specified relation has join-order restrictions
1311  *              due to being inside an outer join or an IN (sub-SELECT).
1312  *
1313  * Essentially, this tests whether have_join_order_restriction() could
1314  * succeed with this rel and some other one.  It's OK if we sometimes
1315  * say "true" incorrectly.      (Therefore, we don't bother with the relatively
1316  * expensive has_legal_joinclause test.)
1317  */
1318 static bool
1319 has_join_restriction(PlannerInfo *root, RelOptInfo *rel)
1320 {
1321         ListCell   *l;
1322
1323         foreach(l, root->join_info_list)
1324         {
1325                 SpecialJoinInfo *sjinfo = (SpecialJoinInfo *) lfirst(l);
1326
1327                 /* ignore full joins --- other mechanisms preserve their ordering */
1328                 if (sjinfo->jointype == JOIN_FULL)
1329                         continue;
1330
1331                 /* ignore if SJ is already contained in rel */
1332                 if (bms_is_subset(sjinfo->min_lefthand, rel->relids) &&
1333                         bms_is_subset(sjinfo->min_righthand, rel->relids))
1334                         continue;
1335
1336                 /* restricted if it overlaps LHS or RHS, but doesn't contain SJ */
1337                 if (bms_overlap(sjinfo->min_lefthand, rel->relids) ||
1338                         bms_overlap(sjinfo->min_righthand, rel->relids))
1339                         return true;
1340         }
1341
1342         return false;
1343 }