• R/O
  • R/O (HTTP)
  • R/W (SSH)
  • R/W (HTTPS)

pg_hint_plan: Commit

firtst release


Commit MetaInfo

Revisiona5663e67fa77a613ab3dbf49cdbc698239ab848d (tree)
Time2017-02-09 10:50:29
AuthorKyotaro Horiguchi <horiguchi.kyotaro@lab....>
CommiterKyotaro Horiguchi

Log Message

Refactor hint application mechamism

Before we have the new hook set_rel_pathlist_hook, plan enforcment is
performed in two steps, one works only for queries with one relation,
the other works only for joins. Now they are performed once and at
once in set_rel_pathlist_hook. As a byproduct of this refactoring,
index restriction no longer runs for indexes on constraint-excluded
relations and expected/ut-S.out changed a bit.

The major changes by this patch are the followings.

- Remove hook to get_relation_info_hook:

The function for the hook pg_hint_plan_get_relation_info is removed.

- pg_hint_plan_join_search no longer calls rebuild_scan_path. The

function and a copied function set_plain_rel_pathlist are removed.

- delete_indexes has been renamed to restrict_indexes, which

represents the functionality clearer. And the function was
refactored main for readablity.

- Added several regtests for parallel.

Change Summary

Incremental Difference

--- a/core.c
+++ b/core.c
@@ -3,15 +3,37 @@
33 * core.c
44 * Routines copied from PostgreSQL core distribution.
55 *
6+
7+ * The main purpose of this files is having access to static functions in core.
8+ * Another purpose is tweaking functions behavior by replacing part of them by
9+ * macro definitions. See at the end of pg_hint_plan.c for details. Anyway,
10+ * this file *must* contain required functions without making any change.
11+ *
12+ * This file contains the following functions from corresponding files.
13+ *
614 * src/backend/optimizer/path/allpaths.c
15+ *
16+ * static functions:
17+ * set_plain_rel_pathlist()
718 * set_append_rel_pathlist()
819 * generate_mergeappend_paths()
920 * get_cheapest_parameterized_child_path()
1021 * accumulate_append_subpath()
11- * standard_join_search()
22+ *
23+ * public functions:
24+ * standard_join_search(): This funcion is not static. The reason for
25+ * including this function is make_rels_by_clause_joins. In order to
26+ * avoid generating apparently unwanted join combination, we decided to
27+ * change the behavior of make_join_rel, which is called under this
28+ * function.
1229 *
1330 * src/backend/optimizer/path/joinrels.c
14- * join_search_one_level()
31+ *
32+ * public functions:
33+ * join_search_one_level(): We have to modify this to call my definition of
34+ * make_rels_by_clause_joins.
35+ *
36+ * static functions:
1537 * make_rels_by_clause_joins()
1638 * make_rels_by_clauseless_joins()
1739 * join_is_legal()
@@ -20,12 +42,44 @@
2042 * mark_dummy_rel()
2143 * restriction_is_constant_false()
2244 *
45+ *
2346 * Portions Copyright (c) 1996-2017, PostgreSQL Global Development Group
2447 * Portions Copyright (c) 1994, Regents of the University of California
2548 *
2649 *-------------------------------------------------------------------------
2750 */
2851
52+
53+/*
54+ * set_plain_rel_pathlist
55+ * Build access paths for a plain relation (no subquery, no inheritance)
56+ */
57+static void
58+set_plain_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
59+{
60+ Relids required_outer;
61+
62+ /*
63+ * We don't support pushing join clauses into the quals of a seqscan, but
64+ * it could still have required parameterization due to LATERAL refs in
65+ * its tlist.
66+ */
67+ required_outer = rel->lateral_relids;
68+
69+ /* Consider sequential scan */
70+ add_path(rel, create_seqscan_path(root, rel, required_outer, 0));
71+
72+ /* If appropriate, consider parallel sequential scan */
73+ if (rel->consider_parallel && required_outer == NULL)
74+ create_plain_partial_paths(root, rel);
75+
76+ /* Consider index scans */
77+ create_index_paths(root, rel);
78+
79+ /* Consider TID scans */
80+ create_tidscan_paths(root, rel);
81+}
82+
2983 /*
3084 * set_append_rel_pathlist
3185 * Build access paths for an "append relation"
--- a/expected/pg_hint_plan.out
+++ b/expected/pg_hint_plan.out
@@ -3441,34 +3441,42 @@ not used hint:
34413441 duplication hint:
34423442 error hint:
34433443
3444- QUERY PLAN
3445------------------------------------------------------------------------------------
3444+ QUERY PLAN
3445+--------------------------------------------------------
34463446 Merge Join
34473447 Merge Cond: (p1.id = t1.id)
3448- -> Sort
3448+ -> Merge Append
34493449 Sort Key: p1.id
3450- -> Append
3451- -> Seq Scan on p1
3452- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3453- -> Seq Scan on p1_c1
3454- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3455- -> Seq Scan on p1_c2
3456- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3457- -> Seq Scan on p1_c3
3458- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3459- -> Seq Scan on p1_c4
3460- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3461- -> Seq Scan on p1_c1_c1
3462- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3463- -> Seq Scan on p1_c1_c2
3464- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3465- -> Seq Scan on p1_c3_c1
3466- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3467- -> Seq Scan on p1_c3_c2
3468- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3450+ -> Index Scan using p1_pkey on p1
3451+ Index Cond: ((id >= 50) AND (id <= 51))
3452+ Filter: (ctid = '(1,1)'::tid)
3453+ -> Index Scan using p1_c1_pkey on p1_c1
3454+ Index Cond: ((id >= 50) AND (id <= 51))
3455+ Filter: (ctid = '(1,1)'::tid)
3456+ -> Index Scan using p1_c2_pkey on p1_c2
3457+ Index Cond: ((id >= 50) AND (id <= 51))
3458+ Filter: (ctid = '(1,1)'::tid)
3459+ -> Index Scan using p1_c3_pkey on p1_c3
3460+ Index Cond: ((id >= 50) AND (id <= 51))
3461+ Filter: (ctid = '(1,1)'::tid)
3462+ -> Index Scan using p1_c4_pkey on p1_c4
3463+ Index Cond: ((id >= 50) AND (id <= 51))
3464+ Filter: (ctid = '(1,1)'::tid)
3465+ -> Index Scan using p1_c1_c1_pkey on p1_c1_c1
3466+ Index Cond: ((id >= 50) AND (id <= 51))
3467+ Filter: (ctid = '(1,1)'::tid)
3468+ -> Index Scan using p1_c1_c2_pkey on p1_c1_c2
3469+ Index Cond: ((id >= 50) AND (id <= 51))
3470+ Filter: (ctid = '(1,1)'::tid)
3471+ -> Index Scan using p1_c3_c1_pkey on p1_c3_c1
3472+ Index Cond: ((id >= 50) AND (id <= 51))
3473+ Filter: (ctid = '(1,1)'::tid)
3474+ -> Index Scan using p1_c3_c2_pkey on p1_c3_c2
3475+ Index Cond: ((id >= 50) AND (id <= 51))
3476+ Filter: (ctid = '(1,1)'::tid)
34693477 -> Index Scan using t1_pkey on t1
34703478 Index Cond: (id < 10)
3471-(25 rows)
3479+(33 rows)
34723480
34733481 /*+BitmapScan(p1)*/
34743482 EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
@@ -3479,34 +3487,61 @@ not used hint:
34793487 duplication hint:
34803488 error hint:
34813489
3482- QUERY PLAN
3483------------------------------------------------------------------------------------
3490+ QUERY PLAN
3491+-------------------------------------------------------------------
34843492 Merge Join
34853493 Merge Cond: (p1.id = t1.id)
34863494 -> Sort
34873495 Sort Key: p1.id
34883496 -> Append
3489- -> Seq Scan on p1
3490- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3491- -> Seq Scan on p1_c1
3492- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3493- -> Seq Scan on p1_c2
3494- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3495- -> Seq Scan on p1_c3
3496- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3497- -> Seq Scan on p1_c4
3498- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3499- -> Seq Scan on p1_c1_c1
3500- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3501- -> Seq Scan on p1_c1_c2
3502- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3503- -> Seq Scan on p1_c3_c1
3504- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3505- -> Seq Scan on p1_c3_c2
3506- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3497+ -> Bitmap Heap Scan on p1
3498+ Recheck Cond: ((id >= 50) AND (id <= 51))
3499+ Filter: (ctid = '(1,1)'::tid)
3500+ -> Bitmap Index Scan on p1_pkey
3501+ Index Cond: ((id >= 50) AND (id <= 51))
3502+ -> Bitmap Heap Scan on p1_c1
3503+ Recheck Cond: ((id >= 50) AND (id <= 51))
3504+ Filter: (ctid = '(1,1)'::tid)
3505+ -> Bitmap Index Scan on p1_c1_pkey
3506+ Index Cond: ((id >= 50) AND (id <= 51))
3507+ -> Bitmap Heap Scan on p1_c2
3508+ Recheck Cond: ((id >= 50) AND (id <= 51))
3509+ Filter: (ctid = '(1,1)'::tid)
3510+ -> Bitmap Index Scan on p1_c2_pkey
3511+ Index Cond: ((id >= 50) AND (id <= 51))
3512+ -> Bitmap Heap Scan on p1_c3
3513+ Recheck Cond: ((id >= 50) AND (id <= 51))
3514+ Filter: (ctid = '(1,1)'::tid)
3515+ -> Bitmap Index Scan on p1_c3_pkey
3516+ Index Cond: ((id >= 50) AND (id <= 51))
3517+ -> Bitmap Heap Scan on p1_c4
3518+ Recheck Cond: ((id >= 50) AND (id <= 51))
3519+ Filter: (ctid = '(1,1)'::tid)
3520+ -> Bitmap Index Scan on p1_c4_pkey
3521+ Index Cond: ((id >= 50) AND (id <= 51))
3522+ -> Bitmap Heap Scan on p1_c1_c1
3523+ Recheck Cond: ((id >= 50) AND (id <= 51))
3524+ Filter: (ctid = '(1,1)'::tid)
3525+ -> Bitmap Index Scan on p1_c1_c1_pkey
3526+ Index Cond: ((id >= 50) AND (id <= 51))
3527+ -> Bitmap Heap Scan on p1_c1_c2
3528+ Recheck Cond: ((id >= 50) AND (id <= 51))
3529+ Filter: (ctid = '(1,1)'::tid)
3530+ -> Bitmap Index Scan on p1_c1_c2_pkey
3531+ Index Cond: ((id >= 50) AND (id <= 51))
3532+ -> Bitmap Heap Scan on p1_c3_c1
3533+ Recheck Cond: ((id >= 50) AND (id <= 51))
3534+ Filter: (ctid = '(1,1)'::tid)
3535+ -> Bitmap Index Scan on p1_c3_c1_pkey
3536+ Index Cond: ((id >= 50) AND (id <= 51))
3537+ -> Bitmap Heap Scan on p1_c3_c2
3538+ Recheck Cond: ((id >= 50) AND (id <= 51))
3539+ Filter: (ctid = '(1,1)'::tid)
3540+ -> Bitmap Index Scan on p1_c3_c2_pkey
3541+ Index Cond: ((id >= 50) AND (id <= 51))
35073542 -> Index Scan using t1_pkey on t1
35083543 Index Cond: (id < 10)
3509-(25 rows)
3544+(52 rows)
35103545
35113546 /*+TidScan(p1)*/
35123547 EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
@@ -3517,34 +3552,43 @@ not used hint:
35173552 duplication hint:
35183553 error hint:
35193554
3520- QUERY PLAN
3521------------------------------------------------------------------------------------
3555+ QUERY PLAN
3556+---------------------------------------------------------
35223557 Merge Join
35233558 Merge Cond: (p1.id = t1.id)
35243559 -> Sort
35253560 Sort Key: p1.id
35263561 -> Append
3527- -> Seq Scan on p1
3528- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3529- -> Seq Scan on p1_c1
3530- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3531- -> Seq Scan on p1_c2
3532- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3533- -> Seq Scan on p1_c3
3534- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3535- -> Seq Scan on p1_c4
3536- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3537- -> Seq Scan on p1_c1_c1
3538- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3539- -> Seq Scan on p1_c1_c2
3540- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3541- -> Seq Scan on p1_c3_c1
3542- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3543- -> Seq Scan on p1_c3_c2
3544- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3562+ -> Tid Scan on p1
3563+ TID Cond: (ctid = '(1,1)'::tid)
3564+ Filter: ((id >= 50) AND (id <= 51))
3565+ -> Tid Scan on p1_c1
3566+ TID Cond: (ctid = '(1,1)'::tid)
3567+ Filter: ((id >= 50) AND (id <= 51))
3568+ -> Tid Scan on p1_c2
3569+ TID Cond: (ctid = '(1,1)'::tid)
3570+ Filter: ((id >= 50) AND (id <= 51))
3571+ -> Tid Scan on p1_c3
3572+ TID Cond: (ctid = '(1,1)'::tid)
3573+ Filter: ((id >= 50) AND (id <= 51))
3574+ -> Tid Scan on p1_c4
3575+ TID Cond: (ctid = '(1,1)'::tid)
3576+ Filter: ((id >= 50) AND (id <= 51))
3577+ -> Tid Scan on p1_c1_c1
3578+ TID Cond: (ctid = '(1,1)'::tid)
3579+ Filter: ((id >= 50) AND (id <= 51))
3580+ -> Tid Scan on p1_c1_c2
3581+ TID Cond: (ctid = '(1,1)'::tid)
3582+ Filter: ((id >= 50) AND (id <= 51))
3583+ -> Tid Scan on p1_c3_c1
3584+ TID Cond: (ctid = '(1,1)'::tid)
3585+ Filter: ((id >= 50) AND (id <= 51))
3586+ -> Tid Scan on p1_c3_c2
3587+ TID Cond: (ctid = '(1,1)'::tid)
3588+ Filter: ((id >= 50) AND (id <= 51))
35453589 -> Index Scan using t1_pkey on t1
35463590 Index Cond: (id < 10)
3547-(25 rows)
3591+(34 rows)
35483592
35493593 /*+NestLoop(p1 t1)*/
35503594 EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
@@ -3696,24 +3740,27 @@ not used hint:
36963740 duplication hint:
36973741 error hint:
36983742
3699- QUERY PLAN
3700------------------------------------------------------------------------------------
3743+ QUERY PLAN
3744+--------------------------------------------------------
37013745 Merge Join
37023746 Merge Cond: (p1.id = t1.id)
3703- -> Sort
3747+ -> Merge Append
37043748 Sort Key: p1.id
3705- -> Append
3706- -> Seq Scan on p1
3707- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3708- -> Seq Scan on p1_c1
3709- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3710- -> Seq Scan on p1_c1_c1
3711- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3712- -> Seq Scan on p1_c1_c2
3713- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3749+ -> Index Scan using p1_pkey on p1
3750+ Index Cond: ((id >= 50) AND (id <= 51))
3751+ Filter: (ctid = '(1,1)'::tid)
3752+ -> Index Scan using p1_c1_pkey on p1_c1
3753+ Index Cond: ((id >= 50) AND (id <= 51))
3754+ Filter: (ctid = '(1,1)'::tid)
3755+ -> Index Scan using p1_c1_c1_pkey on p1_c1_c1
3756+ Index Cond: ((id >= 50) AND (id <= 51))
3757+ Filter: (ctid = '(1,1)'::tid)
3758+ -> Index Scan using p1_c1_c2_pkey on p1_c1_c2
3759+ Index Cond: ((id >= 50) AND (id <= 51))
3760+ Filter: (ctid = '(1,1)'::tid)
37143761 -> Index Scan using t1_pkey on t1
37153762 Index Cond: (id < 10)
3716-(15 rows)
3763+(18 rows)
37173764
37183765 /*+BitmapScan(p1)*/
37193766 EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
@@ -3724,24 +3771,36 @@ not used hint:
37243771 duplication hint:
37253772 error hint:
37263773
3727- QUERY PLAN
3728------------------------------------------------------------------------------------
3774+ QUERY PLAN
3775+-------------------------------------------------------------------
37293776 Merge Join
37303777 Merge Cond: (p1.id = t1.id)
37313778 -> Sort
37323779 Sort Key: p1.id
37333780 -> Append
3734- -> Seq Scan on p1
3735- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3736- -> Seq Scan on p1_c1
3737- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3738- -> Seq Scan on p1_c1_c1
3739- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3740- -> Seq Scan on p1_c1_c2
3741- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3781+ -> Bitmap Heap Scan on p1
3782+ Recheck Cond: ((id >= 50) AND (id <= 51))
3783+ Filter: (ctid = '(1,1)'::tid)
3784+ -> Bitmap Index Scan on p1_pkey
3785+ Index Cond: ((id >= 50) AND (id <= 51))
3786+ -> Bitmap Heap Scan on p1_c1
3787+ Recheck Cond: ((id >= 50) AND (id <= 51))
3788+ Filter: (ctid = '(1,1)'::tid)
3789+ -> Bitmap Index Scan on p1_c1_pkey
3790+ Index Cond: ((id >= 50) AND (id <= 51))
3791+ -> Bitmap Heap Scan on p1_c1_c1
3792+ Recheck Cond: ((id >= 50) AND (id <= 51))
3793+ Filter: (ctid = '(1,1)'::tid)
3794+ -> Bitmap Index Scan on p1_c1_c1_pkey
3795+ Index Cond: ((id >= 50) AND (id <= 51))
3796+ -> Bitmap Heap Scan on p1_c1_c2
3797+ Recheck Cond: ((id >= 50) AND (id <= 51))
3798+ Filter: (ctid = '(1,1)'::tid)
3799+ -> Bitmap Index Scan on p1_c1_c2_pkey
3800+ Index Cond: ((id >= 50) AND (id <= 51))
37423801 -> Index Scan using t1_pkey on t1
37433802 Index Cond: (id < 10)
3744-(15 rows)
3803+(27 rows)
37453804
37463805 /*+TidScan(p1)*/
37473806 EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
@@ -3752,24 +3811,28 @@ not used hint:
37523811 duplication hint:
37533812 error hint:
37543813
3755- QUERY PLAN
3756------------------------------------------------------------------------------------
3814+ QUERY PLAN
3815+---------------------------------------------------------
37573816 Merge Join
37583817 Merge Cond: (p1.id = t1.id)
37593818 -> Sort
37603819 Sort Key: p1.id
37613820 -> Append
3762- -> Seq Scan on p1
3763- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3764- -> Seq Scan on p1_c1
3765- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3766- -> Seq Scan on p1_c1_c1
3767- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3768- -> Seq Scan on p1_c1_c2
3769- Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid))
3821+ -> Tid Scan on p1
3822+ TID Cond: (ctid = '(1,1)'::tid)
3823+ Filter: ((id >= 50) AND (id <= 51))
3824+ -> Tid Scan on p1_c1
3825+ TID Cond: (ctid = '(1,1)'::tid)
3826+ Filter: ((id >= 50) AND (id <= 51))
3827+ -> Tid Scan on p1_c1_c1
3828+ TID Cond: (ctid = '(1,1)'::tid)
3829+ Filter: ((id >= 50) AND (id <= 51))
3830+ -> Tid Scan on p1_c1_c2
3831+ TID Cond: (ctid = '(1,1)'::tid)
3832+ Filter: ((id >= 50) AND (id <= 51))
37703833 -> Index Scan using t1_pkey on t1
37713834 Index Cond: (id < 10)
3772-(15 rows)
3835+(19 rows)
37733836
37743837 /*+NestLoop(p1 t1)*/
37753838 EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
@@ -6428,13 +6491,8 @@ EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid =
64286491 EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
64296492 LOG: available indexes for IndexScan(p2): p2_pkey
64306493 LOG: available indexes for IndexScan(p2_c1): p2_c1_pkey
6431-LOG: available indexes for IndexScan(p2_c2): p2_c2_pkey
6432-LOG: available indexes for IndexScan(p2_c3): p2_c3_pkey
6433-LOG: available indexes for IndexScan(p2_c4): p2_c4_pkey
64346494 LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_pkey
64356495 LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_pkey
6436-LOG: available indexes for IndexScan(p2_c3_c1): p2_c3_c1_pkey
6437-LOG: available indexes for IndexScan(p2_c3_c2): p2_c3_c2_pkey
64386496 LOG: pg_hint_plan:
64396497 used hint:
64406498 IndexScan(p2 p2_pkey)
@@ -6463,13 +6521,8 @@ error hint:
64636521 EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
64646522 LOG: available indexes for IndexScan(p2): p2_id_val_idx
64656523 LOG: available indexes for IndexScan(p2_c1): p2_c1_id_val_idx
6466-LOG: available indexes for IndexScan(p2_c2): p2_c2_id_val_idx
6467-LOG: available indexes for IndexScan(p2_c3): p2_c3_id_val_idx
6468-LOG: available indexes for IndexScan(p2_c4): p2_c4_id_val_idx
64696524 LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_id_val_idx
64706525 LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_id_val_idx
6471-LOG: available indexes for IndexScan(p2_c3_c1): p2_c3_c1_id_val_idx
6472-LOG: available indexes for IndexScan(p2_c3_c2): p2_c3_c2_id_val_idx
64736526 LOG: pg_hint_plan:
64746527 used hint:
64756528 IndexScan(p2 p2_id_val_idx)
@@ -6498,13 +6551,8 @@ error hint:
64986551 EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
64996552 LOG: available indexes for IndexScan(p2): p2_val_id_idx
65006553 LOG: available indexes for IndexScan(p2_c1): p2_c1_val_id_idx
6501-LOG: available indexes for IndexScan(p2_c2): p2_c2_val_id_idx
6502-LOG: available indexes for IndexScan(p2_c3): p2_c3_val_id_idx
6503-LOG: available indexes for IndexScan(p2_c4):
65046554 LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_val_id_idx
65056555 LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_val_id_idx
6506-LOG: available indexes for IndexScan(p2_c3_c1): p2_c3_c1_val_id_idx
6507-LOG: available indexes for IndexScan(p2_c3_c2): p2_c3_c2_val_id_idx
65086556 LOG: pg_hint_plan:
65096557 used hint:
65106558 IndexScan(p2 p2_val_id_idx)
@@ -6598,13 +6646,8 @@ error hint:
65986646 EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
65996647 LOG: available indexes for IndexScan(p2): p2_pkey
66006648 LOG: available indexes for IndexScan(p2_c1): p2_c1_pkey
6601-LOG: available indexes for IndexScan(p2_c2): p2_c2_pkey
6602-LOG: available indexes for IndexScan(p2_c3): p2_c3_pkey
6603-LOG: available indexes for IndexScan(p2_c4): p2_c4_pkey
66046649 LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_pkey
66056650 LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_pkey
6606-LOG: available indexes for IndexScan(p2_c3_c1): p2_c3_c1_pkey
6607-LOG: available indexes for IndexScan(p2_c3_c2): p2_c3_c2_pkey
66086651 LOG: pg_hint_plan:
66096652 used hint:
66106653 IndexScan(p2 p2_pkey)
@@ -6633,13 +6676,8 @@ error hint:
66336676 EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
66346677 LOG: available indexes for IndexScan(p2): p2_id2_val
66356678 LOG: available indexes for IndexScan(p2_c1): p2_c1_id2_val
6636-LOG: available indexes for IndexScan(p2_c2): p2_c2_id2_val
6637-LOG: available indexes for IndexScan(p2_c3):
6638-LOG: available indexes for IndexScan(p2_c4):
66396679 LOG: available indexes for IndexScan(p2_c1_c1):
66406680 LOG: available indexes for IndexScan(p2_c1_c2):
6641-LOG: available indexes for IndexScan(p2_c3_c1):
6642-LOG: available indexes for IndexScan(p2_c3_c2):
66436681 LOG: pg_hint_plan:
66446682 used hint:
66456683 IndexScan(p2 p2_id2_val)
@@ -6666,13 +6704,8 @@ error hint:
66666704 EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
66676705 LOG: available indexes for IndexScan(p2): p2_val2_id
66686706 LOG: available indexes for IndexScan(p2_c1):
6669-LOG: available indexes for IndexScan(p2_c2):
6670-LOG: available indexes for IndexScan(p2_c3):
6671-LOG: available indexes for IndexScan(p2_c4):
66726707 LOG: available indexes for IndexScan(p2_c1_c1):
66736708 LOG: available indexes for IndexScan(p2_c1_c2):
6674-LOG: available indexes for IndexScan(p2_c3_c1):
6675-LOG: available indexes for IndexScan(p2_c3_c2):
66766709 LOG: pg_hint_plan:
66776710 used hint:
66786711 IndexScan(p2 p2_val2_id)
@@ -6698,13 +6731,8 @@ error hint:
66986731 EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
66996732 LOG: available indexes for IndexScan(p2): p2_pkey
67006733 LOG: available indexes for IndexScan(p2_c1): p2_c1_pkey
6701-LOG: available indexes for IndexScan(p2_c2): p2_c2_pkey
6702-LOG: available indexes for IndexScan(p2_c3): p2_c3_pkey
6703-LOG: available indexes for IndexScan(p2_c4): p2_c4_pkey
67046734 LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_pkey
67056735 LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_pkey
6706-LOG: available indexes for IndexScan(p2_c3_c1): p2_c3_c1_pkey
6707-LOG: available indexes for IndexScan(p2_c3_c2): p2_c3_c2_pkey
67086736 LOG: pg_hint_plan:
67096737 used hint:
67106738 IndexScan(p2 p2_pkey)
@@ -6733,13 +6761,8 @@ error hint:
67336761 EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
67346762 LOG: available indexes for IndexScan(p2):
67356763 LOG: available indexes for IndexScan(p2_c1): p2_c1_id_val_idx
6736-LOG: available indexes for IndexScan(p2_c2):
6737-LOG: available indexes for IndexScan(p2_c3):
6738-LOG: available indexes for IndexScan(p2_c4):
67396764 LOG: available indexes for IndexScan(p2_c1_c1):
67406765 LOG: available indexes for IndexScan(p2_c1_c2):
6741-LOG: available indexes for IndexScan(p2_c3_c1):
6742-LOG: available indexes for IndexScan(p2_c3_c2):
67436766 LOG: pg_hint_plan:
67446767 used hint:
67456768 IndexScan(p2 p2_c1_id_val_idx)
@@ -6765,13 +6788,8 @@ error hint:
67656788 EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
67666789 LOG: available indexes for IndexScan(p2):
67676790 LOG: available indexes for IndexScan(p2_c1):
6768-LOG: available indexes for IndexScan(p2_c2):
6769-LOG: available indexes for IndexScan(p2_c3):
6770-LOG: available indexes for IndexScan(p2_c4):
67716791 LOG: available indexes for IndexScan(p2_c1_c1):
67726792 LOG: available indexes for IndexScan(p2_c1_c2):
6773-LOG: available indexes for IndexScan(p2_c3_c1):
6774-LOG: available indexes for IndexScan(p2_c3_c2):
67756793 LOG: pg_hint_plan:
67766794 used hint:
67776795 IndexScan(p2 no_exist)
@@ -6796,13 +6814,8 @@ error hint:
67966814 EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
67976815 LOG: available indexes for IndexScan(p2): p2_pkey
67986816 LOG: available indexes for IndexScan(p2_c1): p2_c1_id_val_idx p2_c1_pkey
6799-LOG: available indexes for IndexScan(p2_c2): p2_c2_pkey
6800-LOG: available indexes for IndexScan(p2_c3): p2_c3_pkey
6801-LOG: available indexes for IndexScan(p2_c4): p2_c4_pkey
68026817 LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_pkey
68036818 LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_pkey
6804-LOG: available indexes for IndexScan(p2_c3_c1): p2_c3_c1_pkey
6805-LOG: available indexes for IndexScan(p2_c3_c2): p2_c3_c2_pkey
68066819 LOG: pg_hint_plan:
68076820 used hint:
68086821 IndexScan(p2 p2_pkey p2_c1_id_val_idx)
@@ -6831,13 +6844,8 @@ error hint:
68316844 EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
68326845 LOG: available indexes for IndexScan(p2): p2_pkey
68336846 LOG: available indexes for IndexScan(p2_c1): p2_c1_pkey
6834-LOG: available indexes for IndexScan(p2_c2): p2_c2_pkey
6835-LOG: available indexes for IndexScan(p2_c3): p2_c3_pkey
6836-LOG: available indexes for IndexScan(p2_c4): p2_c4_pkey
68376847 LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_pkey
68386848 LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_pkey
6839-LOG: available indexes for IndexScan(p2_c3_c1): p2_c3_c1_pkey
6840-LOG: available indexes for IndexScan(p2_c3_c2): p2_c3_c2_pkey
68416849 LOG: pg_hint_plan:
68426850 used hint:
68436851 IndexScan(p2 p2_pkey no_exist)
@@ -6866,13 +6874,8 @@ error hint:
68666874 EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
68676875 LOG: available indexes for IndexScan(p2):
68686876 LOG: available indexes for IndexScan(p2_c1): p2_c1_id_val_idx
6869-LOG: available indexes for IndexScan(p2_c2):
6870-LOG: available indexes for IndexScan(p2_c3):
6871-LOG: available indexes for IndexScan(p2_c4):
68726877 LOG: available indexes for IndexScan(p2_c1_c1):
68736878 LOG: available indexes for IndexScan(p2_c1_c2):
6874-LOG: available indexes for IndexScan(p2_c3_c1):
6875-LOG: available indexes for IndexScan(p2_c3_c2):
68766879 LOG: pg_hint_plan:
68776880 used hint:
68786881 IndexScan(p2 p2_c1_id_val_idx no_exist)
@@ -6898,13 +6901,8 @@ error hint:
68986901 EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
68996902 LOG: available indexes for IndexScan(p2): p2_pkey
69006903 LOG: available indexes for IndexScan(p2_c1): p2_c1_id_val_idx p2_c1_pkey
6901-LOG: available indexes for IndexScan(p2_c2): p2_c2_pkey
6902-LOG: available indexes for IndexScan(p2_c3): p2_c3_pkey
6903-LOG: available indexes for IndexScan(p2_c4): p2_c4_pkey
69046904 LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_pkey
69056905 LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_pkey
6906-LOG: available indexes for IndexScan(p2_c3_c1): p2_c3_c1_pkey
6907-LOG: available indexes for IndexScan(p2_c3_c2): p2_c3_c2_pkey
69086906 LOG: pg_hint_plan:
69096907 used hint:
69106908 IndexScan(p2 p2_pkey p2_c1_id_val_idx no_exist)
--- a/expected/ut-S.out
+++ b/expected/ut-S.out
@@ -5103,8 +5103,6 @@ error hint:
51035103 EXPLAIN (COSTS false) UPDATE s1.p1 SET c4 = c4 WHERE c1 = 1;
51045104 LOG: available indexes for IndexScan(p1): p1_pkey
51055105 LOG: available indexes for IndexScan(p1c1): p1c1_pkey
5106-LOG: available indexes for IndexScan(p1c2): p1c2_pkey
5107-LOG: available indexes for IndexScan(p1c3): p1c3_pkey
51085106 LOG: pg_hint_plan:
51095107 used hint:
51105108 IndexScan(p1 p1_pkey)
@@ -5263,8 +5261,6 @@ EXPLAIN SELECT c4 FROM s1.p1 WHERE c2 * 2 < 100 AND c1 < 10;
52635261 /*+IndexScan(p1 p1_parent)*/ EXPLAIN SELECT c4 FROM s1.p1 WHERE c2 * 2 < 100 AND c1 < 10;
52645262 LOG: available indexes for IndexScan(p1): p1_parent
52655263 LOG: available indexes for IndexScan(p1c1): p1c1_c4_expr_idx
5266-LOG: available indexes for IndexScan(p1c2): p1c2_c4_expr_idx
5267-LOG: available indexes for IndexScan(p1c3): p1c3_c4_expr_idx
52685264 LOG: pg_hint_plan:
52695265 used hint:
52705266 IndexScan(p1 p1_parent)
@@ -5317,14 +5313,7 @@ error hint:
53175313 /*+IndexScan(p2 p2c1_pkey)*/ EXPLAIN (COSTS true) SELECT * FROM s1.p2 WHERE c1 = 1;
53185314 LOG: available indexes for IndexScan(p2):
53195315 LOG: available indexes for IndexScan(p2c1): p2c1_pkey
5320-LOG: available indexes for IndexScan(p2c2):
5321-LOG: available indexes for IndexScan(p2c3):
53225316 LOG: available indexes for IndexScan(p2c1c1):
5323-LOG: available indexes for IndexScan(p2c1c2):
5324-LOG: available indexes for IndexScan(p2c2c1):
5325-LOG: available indexes for IndexScan(p2c2c2):
5326-LOG: available indexes for IndexScan(p2c3c1):
5327-LOG: available indexes for IndexScan(p2c3c2):
53285317 LOG: pg_hint_plan:
53295318 used hint:
53305319 IndexScan(p2 p2c1_pkey)
@@ -6056,8 +6045,6 @@ error hint:
60566045 EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
60576046 LOG: available indexes for IndexScanRegexp(p1): p1_pkey
60586047 LOG: available indexes for IndexScanRegexp(p1c1): p1c1_pkey
6059-LOG: available indexes for IndexScanRegexp(p1c2): p1c2_pkey
6060-LOG: available indexes for IndexScanRegexp(p1c3): p1c3_pkey
60616048 LOG: pg_hint_plan:
60626049 used hint:
60636050 IndexScanRegexp(p1 .*pkey)
@@ -6079,8 +6066,6 @@ error hint:
60796066 EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
60806067 LOG: available indexes for IndexScanRegexp(p1): p1_i2 p1_i
60816068 LOG: available indexes for IndexScanRegexp(p1c1): p1c1_i p1c1_c4_expr_idx
6082-LOG: available indexes for IndexScanRegexp(p1c2): p1c2_i p1c2_c4_expr_idx
6083-LOG: available indexes for IndexScanRegexp(p1c3): p1c3_i p1c3_c4_expr_idx
60846069 LOG: pg_hint_plan:
60856070 used hint:
60866071 IndexScanRegexp(p1 p1.*i)
@@ -6102,8 +6087,6 @@ error hint:
61026087 EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
61036088 LOG: available indexes for IndexScanRegexp(p1):
61046089 LOG: available indexes for IndexScanRegexp(p1c1):
6105-LOG: available indexes for IndexScanRegexp(p1c2):
6106-LOG: available indexes for IndexScanRegexp(p1c3):
61076090 LOG: pg_hint_plan:
61086091 used hint:
61096092 IndexScanRegexp(p1 no.*_exist)
--- a/expected/ut-W.out
+++ b/expected/ut-W.out
@@ -3,22 +3,18 @@ ALTER SYSTEM SET session_preload_libraries TO 'pg_hint_plan';
33 SET pg_hint_plan.enable_hint TO on;
44 SET pg_hint_plan.debug_print TO on;
55 SET client_min_messages TO LOG;
6-SET search_path TO public;
7-SET max_parallel_workers_per_gather TO 0;
8-SET enable_indexscan to false;
9-SET enable_bitmapscan to false;
10-SET parallel_setup_cost to 0;
11-SET parallel_tuple_cost to 0;
12-SET min_parallel_relation_size to 0;
13-SET max_parallel_workers_per_gather to 0;
146 CREATE TABLE s1.tl (a int);
157 INSERT INTO s1.tl (SELECT a FROM generate_series(0, 100000) a);
8+-- Queries on ordinary tables
169 EXPLAIN (COSTS false) SELECT * FROM s1.t1;
1710 QUERY PLAN
1811 ----------------
1912 Seq Scan on t1
2013 (1 row)
2114
15+SET parallel_setup_cost to 0;
16+SET parallel_tuple_cost to 0;
17+SET min_parallel_relation_size to 0;
2218 /*+Parallel(t1 10)*/
2319 EXPLAIN (COSTS false) SELECT * FROM s1.t1;
2420 LOG: pg_hint_plan:
@@ -51,6 +47,9 @@ error hint:
5147 -> Parallel Seq Scan on t1
5248 (3 rows)
5349
50+SET parallel_setup_cost to DEFAULT;
51+SET parallel_tuple_cost to DEFAULT;
52+SET min_parallel_relation_size to DEFAULT;
5453 /*+Parallel(t1 10 hard)*/
5554 EXPLAIN (COSTS false) SELECT * FROM s1.t1;
5655 LOG: pg_hint_plan:
@@ -67,8 +66,11 @@ error hint:
6766 -> Parallel Seq Scan on t1
6867 (3 rows)
6968
70--- Inheritnce tables
71-/*+Parallel(p1 10 soft)*/
69+-- Queries on inheritance tables
70+SET parallel_setup_cost to 0;
71+SET parallel_tuple_cost to 0;
72+SET min_parallel_relation_size to 0;
73+/*+Parallel(p1 10)*/
7274 EXPLAIN (COSTS false) SELECT * FROM p1;
7375 LOG: pg_hint_plan:
7476 used hint:
@@ -93,6 +95,9 @@ error hint:
9395 -> Parallel Seq Scan on p1_c3_c2
9496 (12 rows)
9597
98+SET parallel_setup_cost to DEFAULT;
99+SET parallel_tuple_cost to DEFAULT;
100+SET min_parallel_relation_size to DEFAULT;
96101 /*+Parallel(p1 10 hard)*/
97102 EXPLAIN (COSTS false) SELECT * FROM p1;
98103 LOG: pg_hint_plan:
@@ -118,6 +123,30 @@ error hint:
118123 -> Parallel Seq Scan on p1_c3_c2
119124 (12 rows)
120125
126+-- hinting on children don't work
127+/*+Parallel(p1_c1 10 hard)*/
128+EXPLAIN (COSTS false) SELECT * FROM p1;
129+LOG: pg_hint_plan:
130+used hint:
131+not used hint:
132+Parallel(p1_c1 10 hard)
133+duplication hint:
134+error hint:
135+
136+ QUERY PLAN
137+----------------------------
138+ Append
139+ -> Seq Scan on p1
140+ -> Seq Scan on p1_c1
141+ -> Seq Scan on p1_c2
142+ -> Seq Scan on p1_c3
143+ -> Seq Scan on p1_c4
144+ -> Seq Scan on p1_c1_c1
145+ -> Seq Scan on p1_c1_c2
146+ -> Seq Scan on p1_c3_c1
147+ -> Seq Scan on p1_c3_c2
148+(10 rows)
149+
121150 -- Joins
122151 EXPLAIN (COSTS false) SELECT * FROM p1_c1 join p2_c1 on p1_c1.id = p2_c1.id;
123152 QUERY PLAN
@@ -217,6 +246,9 @@ error hint:
217246 (15 rows)
218247
219248 -- Joins on inheritance tables
249+SET parallel_setup_cost to 0;
250+SET parallel_tuple_cost to 0;
251+SET min_parallel_relation_size to 0;
220252 /*+Parallel(p1 10)*/
221253 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
222254 LOG: pg_hint_plan:
@@ -255,6 +287,9 @@ error hint:
255287 -> Seq Scan on p2_c3_c2
256288 (25 rows)
257289
290+SET parallel_setup_cost to DEFAULT;
291+SET parallel_tuple_cost to DEFAULT;
292+SET min_parallel_relation_size to DEFAULT;
258293 /*+Parallel(p2 10 hard)*/
259294 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
260295 LOG: pg_hint_plan:
@@ -334,8 +369,265 @@ error hint:
334369 -> Parallel Seq Scan on p2_c3_c2
335370 (27 rows)
336371
372+-- Mixture with a scan hint
373+-- p1 can be parallel
374+/*+Parallel(p1 10 hard) IndexScan(p2) */
375+EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
376+LOG: pg_hint_plan:
377+used hint:
378+IndexScan(p2)
379+Parallel(p1 10 hard)
380+not used hint:
381+duplication hint:
382+error hint:
383+
384+ QUERY PLAN
385+--------------------------------------------------------------
386+ Hash Join
387+ Hash Cond: (p2.id = p1.id)
388+ -> Append
389+ -> Index Scan using p2_id2_val on p2
390+ -> Index Scan using p2_c1_id2_val on p2_c1
391+ -> Index Scan using p2_c2_id2_val on p2_c2
392+ -> Index Scan using p2_c3_id_val_idx on p2_c3
393+ -> Index Scan using p2_c4_id_val_idx on p2_c4
394+ -> Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1
395+ -> Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2
396+ -> Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1
397+ -> Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2
398+ -> Hash
399+ -> Gather
400+ Workers Planned: 10
401+ -> Append
402+ -> Parallel Seq Scan on p1
403+ -> Parallel Seq Scan on p1_c1
404+ -> Parallel Seq Scan on p1_c2
405+ -> Parallel Seq Scan on p1_c3
406+ -> Parallel Seq Scan on p1_c4
407+ -> Parallel Seq Scan on p1_c1_c1
408+ -> Parallel Seq Scan on p1_c1_c2
409+ -> Parallel Seq Scan on p1_c3_c1
410+ -> Parallel Seq Scan on p1_c3_c2
411+(25 rows)
412+
413+-- seqscan doesn't harm parallelism
414+/*+Parallel(p1 10 hard) SeqScan(p1) */
415+EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
416+LOG: pg_hint_plan:
417+used hint:
418+SeqScan(p1)
419+Parallel(p1 10 hard)
420+not used hint:
421+duplication hint:
422+error hint:
423+
424+ QUERY PLAN
425+-------------------------------------------------
426+ Gather
427+ Workers Planned: 10
428+ -> Hash Join
429+ Hash Cond: (p1.id = p2.id)
430+ -> Append
431+ -> Parallel Seq Scan on p1
432+ -> Parallel Seq Scan on p1_c1
433+ -> Parallel Seq Scan on p1_c2
434+ -> Parallel Seq Scan on p1_c3
435+ -> Parallel Seq Scan on p1_c4
436+ -> Parallel Seq Scan on p1_c1_c1
437+ -> Parallel Seq Scan on p1_c1_c2
438+ -> Parallel Seq Scan on p1_c3_c1
439+ -> Parallel Seq Scan on p1_c3_c2
440+ -> Hash
441+ -> Append
442+ -> Seq Scan on p2
443+ -> Seq Scan on p2_c1
444+ -> Seq Scan on p2_c2
445+ -> Seq Scan on p2_c3
446+ -> Seq Scan on p2_c4
447+ -> Seq Scan on p2_c1_c1
448+ -> Seq Scan on p2_c1_c2
449+ -> Seq Scan on p2_c3_c1
450+ -> Seq Scan on p2_c3_c2
451+(25 rows)
452+
453+-- parallelism is not available for the case
454+/*+Parallel(p1 10 hard) IndexScan(p1) */
455+EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
456+LOG: pg_hint_plan:
457+used hint:
458+IndexScan(p1)
459+Parallel(p1 10 hard)
460+not used hint:
461+duplication hint:
462+error hint:
463+
464+ QUERY PLAN
465+--------------------------------------------------------
466+ Hash Join
467+ Hash Cond: (p1.id = p2.id)
468+ -> Append
469+ -> Index Scan using p1_pkey on p1
470+ -> Index Scan using p1_c1_pkey on p1_c1
471+ -> Index Scan using p1_c2_pkey on p1_c2
472+ -> Index Scan using p1_c3_pkey on p1_c3
473+ -> Index Scan using p1_c4_pkey on p1_c4
474+ -> Index Scan using p1_c1_c1_pkey on p1_c1_c1
475+ -> Index Scan using p1_c1_c2_pkey on p1_c1_c2
476+ -> Index Scan using p1_c3_c1_pkey on p1_c3_c1
477+ -> Index Scan using p1_c3_c2_pkey on p1_c3_c2
478+ -> Hash
479+ -> Append
480+ -> Seq Scan on p2
481+ -> Seq Scan on p2_c1
482+ -> Seq Scan on p2_c2
483+ -> Seq Scan on p2_c3
484+ -> Seq Scan on p2_c4
485+ -> Seq Scan on p2_c1_c1
486+ -> Seq Scan on p2_c1_c2
487+ -> Seq Scan on p2_c3_c1
488+ -> Seq Scan on p2_c3_c2
489+(23 rows)
490+
491+-- Parallel on UNION
492+EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
493+ QUERY PLAN
494+----------------------------
495+ Append
496+ -> Seq Scan on p1
497+ -> Seq Scan on p1_c1
498+ -> Seq Scan on p1_c2
499+ -> Seq Scan on p1_c3
500+ -> Seq Scan on p1_c4
501+ -> Seq Scan on p1_c1_c1
502+ -> Seq Scan on p1_c1_c2
503+ -> Seq Scan on p1_c3_c1
504+ -> Seq Scan on p1_c3_c2
505+ -> Seq Scan on p2
506+ -> Seq Scan on p2_c1
507+ -> Seq Scan on p2_c2
508+ -> Seq Scan on p2_c3
509+ -> Seq Scan on p2_c4
510+ -> Seq Scan on p2_c1_c1
511+ -> Seq Scan on p2_c1_c2
512+ -> Seq Scan on p2_c3_c1
513+ -> Seq Scan on p2_c3_c2
514+(19 rows)
515+
516+-- some of the scans are not parallel, so this cannot be parallel
517+SET parallel_setup_cost to 0;
518+SET parallel_tuple_cost to 0;
519+SET min_parallel_relation_size to 0;
520+/*+Parallel(p1 10) */
521+EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
522+LOG: pg_hint_plan:
523+used hint:
524+Parallel(p1 10 soft)
525+not used hint:
526+duplication hint:
527+error hint:
528+
529+ QUERY PLAN
530+----------------------------
531+ Append
532+ -> Seq Scan on p1
533+ -> Seq Scan on p1_c1
534+ -> Seq Scan on p1_c2
535+ -> Seq Scan on p1_c3
536+ -> Seq Scan on p1_c4
537+ -> Seq Scan on p1_c1_c1
538+ -> Seq Scan on p1_c1_c2
539+ -> Seq Scan on p1_c3_c1
540+ -> Seq Scan on p1_c3_c2
541+ -> Seq Scan on p2
542+ -> Seq Scan on p2_c1
543+ -> Seq Scan on p2_c2
544+ -> Seq Scan on p2_c3
545+ -> Seq Scan on p2_c4
546+ -> Seq Scan on p2_c1_c1
547+ -> Seq Scan on p2_c1_c2
548+ -> Seq Scan on p2_c3_c1
549+ -> Seq Scan on p2_c3_c2
550+(19 rows)
551+
552+-- all children are parallel, so this can be parallel
553+/*+Parallel(p1 10) Parallel(p2 10) */
554+EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
555+LOG: pg_hint_plan:
556+used hint:
557+Parallel(p1 10 soft)
558+Parallel(p2 10 soft)
559+not used hint:
560+duplication hint:
561+error hint:
562+
563+ QUERY PLAN
564+-------------------------------------------
565+ Gather
566+ Workers Planned: 1
567+ -> Append
568+ -> Parallel Seq Scan on p1
569+ -> Parallel Seq Scan on p1_c1
570+ -> Parallel Seq Scan on p1_c2
571+ -> Parallel Seq Scan on p1_c3
572+ -> Parallel Seq Scan on p1_c4
573+ -> Parallel Seq Scan on p1_c1_c1
574+ -> Parallel Seq Scan on p1_c1_c2
575+ -> Parallel Seq Scan on p1_c3_c1
576+ -> Parallel Seq Scan on p1_c3_c2
577+ -> Parallel Seq Scan on p2
578+ -> Parallel Seq Scan on p2_c1
579+ -> Parallel Seq Scan on p2_c2
580+ -> Parallel Seq Scan on p2_c3
581+ -> Parallel Seq Scan on p2_c4
582+ -> Parallel Seq Scan on p2_c1_c1
583+ -> Parallel Seq Scan on p2_c1_c2
584+ -> Parallel Seq Scan on p2_c3_c1
585+ -> Parallel Seq Scan on p2_c3_c2
586+(21 rows)
587+
588+SET parallel_setup_cost to DEFAULT;
589+SET parallel_tuple_cost to DEFAULT;
590+SET min_parallel_relation_size to DEFAULT;
591+/*+Parallel(p1 10 hard)Parallel(p2 10 hard) */
592+EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
593+LOG: pg_hint_plan:
594+used hint:
595+Parallel(p1 10 hard)
596+Parallel(p2 10 hard)
597+not used hint:
598+duplication hint:
599+error hint:
600+
601+ QUERY PLAN
602+-------------------------------------------
603+ Gather
604+ Workers Planned: 10
605+ -> Append
606+ -> Parallel Seq Scan on p1
607+ -> Parallel Seq Scan on p1_c1
608+ -> Parallel Seq Scan on p1_c2
609+ -> Parallel Seq Scan on p1_c3
610+ -> Parallel Seq Scan on p1_c4
611+ -> Parallel Seq Scan on p1_c1_c1
612+ -> Parallel Seq Scan on p1_c1_c2
613+ -> Parallel Seq Scan on p1_c3_c1
614+ -> Parallel Seq Scan on p1_c3_c2
615+ -> Parallel Seq Scan on p2
616+ -> Parallel Seq Scan on p2_c1
617+ -> Parallel Seq Scan on p2_c2
618+ -> Parallel Seq Scan on p2_c3
619+ -> Parallel Seq Scan on p2_c4
620+ -> Parallel Seq Scan on p2_c1_c1
621+ -> Parallel Seq Scan on p2_c1_c2
622+ -> Parallel Seq Scan on p2_c3_c1
623+ -> Parallel Seq Scan on p2_c3_c2
624+(21 rows)
625+
337626 -- Negative hint
338627 SET max_parallel_workers_per_gather to 5;
628+SET parallel_setup_cost to 0;
629+SET parallel_tuple_cost to 0;
630+SET min_parallel_relation_size to 0;
339631 EXPLAIN (COSTS false) SELECT * FROM p1;
340632 QUERY PLAN
341633 -------------------------------------------
--- a/make_join_rel.c
+++ b/make_join_rel.c
@@ -1,7 +1,8 @@
11 /*-------------------------------------------------------------------------
22 *
33 * make_join_rel.c
4- * Routines copied from PostgreSQL core distribution.
4+ * Routines copied from PostgreSQL core distribution with some
5+ * modifications.
56 *
67 * src/backend/optimizer/path/joinrels.c
78 * make_join_rel()
--- a/pg_hint_plan.c
+++ b/pg_hint_plan.c
@@ -374,9 +374,6 @@ static void pg_hint_plan_ProcessUtility(Node *parsetree,
374374 DestReceiver *dest, char *completionTag);
375375 static PlannedStmt *pg_hint_plan_planner(Query *parse, int cursorOptions,
376376 ParamListInfo boundParams);
377-static void pg_hint_plan_get_relation_info(PlannerInfo *root,
378- Oid relationObjectId,
379- bool inhparent, RelOptInfo *rel);
380377 static RelOptInfo *pg_hint_plan_join_search(PlannerInfo *root,
381378 int levels_needed,
382379 List *initial_rels);
@@ -456,6 +453,8 @@ static void make_rels_by_clauseless_joins(PlannerInfo *root,
456453 RelOptInfo *old_rel,
457454 ListCell *other_rels);
458455 static bool has_join_restriction(PlannerInfo *root, RelOptInfo *rel);
456+static void set_plain_rel_pathlist(PlannerInfo *root, RelOptInfo *rel,
457+ RangeTblEntry *rte);
459458 static void set_append_rel_pathlist(PlannerInfo *root, RelOptInfo *rel,
460459 Index rti, RangeTblEntry *rte);
461460 static void generate_mergeappend_paths(PlannerInfo *root, RelOptInfo *rel,
@@ -537,7 +536,6 @@ static const struct config_enum_entry parse_debug_level_options[] = {
537536 /* Saved hook values in case of unload */
538537 static ProcessUtility_hook_type prev_ProcessUtility = NULL;
539538 static planner_hook_type prev_planner = NULL;
540-static get_relation_info_hook_type prev_get_relation_info = NULL;
541539 static join_search_hook_type prev_join_search = NULL;
542540 static set_rel_pathlist_hook_type prev_set_rel_pathlist = NULL;
543541
@@ -671,8 +669,6 @@ _PG_init(void)
671669 ProcessUtility_hook = pg_hint_plan_ProcessUtility;
672670 prev_planner = planner_hook;
673671 planner_hook = pg_hint_plan_planner;
674- prev_get_relation_info = get_relation_info_hook;
675- get_relation_info_hook = pg_hint_plan_get_relation_info;
676672 prev_join_search = join_search_hook;
677673 join_search_hook = pg_hint_plan_join_search;
678674 prev_set_rel_pathlist = set_rel_pathlist_hook;
@@ -697,7 +693,6 @@ _PG_fini(void)
697693 /* Uninstall hooks. */
698694 ProcessUtility_hook = prev_ProcessUtility;
699695 planner_hook = prev_planner;
700- get_relation_info_hook = prev_get_relation_info;
701696 join_search_hook = prev_join_search;
702697 set_rel_pathlist_hook = prev_set_rel_pathlist;
703698
@@ -2528,7 +2523,7 @@ setup_guc_enforcement(SetHint **options, int noptions, GucContext context)
25282523 * If hint is not NULL, set up using it, elsewise reset to initial environment.
25292524 */
25302525 static void
2531-setup_parallel_plan_enfocement(ParallelHint *hint, HintState *state)
2526+setup_parallel_plan_enforcement(ParallelHint *hint, HintState *state)
25322527 {
25332528 if (hint)
25342529 {
@@ -3041,9 +3036,9 @@ find_parallel_hint(PlannerInfo *root, Index relid, RelOptInfo *rel)
30413036 * - not a base relation and inheritance children
30423037 * - not an ordinary relation (such as join and subquery)
30433038 */
3044- if (rel && ((rel->reloptkind != RELOPT_BASEREL &&
3045- rel->reloptkind != RELOPT_OTHER_MEMBER_REL) ||
3046- rel->rtekind != RTE_RELATION))
3039+ if (!rel || rel->rtekind != RTE_RELATION ||
3040+ (rel->reloptkind != RELOPT_BASEREL &&
3041+ rel->reloptkind != RELOPT_OTHER_MEMBER_REL))
30473042 return NULL;
30483043
30493044 rte = root->simple_rte_array[relid];
@@ -3095,13 +3090,18 @@ regexpeq(const char *s1, const char *s2)
30953090 return DatumGetBool(result);
30963091 }
30973092
3093+
3094+/* Remove indexes instructed not to use by hint. */
30983095 static void
3099-delete_indexes(ScanMethodHint *hint, RelOptInfo *rel, Oid relationObjectId)
3096+restrict_indexes(PlannerInfo *root, ScanMethodHint *hint, RelOptInfo *rel,
3097+ bool using_parent_hint)
31003098 {
31013099 ListCell *cell;
31023100 ListCell *prev;
31033101 ListCell *next;
31043102 StringInfoData buf;
3103+ RangeTblEntry *rte = root->simple_rte_array[rel->relid];
3104+ Oid relationObjectId = rte->relid;
31053105
31063106 /*
31073107 * We delete all the IndexOptInfo list and prevent you from being usable by
@@ -3164,10 +3164,11 @@ delete_indexes(ScanMethodHint *hint, RelOptInfo *rel, Oid relationObjectId)
31643164 }
31653165
31663166 /*
3167- * to make the index a candidate when definition of this index is
3168- * matched with the index's definition of current_hint_state.
3167+ * Apply index restriction of parent hint to children. Since index
3168+ * inheritance is not explicitly described we should search for an
3169+ * children's index with the same definition to that of the parent.
31693170 */
3170- if (OidIsValid(relationObjectId) && !use_index)
3171+ if (using_parent_hint && !use_index)
31713172 {
31723173 foreach(l, current_hint_state->parent_index_infos)
31733174 {
@@ -3175,71 +3176,61 @@ delete_indexes(ScanMethodHint *hint, RelOptInfo *rel, Oid relationObjectId)
31753176 HeapTuple ht_idx;
31763177 ParentIndexInfo *p_info = (ParentIndexInfo *)lfirst(l);
31773178
3178- /* check to match the parameter of unique */
3179- if (p_info->indisunique != info->unique)
3180- continue;
3181-
3182- /* check to match the parameter of index's method */
3183- if (p_info->method != info->relam)
3184- continue;
3185-
3186- /* to check to match the indexkey's configuration */
3187- if ((list_length(p_info->column_names)) !=
3188- info->ncolumns)
3179+ /*
3180+ * we check the 'same' index by comparing uniqueness, access
3181+ * method and index key columns.
3182+ */
3183+ if (p_info->indisunique != info->unique ||
3184+ p_info->method != info->relam ||
3185+ list_length(p_info->column_names) != info->ncolumns)
31893186 continue;
31903187
3191- /* check to match the indexkey's configuration */
3188+ /* Check if index key columns match */
31923189 for (i = 0; i < info->ncolumns; i++)
31933190 {
31943191 char *c_attname = NULL;
31953192 char *p_attname = NULL;
31963193
3197- p_attname =
3198- list_nth(p_info->column_names, i);
3194+ p_attname = list_nth(p_info->column_names, i);
31993195
3200- /* both are expressions */
3196+ /*
3197+ * if both of the key of the same position are expressions,
3198+ * ignore them for now and check later.
3199+ */
32013200 if (info->indexkeys[i] == 0 && !p_attname)
32023201 continue;
32033202
3204- /* one's column is expression, the other is not */
3203+ /* deny if one is expression while another is not */
32053204 if (info->indexkeys[i] == 0 || !p_attname)
32063205 break;
32073206
32083207 c_attname = get_attname(relationObjectId,
32093208 info->indexkeys[i]);
32103209
3211- if (strcmp(p_attname, c_attname) != 0)
3212- break;
3213-
3214- if (p_info->indcollation[i] != info->indexcollations[i])
3215- break;
3216-
3217- if (p_info->opclass[i] != info->opcintype[i])
3218- break;
3219-
3220- if (((p_info->indoption[i] & INDOPTION_DESC) != 0) !=
3221- info->reverse_sort[i])
3210+ /* deny if any of column attributes don't match */
3211+ if (strcmp(p_attname, c_attname) != 0 ||
3212+ p_info->indcollation[i] != info->indexcollations[i] ||
3213+ p_info->opclass[i] != info->opcintype[i]||
3214+ ((p_info->indoption[i] & INDOPTION_DESC) != 0)
3215+ != info->reverse_sort[i] ||
3216+ ((p_info->indoption[i] & INDOPTION_NULLS_FIRST) != 0)
3217+ != info->nulls_first[i])
32223218 break;
3223-
3224- if (((p_info->indoption[i] & INDOPTION_NULLS_FIRST) != 0) !=
3225- info->nulls_first[i])
3226- break;
3227-
32283219 }
32293220
3221+ /* deny this if any difference found */
32303222 if (i != info->ncolumns)
32313223 continue;
32323224
3225+ /* check on key expressions */
32333226 if ((p_info->expression_str && (info->indexprs != NIL)) ||
32343227 (p_info->indpred_str && (info->indpred != NIL)))
32353228 {
3236- /*
3237- * Fetch the pg_index tuple by the Oid of the index
3238- */
3229+ /* fetch the index of this child */
32393230 ht_idx = SearchSysCache1(INDEXRELID,
32403231 ObjectIdGetDatum(info->indexoid));
32413232
3242- /* check to match the expression's parameter of index */
3233+ /* check expressions if both expressions are available */
32433234 if (p_info->expression_str &&
32443235 !heap_attisnull(ht_idx, Anum_pg_index_indexprs))
32453236 {
@@ -3260,17 +3251,17 @@ delete_indexes(ScanMethodHint *hint, RelOptInfo *rel, Oid relationObjectId)
32603251 ObjectIdGetDatum(
32613252 relationObjectId));
32623253
3254+ /* deny if expressions don't match */
32633255 if (strcmp(p_info->expression_str,
32643256 text_to_cstring(DatumGetTextP(result))) != 0)
32653257 {
32663258 /* Clean up */
32673259 ReleaseSysCache(ht_idx);
3268-
32693260 continue;
32703261 }
32713262 }
32723263
3273- /* Check to match the predicate's parameter of index */
3264+ /* compare index predicates */
32743265 if (p_info->indpred_str &&
32753266 !heap_attisnull(ht_idx, Anum_pg_index_indpred))
32763267 {
@@ -3278,10 +3269,6 @@ delete_indexes(ScanMethodHint *hint, RelOptInfo *rel, Oid relationObjectId)
32783269 bool isnull;
32793270 Datum result;
32803271
3281- /*
3282- * to change the predicate's parameter of child's
3283- * index to strings
3284- */
32853272 predDatum = SysCacheGetAttr(INDEXRELID, ht_idx,
32863273 Anum_pg_index_indpred,
32873274 &isnull);
@@ -3296,7 +3283,6 @@ delete_indexes(ScanMethodHint *hint, RelOptInfo *rel, Oid relationObjectId)
32963283 {
32973284 /* Clean up */
32983285 ReleaseSysCache(ht_idx);
3299-
33003286 continue;
33013287 }
33023288 }
@@ -3332,16 +3318,21 @@ delete_indexes(ScanMethodHint *hint, RelOptInfo *rel, Oid relationObjectId)
33323318
33333319 if (debug_level == 1)
33343320 {
3335- char *relname;
33363321 StringInfoData rel_buf;
3322+ char *disprelname = "";
33373323
3338- if (OidIsValid(relationObjectId))
3339- relname = get_rel_name(relationObjectId);
3324+ /*
3325+ * If this hint targetted the parent, use the real name of this
3326+ * child. Otherwise use hint specification.
3327+ */
3328+ if (using_parent_hint)
3329+ disprelname = get_rel_name(rte->relid);
33403330 else
3341- relname = hint->relname;
3331+ disprelname = hint->relname;
3332+
33423333
33433334 initStringInfo(&rel_buf);
3344- quote_value(&rel_buf, relname);
3335+ quote_value(&rel_buf, disprelname);
33453336
33463337 ereport(LOG,
33473338 (errmsg("available indexes for %s(%s):%s",
@@ -3433,15 +3424,27 @@ get_parent_index_info(Oid indexoid, Oid relid)
34333424 }
34343425
34353426 /*
3436- * Set planner guc parameters according to corresponding scan hints.
3427+ * cancel hint enforcement
34373428 */
34383429 static void
3439-setup_hint_enforcement(PlannerInfo *root, Oid relationObjectId,
3440- bool inhparent, RelOptInfo *rel)
3430+reset_hint_enforcement()
34413431 {
3442- Index new_parent_relid = 0;
3432+ setup_scan_method_enforcement(NULL, current_hint_state);
3433+ setup_parallel_plan_enforcement(NULL, current_hint_state);
3434+}
3435+
3436+/*
3437+ * Set planner guc parameters according to corresponding scan hints.
3438+ */
3439+static bool
3440+setup_hint_enforcement(PlannerInfo *root, RelOptInfo *rel)
3441+{
3442+ Index new_parent_relid = 0;
34433443 ListCell *l;
3444- ScanMethodHint *scanhint = NULL;
3444+ ScanMethodHint *shint = NULL;
3445+ ParallelHint *phint = NULL;
3446+ bool inhparent = root->simple_rte_array[rel->relid]->inh;
3447+ Oid relationObjectId = root->simple_rte_array[rel->relid]->relid;
34453448
34463449 /*
34473450 * We could register the parent relation of the following children here
@@ -3455,13 +3458,13 @@ setup_hint_enforcement(PlannerInfo *root, Oid relationObjectId,
34553458 if (debug_level > 1)
34563459 ereport(pg_hint_plan_message_level,
34573460 (errhidestmt(true),
3458- errmsg ("pg_hint_plan%s: get_relation_info"
3461+ errmsg ("pg_hint_plan%s: setup_hint_enforcement"
34593462 " skipping inh parent: relation=%u(%s), inhparent=%d,"
34603463 " current_hint_state=%p, hint_inhibit_level=%d",
34613464 qnostr, relationObjectId,
34623465 get_rel_name(relationObjectId),
34633466 inhparent, current_hint_state, hint_inhibit_level)));
3464- return;
3467+ return false;
34653468 }
34663469
34673470 /* Find the parent for this relation other than the registered parent */
@@ -3492,28 +3495,27 @@ setup_hint_enforcement(PlannerInfo *root, Oid relationObjectId,
34923495 * hint to other childrens of this parent so remember it * to avoid
34933496 * hinthintredundant setup cost.
34943497 */
3495- ScanMethodHint *parent_scan_hint = NULL;
3496- ParallelHint *parent_parallel_hint = NULL;
3497-
34983498 current_hint_state->parent_relid = new_parent_relid;
34993499
35003500 /* Find hints for the parent */
3501- current_hint_state->parent_scan_hint = parent_scan_hint =
3501+ current_hint_state->parent_scan_hint =
35023502 find_scan_hint(root, current_hint_state->parent_relid, NULL);
35033503
3504- current_hint_state->parent_parallel_hint = parent_parallel_hint =
3504+ current_hint_state->parent_parallel_hint =
35053505 find_parallel_hint(root, current_hint_state->parent_relid, NULL);
35063506
3507- if (parent_scan_hint)
3507+ /*
3508+ * If hint is found for the parent, apply it for this child instead
3509+ * of its own.
3510+ */
3511+ if (current_hint_state->parent_scan_hint)
35083512 {
3509- /*
3510- * If hint is found for the parent, apply it for this child instead
3511- * of its own.
3512- */
3513- parent_scan_hint->base.state = HINT_STATE_USED;
3513+ ScanMethodHint * pshint = current_hint_state->parent_scan_hint;
3514+
3515+ pshint->base.state = HINT_STATE_USED;
35143516
35153517 /* Apply index mask in the same manner to the parent. */
3516- if (parent_scan_hint->indexnames)
3518+ if (pshint->indexnames)
35173519 {
35183520 Oid parentrel_oid;
35193521 Relation parent_rel;
@@ -3530,7 +3532,7 @@ setup_hint_enforcement(PlannerInfo *root, Oid relationObjectId,
35303532 ListCell *lc;
35313533 ParentIndexInfo *parent_index_info;
35323534
3533- foreach(lc, parent_scan_hint->indexnames)
3535+ foreach(lc, pshint->indexnames)
35343536 {
35353537 if (RelnameCmp(&indexname, &lfirst(lc)) == 0)
35363538 break;
@@ -3541,106 +3543,82 @@ setup_hint_enforcement(PlannerInfo *root, Oid relationObjectId,
35413543 parent_index_info =
35423544 get_parent_index_info(indexoid, parentrel_oid);
35433545 current_hint_state->parent_index_infos =
3544- lappend(current_hint_state->parent_index_infos, parent_index_info);
3546+ lappend(current_hint_state->parent_index_infos,
3547+ parent_index_info);
35453548 }
35463549 heap_close(parent_rel, NoLock);
35473550 }
35483551 }
3549-
3550- /* Setup planner environment */
3551- setup_scan_method_enforcement(parent_scan_hint, current_hint_state);
3552- setup_parallel_plan_enfocement(
3553- find_parallel_hint(root, rel->relid, rel), current_hint_state);
35543552 }
35553553
3556- /* Process index restriction hint inheritance */
3557- if (current_hint_state->parent_scan_hint != 0)
3558- {
3559- delete_indexes(current_hint_state->parent_scan_hint, rel,
3560- relationObjectId);
3561-
3562- /* Scan fixation status is the same to the parent. */
3563- if (debug_level > 1)
3564- ereport(pg_hint_plan_message_level,
3565- (errhidestmt(true),
3566- errmsg("pg_hint_plan%s: get_relation_info:"
3567- " index deletion by parent hint: "
3568- "relation=%u(%s), inhparent=%d, current_hint_state=%p,"
3569- " hint_inhibit_level=%d",
3570- qnostr, relationObjectId,
3571- get_rel_name(relationObjectId),
3572- inhparent, current_hint_state, hint_inhibit_level)));
3573- return;
3574- }
3554+ if (current_hint_state->parent_scan_hint)
3555+ shint = current_hint_state->parent_scan_hint;
3556+ else
3557+ shint = find_scan_hint(root, rel->relid, rel);
35753558
3576- /* This table doesn't have a parent hint. Apply its own hint if any. */
3577- if ((scanhint = find_scan_hint(root, rel->relid, rel)) != NULL)
3559+ if (shint)
35783560 {
3579- setup_scan_method_enforcement(scanhint, current_hint_state);
3561+ bool using_parent_hint =
3562+ (shint == current_hint_state->parent_scan_hint);
35803563
3581- delete_indexes(scanhint, rel, InvalidOid);
3564+ /* Setup scan enforcement environment */
3565+ setup_scan_method_enforcement(shint, current_hint_state);
3566+
3567+ /* restrict unwanted inexes */
3568+ restrict_indexes(root, shint, rel, using_parent_hint);
35823569
35833570 if (debug_level > 1)
3571+ {
3572+ char *additional_message = "";
3573+
3574+ if (shint == current_hint_state->parent_scan_hint)
3575+ additional_message = " by parent hint";
3576+
35843577 ereport(pg_hint_plan_message_level,
35853578 (errhidestmt(true),
3586- errmsg ("pg_hint_plan%s: get_relation_info"
3587- " index deletion:"
3588- " relation=%u(%s), inhparent=%d, current_hint=%p,"
3579+ errmsg ("pg_hint_plan%s: setup_hint_enforcement"
3580+ " index deletion%s:"
3581+ " relation=%u(%s), inhparent=%d, "
3582+ "current_hint_state=%p,"
35893583 " hint_inhibit_level=%d, scanmask=0x%x",
3590- qnostr, relationObjectId,
3584+ qnostr, additional_message,
3585+ relationObjectId,
35913586 get_rel_name(relationObjectId),
3592- inhparent, current_hint_state, hint_inhibit_level,
3593- scanhint->enforce_mask)));
3594- return;
3587+ inhparent, current_hint_state,
3588+ hint_inhibit_level,
3589+ shint->enforce_mask)));
3590+ }
35953591 }
35963592
35973593 /* Do the same for parallel plan enforcement */
3598- setup_parallel_plan_enfocement(find_parallel_hint(root, rel->relid, rel),
3599- current_hint_state);
3600-
3601- /* Nothing to apply. Reset the scan mask to intial state */
3602- if (debug_level > 1)
3603- ereport(pg_hint_plan_message_level,
3604- (errhidestmt (true),
3605- errmsg ("pg_hint_plan%s: get_relation_info"
3606- " no hint applied:"
3607- " relation=%u(%s), inhparent=%d, current_hint=%p,"
3608- " hint_inhibit_level=%d, scanmask=0x%x",
3609- qnostr, relationObjectId,
3610- get_rel_name(relationObjectId),
3611- inhparent, current_hint_state, hint_inhibit_level,
3612- current_hint_state->init_scan_mask)));
3613- setup_scan_method_enforcement(NULL, current_hint_state);
3614-}
3594+ if (current_hint_state->parent_parallel_hint)
3595+ phint = current_hint_state->parent_parallel_hint;
3596+ else
3597+ phint = find_parallel_hint(root, rel->relid, rel);
36153598
3616-static void
3617-pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId,
3618- bool inhparent, RelOptInfo *rel)
3619-{
3620- if (prev_get_relation_info)
3621- (*prev_get_relation_info) (root, relationObjectId, inhparent, rel);
3599+ setup_parallel_plan_enforcement(phint, current_hint_state);
36223600
3623- /*
3624- * Do nothing if we don't have a valid hint in this context or current
3625- * nesting depth is at SPI calls.
3626- */
3627- if (!current_hint_state || hint_inhibit_level > 0)
3601+ /* Nothing to apply. Reset the scan mask to intial state */
3602+ if (!shint && ! phint)
36283603 {
36293604 if (debug_level > 1)
36303605 ereport(pg_hint_plan_message_level,
36313606 (errhidestmt (true),
36323607 errmsg ("pg_hint_plan%s: get_relation_info"
3633- " no hint to apply: relation=%u(%s), inhparent=%d,"
3634- " current_hint_state=%p, hint_inhibit_level=%d",
3608+ " no hint applied:"
3609+ " relation=%u(%s), inhparent=%d, current_hint=%p,"
3610+ " hint_inhibit_level=%d, scanmask=0x%x",
36353611 qnostr, relationObjectId,
36363612 get_rel_name(relationObjectId),
3637- inhparent, current_hint_state, hint_inhibit_level)));
3638- return;
3639- }
3613+ inhparent, current_hint_state, hint_inhibit_level,
3614+ current_hint_state->init_scan_mask)));
36403615
3641- setup_hint_enforcement(root, relationObjectId, inhparent, rel);
3616+ setup_scan_method_enforcement(NULL, current_hint_state);
36423617
3643- return;
3618+ return false;
3619+ }
3620+
3621+ return true;
36443622 }
36453623
36463624 /*
@@ -4086,150 +4064,6 @@ transform_join_hints(HintState *hstate, PlannerInfo *root, int nbaserel,
40864064 }
40874065
40884066 /*
4089- * set_plain_rel_pathlist
4090- * Build access paths for a plain relation (no subquery, no inheritance)
4091- *
4092- * This function was copied and edited from set_plain_rel_pathlist() in
4093- * src/backend/optimizer/path/allpaths.c
4094- *
4095- * - removed parallel stuff.
4096- */
4097-static void
4098-set_plain_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
4099-{
4100- Relids required_outer;
4101-
4102- /*
4103- * We don't support pushing join clauses into the quals of a seqscan, but
4104- * it could still have required parameterization due to LATERAL refs in
4105- * its tlist.
4106- */
4107- required_outer = rel->lateral_relids;
4108-
4109- /* Consider sequential scan */
4110- add_path(rel, create_seqscan_path(root, rel, required_outer, 0));
4111-
4112- /* If appropriate, consider parallel sequential scan */
4113- if (rel->consider_parallel && required_outer == NULL)
4114- {
4115- ParallelHint *phint = find_parallel_hint(root, rel->relid, rel);
4116-
4117- /* Consider parallel paths only if not inhibited by hint */
4118- if (!phint || phint->nworkers > 0)
4119- create_plain_partial_paths(root, rel);
4120-
4121- /*
4122- * Overwirte parallel_workers if requested. partial_pathlist seems to
4123- * have up to one path but looping over all possible paths don't harm.
4124- */
4125- if (phint && phint->nworkers > 0 && phint->force_parallel)
4126- {
4127- ListCell *l;
4128- foreach (l, rel->partial_pathlist)
4129- {
4130- Path *ppath = (Path *) lfirst(l);
4131-
4132- Assert(ppath->parallel_workers > 0);
4133- ppath->parallel_workers = phint->nworkers;
4134- }
4135- }
4136- }
4137-
4138- /* Consider index scans */
4139- create_index_paths(root, rel);
4140-
4141- /* Consider TID scans */
4142- create_tidscan_paths(root, rel);
4143-}
4144-
4145-/*
4146- * Clear exiting access paths and create new ones applying hints.
4147- * This does the similar to set_rel_pathlist
4148- */
4149-static void
4150-rebuild_scan_path(HintState *hstate, PlannerInfo *root, int level,
4151- List *initial_rels)
4152-{
4153- ListCell *l;
4154-
4155- foreach(l, initial_rels)
4156- {
4157- RelOptInfo *rel = (RelOptInfo *) lfirst(l);
4158- RangeTblEntry *rte;
4159-
4160- /* Skip relations which we can't choose scan method. */
4161- if (rel->reloptkind != RELOPT_BASEREL || rel->rtekind != RTE_RELATION)
4162- continue;
4163-
4164- rte = root->simple_rte_array[rel->relid];
4165-
4166- /* We can't force scan method of foreign tables */
4167- if (rte->relkind == RELKIND_FOREIGN_TABLE)
4168- continue;
4169-
4170- /*
4171- * Create scan paths with GUC parameters which are at the beginning of
4172- * planner if scan method hint is not specified, otherwise use
4173- * specified hints and mark the hint as used.
4174- */
4175- setup_scan_method_enforcement(find_scan_hint(root, rel->relid, rel),
4176- hstate);
4177-
4178- /* Setup parallel environment according to the hint */
4179- setup_parallel_plan_enfocement(
4180- find_parallel_hint(root, rel->relid, rel), current_hint_state);
4181-
4182- /* remove existing partial paths from this baserel */
4183- list_free_deep(rel->partial_pathlist);
4184- rel->partial_pathlist = NIL;
4185-
4186- /* remove existing paths from this baserel */
4187- list_free_deep(rel->pathlist);
4188- rel->pathlist = NIL;
4189-
4190- if (rte->inh)
4191- {
4192- ListCell *l;
4193-
4194- /* remove partial paths from all chlidren */
4195- foreach (l, root->append_rel_list)
4196- {
4197- AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(l);
4198- RelOptInfo *childrel;
4199-
4200- if (appinfo->parent_relid != rel->relid)
4201- continue;
4202-
4203- childrel = root->simple_rel_array[appinfo->child_relid];
4204- list_free_deep(childrel->partial_pathlist);
4205- childrel->partial_pathlist = NIL;
4206- }
4207- /* It's an "append relation", process accordingly */
4208- set_append_rel_pathlist(root, rel, rel->relid, rte);
4209- }
4210- else
4211- {
4212- set_plain_rel_pathlist(root, rel, rte);
4213- }
4214-
4215- /*
4216- * If this is a baserel, consider gathering any partial paths we may
4217- * hinthave created for it.
4218- */
4219- if (rel->reloptkind == RELOPT_BASEREL)
4220- generate_gather_paths(root, rel);
4221-
4222- /* Now find the cheapest of the paths for this rel */
4223- set_cheapest(rel);
4224- }
4225-
4226- /*
4227- * Restore the GUC variables we set above.
4228- */
4229- setup_scan_method_enforcement(NULL, hstate);
4230-}
4231-
4232-/*
42334067 * wrapper of make_join_rel()
42344068 *
42354069 * call make_join_rel() after changing enable_* parameters according to given
@@ -4380,9 +4214,6 @@ pg_hint_plan_join_search(PlannerInfo *root, int levels_needed,
43804214 return standard_join_search(root, levels_needed, initial_rels);
43814215 }
43824216
4383- /* apply scan method hint rebuild scan path. */
4384- rebuild_scan_path(current_hint_state, root, levels_needed, initial_rels);
4385-
43864217 /*
43874218 * In the case using GEQO, only scan method hints and Set hints have
43884219 * effect. Join method and join order is not controllable by hints.
@@ -4428,55 +4259,94 @@ pg_hint_plan_set_rel_pathlist(PlannerInfo * root, RelOptInfo *rel,
44284259 Index rti, RangeTblEntry *rte)
44294260 {
44304261 ParallelHint *phint;
4431- List *oldpathlist;
44324262 ListCell *l;
4433- bool regenerate = false;
44344263
4435- /* Hint has not been parsed yet, or this is not a parallel'able relation */
4436- if (current_hint_state == NULL || rel->partial_pathlist == NIL)
4264+ /* call the previous hook */
4265+ if (prev_set_rel_pathlist)
4266+ prev_set_rel_pathlist(root, rel, rti, rte);
4267+
4268+ /* Nothing to do when hint has not been parsed yet */
4269+ if (current_hint_state == NULL)
44374270 return;
44384271
4439- phint = find_parallel_hint(root, rel->relid, rel);
4272+ /* Don't touch dummy rel */
4273+ if (IS_DUMMY_REL(rel))
4274+ return;
44404275
4441- if (phint == NULL || !phint->force_parallel)
4276+ /* We cannot handle if this requires an outer */
4277+ if (rel->lateral_relids)
44424278 return;
44434279
4444- /*
4445- * This relation contains gather paths previously created and they prevent
4446- * adding new gahter path with same cost. Remove them.
4447- */
4448- oldpathlist = rel->pathlist;
4449- rel->pathlist = NIL;
4450- foreach (l, oldpathlist)
4280+ if (!setup_hint_enforcement(root, rel))
44514281 {
4452- Path *path = (Path *) lfirst(l);
4282+ /*
4283+ * No enforcement requested, but we might have to generate gather
4284+ * path on this relation
4285+ */
44534286
4454- if (IsA(path, GatherPath) &&
4455- path->parallel_workers != phint->nworkers)
4287+ /* If no need of a gather path, just return */
4288+ if (rel->reloptkind != RELOPT_BASEREL || max_hint_nworkers < 1 ||
4289+ rel->partial_pathlist == NIL)
4290+ return;
4291+
4292+ /* Lower the priorities of existing paths, then add a new path */
4293+ foreach (l, rel->pathlist)
44564294 {
4457- pfree(path);
4458- regenerate = true;
4295+ Path *path = (Path *) lfirst(l);
4296+
4297+ if (path->startup_cost < disable_cost)
4298+ {
4299+ path->startup_cost += disable_cost;
4300+ path->total_cost += disable_cost;
4301+ }
44594302 }
4460- else
4461- rel->pathlist = lappend(rel->pathlist, path);
4303+
4304+ generate_gather_paths(root, rel);
4305+ return;
44624306 }
4463- list_free(oldpathlist);
44644307
4465- if (regenerate)
4308+ /* Here, we regenerate paths with the current hint restriction */
4309+
4310+ /* Remove prviously paths except dummy rels */
4311+ list_free_deep(rel->pathlist);
4312+ rel->pathlist = NIL;
4313+
4314+ /* Rebuild access paths */
4315+ set_plain_rel_pathlist(root, rel, rte);
4316+
4317+ /*
4318+ * create_plain_partial_paths creates partial paths with reasonably
4319+ * estimated number of workers. Force the requested number of workers if
4320+ * hard mode.
4321+ */
4322+ phint = find_parallel_hint(root, rel->relid, rel);
4323+
4324+ if (phint)
44664325 {
4467- foreach (l, rel->partial_pathlist)
4326+ /* if inhibiting parallel, remove existing partial paths */
4327+ if (phint->nworkers == 0 && rel->partial_pathlist)
44684328 {
4469- Path *ppath = (Path *) lfirst(l);
4470-
4471- if (phint && phint->nworkers > 0 && phint->force_parallel)
4329+ list_free_deep(rel->partial_pathlist);
4330+ rel->partial_pathlist = NIL;
4331+ }
4332+
4333+ /* enforce number of workers if requested */
4334+ if (rel->partial_pathlist && phint->force_parallel)
4335+ {
4336+ foreach (l, rel->partial_pathlist)
44724337 {
4473- Assert(ppath->parallel_workers > 0);
4338+ Path *ppath = (Path *) lfirst(l);
4339+
44744340 ppath->parallel_workers = phint->nworkers;
4475- }
4341+ }
44764342 }
4477-
4478- generate_gather_paths(root, rel);
4343+
4344+ /* Generate gather paths for base rels */
4345+ if (rel->reloptkind == RELOPT_BASEREL)
4346+ generate_gather_paths(root, rel);
44794347 }
4348+
4349+ reset_hint_enforcement();
44804350 }
44814351
44824352 /*
@@ -4484,7 +4354,8 @@ pg_hint_plan_set_rel_pathlist(PlannerInfo * root, RelOptInfo *rel,
44844354 * Build access paths for a base relation
44854355 *
44864356 * This function was copied and edited from set_rel_pathlist() in
4487- * src/backend/optimizer/path/allpaths.c
4357+ * src/backend/optimizer/path/allpaths.c in order not to copy other static
4358+ * functions not required here.
44884359 */
44894360 static void
44904361 set_rel_pathlist(PlannerInfo *root, RelOptInfo *rel,
--- a/sql/ut-W.sql
+++ b/sql/ut-W.sql
@@ -3,36 +3,47 @@ ALTER SYSTEM SET session_preload_libraries TO 'pg_hint_plan';
33 SET pg_hint_plan.enable_hint TO on;
44 SET pg_hint_plan.debug_print TO on;
55 SET client_min_messages TO LOG;
6-SET search_path TO public;
7-SET max_parallel_workers_per_gather TO 0;
8-SET enable_indexscan to false;
9-SET enable_bitmapscan to false;
10-SET parallel_setup_cost to 0;
11-SET parallel_tuple_cost to 0;
12-SET min_parallel_relation_size to 0;
13-SET max_parallel_workers_per_gather to 0;
6+
147
158 CREATE TABLE s1.tl (a int);
169 INSERT INTO s1.tl (SELECT a FROM generate_series(0, 100000) a);
1710
11+-- Queries on ordinary tables
1812 EXPLAIN (COSTS false) SELECT * FROM s1.t1;
1913
14+SET parallel_setup_cost to 0;
15+SET parallel_tuple_cost to 0;
16+SET min_parallel_relation_size to 0;
2017 /*+Parallel(t1 10)*/
2118 EXPLAIN (COSTS false) SELECT * FROM s1.t1;
2219
2320 /*+Parallel(t1 10 soft)*/
2421 EXPLAIN (COSTS false) SELECT * FROM s1.t1;
22+SET parallel_setup_cost to DEFAULT;
23+SET parallel_tuple_cost to DEFAULT;
24+SET min_parallel_relation_size to DEFAULT;
2525
2626 /*+Parallel(t1 10 hard)*/
2727 EXPLAIN (COSTS false) SELECT * FROM s1.t1;
2828
29--- Inheritnce tables
30-/*+Parallel(p1 10 soft)*/
29+-- Queries on inheritance tables
30+SET parallel_setup_cost to 0;
31+SET parallel_tuple_cost to 0;
32+SET min_parallel_relation_size to 0;
33+/*+Parallel(p1 10)*/
3134 EXPLAIN (COSTS false) SELECT * FROM p1;
35+SET parallel_setup_cost to DEFAULT;
36+SET parallel_tuple_cost to DEFAULT;
37+SET min_parallel_relation_size to DEFAULT;
3238
3339 /*+Parallel(p1 10 hard)*/
3440 EXPLAIN (COSTS false) SELECT * FROM p1;
3541
42+-- hinting on children don't work
43+/*+Parallel(p1_c1 10 hard)*/
44+EXPLAIN (COSTS false) SELECT * FROM p1;
45+
46+
3647 -- Joins
3748 EXPLAIN (COSTS false) SELECT * FROM p1_c1 join p2_c1 on p1_c1.id = p2_c1.id;
3849
@@ -45,9 +56,16 @@ EXPLAIN (COSTS false) SELECT * FROM p1_c1 join p2_c1 on p1_c1.id = p2_c1.id;
4556 /*+Parallel(p1_c1 10 hard) Parallel(p2_c1 10 hard)*/
4657 EXPLAIN (COSTS false) SELECT * FROM p1_c1 join p2_c1 on p1_c1.id = p2_c1.id;
4758
59+
4860 -- Joins on inheritance tables
61+SET parallel_setup_cost to 0;
62+SET parallel_tuple_cost to 0;
63+SET min_parallel_relation_size to 0;
4964 /*+Parallel(p1 10)*/
5065 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
66+SET parallel_setup_cost to DEFAULT;
67+SET parallel_tuple_cost to DEFAULT;
68+SET min_parallel_relation_size to DEFAULT;
5169
5270 /*+Parallel(p2 10 hard)*/
5371 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
@@ -55,8 +73,47 @@ EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
5573 /*+Parallel(p2 10 hard) Parallel(p1 5 hard) */
5674 EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
5775
76+
77+-- Mixture with a scan hint
78+-- p1 can be parallel
79+/*+Parallel(p1 10 hard) IndexScan(p2) */
80+EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
81+
82+-- seqscan doesn't harm parallelism
83+/*+Parallel(p1 10 hard) SeqScan(p1) */
84+EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
85+
86+-- parallelism is not available for the case
87+/*+Parallel(p1 10 hard) IndexScan(p1) */
88+EXPLAIN (COSTS false) SELECT * FROM p1 join p2 on p1.id = p2.id;
89+
90+
91+-- Parallel on UNION
92+EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
93+
94+-- some of the scans are not parallel, so this cannot be parallel
95+SET parallel_setup_cost to 0;
96+SET parallel_tuple_cost to 0;
97+SET min_parallel_relation_size to 0;
98+/*+Parallel(p1 10) */
99+EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
100+
101+-- all children are parallel, so this can be parallel
102+/*+Parallel(p1 10) Parallel(p2 10) */
103+EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
104+SET parallel_setup_cost to DEFAULT;
105+SET parallel_tuple_cost to DEFAULT;
106+SET min_parallel_relation_size to DEFAULT;
107+
108+/*+Parallel(p1 10 hard)Parallel(p2 10 hard) */
109+EXPLAIN (COSTS false) SELECT id FROM p1 UNION ALL SELECT id FROM p2;
110+
111+
58112 -- Negative hint
59113 SET max_parallel_workers_per_gather to 5;
114+SET parallel_setup_cost to 0;
115+SET parallel_tuple_cost to 0;
116+SET min_parallel_relation_size to 0;
60117 EXPLAIN (COSTS false) SELECT * FROM p1;
61118
62119 /*+Parallel(p1 0 hard)*/
Show on old repository browser