OSDN -- Develop and Download Open Source Software
  • R/O
  • HTTP
  • SSH
  • HTTPS
Fork

pg_hint_plan: Commit

firtst release


Commit MetaInfo

Revisionb32d680a8b6bca9dcaadfbff01f26a2ecce0a48d (tree)
Time2018-12-05 17:32:46
AuthorKyotaro Horiguchi <horiguchi.kyotaro@lab....>
CommiterKyotaro Horiguchi

Log Message

Fix for union-on-inheritance case

setup_hint_enforcement may pick up a wrong hint when multiple
subqueries access inheritance tables. This leads to failure to apply
hints on other than the first subquery.

Change Summary

Incremental Difference

--- a/expected/pg_hint_plan.out
+++ b/expected/pg_hint_plan.out
@@ -8343,6 +8343,384 @@ SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE schemaname = '
83438343 p1_c2 | 0 | 2
83448344 (2 rows)
83458345
8346+-- Subqueries on inheritance tables under UNION
8347+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
8348+UNION ALL
8349+SELECT val::int FROM p2 WHERE id < 1000;
8350+ QUERY PLAN
8351+-----------------------------------------
8352+ Append
8353+ -> Append
8354+ -> Seq Scan on p1
8355+ Filter: (val < 1000)
8356+ -> Seq Scan on p1_c1
8357+ Filter: (val < 1000)
8358+ -> Seq Scan on p1_c2
8359+ Filter: (val < 1000)
8360+ -> Seq Scan on p1_c3
8361+ Filter: (val < 1000)
8362+ -> Seq Scan on p1_c4
8363+ Filter: (val < 1000)
8364+ -> Seq Scan on p1_c1_c1
8365+ Filter: (val < 1000)
8366+ -> Seq Scan on p1_c1_c2
8367+ Filter: (val < 1000)
8368+ -> Seq Scan on p1_c3_c1
8369+ Filter: (val < 1000)
8370+ -> Seq Scan on p1_c3_c2
8371+ Filter: (val < 1000)
8372+ -> Result
8373+ -> Append
8374+ -> Seq Scan on p2
8375+ Filter: (id < 1000)
8376+ -> Seq Scan on p2_c1
8377+ Filter: (id < 1000)
8378+ -> Seq Scan on p2_c2
8379+ Filter: (id < 1000)
8380+ -> Seq Scan on p2_c3
8381+ Filter: (id < 1000)
8382+ -> Seq Scan on p2_c4
8383+ Filter: (id < 1000)
8384+ -> Seq Scan on p2_c1_c1
8385+ Filter: (id < 1000)
8386+ -> Seq Scan on p2_c1_c2
8387+ Filter: (id < 1000)
8388+ -> Seq Scan on p2_c3_c1
8389+ Filter: (id < 1000)
8390+ -> Seq Scan on p2_c3_c2
8391+ Filter: (id < 1000)
8392+(40 rows)
8393+
8394+/*+ IndexScan(p1 p1_val2) */
8395+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
8396+UNION ALL
8397+SELECT val::int FROM p2 WHERE id < 1000;
8398+LOG: available indexes for IndexScan(p1): p1_val3 p1_val2 p1_val1
8399+LOG: available indexes for IndexScan(p1_c1): p1_c1_val3 p1_c1_val2 p1_c1_val1
8400+LOG: available indexes for IndexScan(p1_c2): p1_c2_val3 p1_c2_val2 p1_c2_val1
8401+LOG: available indexes for IndexScan(p1_c3): p1_c3_val3 p1_c3_val2 p1_c3_val1
8402+LOG: available indexes for IndexScan(p1_c4): p1_c4_val3 p1_c4_val2 p1_c4_val1
8403+LOG: available indexes for IndexScan(p1_c1_c1): p1_c1_c1_val3 p1_c1_c1_val2 p1_c1_c1_val1
8404+LOG: available indexes for IndexScan(p1_c1_c2): p1_c1_c2_val3 p1_c1_c2_val2 p1_c1_c2_val1
8405+LOG: available indexes for IndexScan(p1_c3_c1): p1_c3_c1_val3 p1_c3_c1_val2 p1_c3_c1_val1
8406+LOG: available indexes for IndexScan(p1_c3_c2): p1_c3_c2_val3 p1_c3_c2_val2 p1_c3_c2_val1
8407+LOG: pg_hint_plan:
8408+used hint:
8409+IndexScan(p1 p1_val2)
8410+not used hint:
8411+duplication hint:
8412+error hint:
8413+
8414+ QUERY PLAN
8415+--------------------------------------------------------
8416+ Append
8417+ -> Append
8418+ -> Index Scan using p1_val3 on p1
8419+ Index Cond: (val < 1000)
8420+ -> Index Scan using p1_c1_val3 on p1_c1
8421+ Index Cond: (val < 1000)
8422+ -> Index Scan using p1_c2_val3 on p1_c2
8423+ Index Cond: (val < 1000)
8424+ -> Index Scan using p1_c3_val3 on p1_c3
8425+ Index Cond: (val < 1000)
8426+ -> Index Scan using p1_c4_val3 on p1_c4
8427+ Index Cond: (val < 1000)
8428+ -> Index Scan using p1_c1_c1_val3 on p1_c1_c1
8429+ Index Cond: (val < 1000)
8430+ -> Index Scan using p1_c1_c2_val3 on p1_c1_c2
8431+ Index Cond: (val < 1000)
8432+ -> Index Scan using p1_c3_c1_val3 on p1_c3_c1
8433+ Index Cond: (val < 1000)
8434+ -> Index Scan using p1_c3_c2_val3 on p1_c3_c2
8435+ Index Cond: (val < 1000)
8436+ -> Result
8437+ -> Append
8438+ -> Seq Scan on p2
8439+ Filter: (id < 1000)
8440+ -> Seq Scan on p2_c1
8441+ Filter: (id < 1000)
8442+ -> Seq Scan on p2_c2
8443+ Filter: (id < 1000)
8444+ -> Seq Scan on p2_c3
8445+ Filter: (id < 1000)
8446+ -> Seq Scan on p2_c4
8447+ Filter: (id < 1000)
8448+ -> Seq Scan on p2_c1_c1
8449+ Filter: (id < 1000)
8450+ -> Seq Scan on p2_c1_c2
8451+ Filter: (id < 1000)
8452+ -> Seq Scan on p2_c3_c1
8453+ Filter: (id < 1000)
8454+ -> Seq Scan on p2_c3_c2
8455+ Filter: (id < 1000)
8456+(40 rows)
8457+
8458+/*+ IndexScan(p1 p1_val2) IndexScan(p2 p2_id_val_idx) */
8459+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
8460+UNION ALL
8461+SELECT val::int FROM p2 WHERE id < 1000;
8462+LOG: available indexes for IndexScan(p1): p1_val3 p1_val2 p1_val1
8463+LOG: available indexes for IndexScan(p1_c1): p1_c1_val3 p1_c1_val2 p1_c1_val1
8464+LOG: available indexes for IndexScan(p1_c2): p1_c2_val3 p1_c2_val2 p1_c2_val1
8465+LOG: available indexes for IndexScan(p1_c3): p1_c3_val3 p1_c3_val2 p1_c3_val1
8466+LOG: available indexes for IndexScan(p1_c4): p1_c4_val3 p1_c4_val2 p1_c4_val1
8467+LOG: available indexes for IndexScan(p1_c1_c1): p1_c1_c1_val3 p1_c1_c1_val2 p1_c1_c1_val1
8468+LOG: available indexes for IndexScan(p1_c1_c2): p1_c1_c2_val3 p1_c1_c2_val2 p1_c1_c2_val1
8469+LOG: available indexes for IndexScan(p1_c3_c1): p1_c3_c1_val3 p1_c3_c1_val2 p1_c3_c1_val1
8470+LOG: available indexes for IndexScan(p1_c3_c2): p1_c3_c2_val3 p1_c3_c2_val2 p1_c3_c2_val1
8471+LOG: available indexes for IndexScan(p2): p2_id_val_idx
8472+LOG: available indexes for IndexScan(p2_c1): p2_c1_id_val_idx
8473+LOG: available indexes for IndexScan(p2_c2): p2_c2_id_val_idx
8474+LOG: available indexes for IndexScan(p2_c3): p2_c3_id_val_idx
8475+LOG: available indexes for IndexScan(p2_c4): p2_c4_id_val_idx
8476+LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_id_val_idx
8477+LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_id_val_idx
8478+LOG: available indexes for IndexScan(p2_c3_c1): p2_c3_c1_id_val_idx
8479+LOG: available indexes for IndexScan(p2_c3_c2): p2_c3_c2_id_val_idx
8480+LOG: pg_hint_plan:
8481+used hint:
8482+IndexScan(p1 p1_val2)
8483+IndexScan(p2 p2_id_val_idx)
8484+not used hint:
8485+duplication hint:
8486+error hint:
8487+
8488+ QUERY PLAN
8489+--------------------------------------------------------------------
8490+ Append
8491+ -> Append
8492+ -> Index Scan using p1_val3 on p1
8493+ Index Cond: (val < 1000)
8494+ -> Index Scan using p1_c1_val3 on p1_c1
8495+ Index Cond: (val < 1000)
8496+ -> Index Scan using p1_c2_val3 on p1_c2
8497+ Index Cond: (val < 1000)
8498+ -> Index Scan using p1_c3_val3 on p1_c3
8499+ Index Cond: (val < 1000)
8500+ -> Index Scan using p1_c4_val3 on p1_c4
8501+ Index Cond: (val < 1000)
8502+ -> Index Scan using p1_c1_c1_val3 on p1_c1_c1
8503+ Index Cond: (val < 1000)
8504+ -> Index Scan using p1_c1_c2_val3 on p1_c1_c2
8505+ Index Cond: (val < 1000)
8506+ -> Index Scan using p1_c3_c1_val3 on p1_c3_c1
8507+ Index Cond: (val < 1000)
8508+ -> Index Scan using p1_c3_c2_val3 on p1_c3_c2
8509+ Index Cond: (val < 1000)
8510+ -> Result
8511+ -> Append
8512+ -> Index Scan using p2_id_val_idx on p2
8513+ Index Cond: (id < 1000)
8514+ -> Index Scan using p2_c1_id_val_idx on p2_c1
8515+ Index Cond: (id < 1000)
8516+ -> Index Scan using p2_c2_id_val_idx on p2_c2
8517+ Index Cond: (id < 1000)
8518+ -> Index Scan using p2_c3_id_val_idx on p2_c3
8519+ Index Cond: (id < 1000)
8520+ -> Index Scan using p2_c4_id_val_idx on p2_c4
8521+ Index Cond: (id < 1000)
8522+ -> Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1
8523+ Index Cond: (id < 1000)
8524+ -> Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2
8525+ Index Cond: (id < 1000)
8526+ -> Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1
8527+ Index Cond: (id < 1000)
8528+ -> Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2
8529+ Index Cond: (id < 1000)
8530+(40 rows)
8531+
8532+-- union all case
8533+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
8534+UNION
8535+SELECT val::int FROM p2 WHERE id < 1000;
8536+ QUERY PLAN
8537+-----------------------------------------------
8538+ HashAggregate
8539+ Group Key: p1.val
8540+ -> Append
8541+ -> Append
8542+ -> Seq Scan on p1
8543+ Filter: (val < 1000)
8544+ -> Seq Scan on p1_c1
8545+ Filter: (val < 1000)
8546+ -> Seq Scan on p1_c2
8547+ Filter: (val < 1000)
8548+ -> Seq Scan on p1_c3
8549+ Filter: (val < 1000)
8550+ -> Seq Scan on p1_c4
8551+ Filter: (val < 1000)
8552+ -> Seq Scan on p1_c1_c1
8553+ Filter: (val < 1000)
8554+ -> Seq Scan on p1_c1_c2
8555+ Filter: (val < 1000)
8556+ -> Seq Scan on p1_c3_c1
8557+ Filter: (val < 1000)
8558+ -> Seq Scan on p1_c3_c2
8559+ Filter: (val < 1000)
8560+ -> Result
8561+ -> Append
8562+ -> Seq Scan on p2
8563+ Filter: (id < 1000)
8564+ -> Seq Scan on p2_c1
8565+ Filter: (id < 1000)
8566+ -> Seq Scan on p2_c2
8567+ Filter: (id < 1000)
8568+ -> Seq Scan on p2_c3
8569+ Filter: (id < 1000)
8570+ -> Seq Scan on p2_c4
8571+ Filter: (id < 1000)
8572+ -> Seq Scan on p2_c1_c1
8573+ Filter: (id < 1000)
8574+ -> Seq Scan on p2_c1_c2
8575+ Filter: (id < 1000)
8576+ -> Seq Scan on p2_c3_c1
8577+ Filter: (id < 1000)
8578+ -> Seq Scan on p2_c3_c2
8579+ Filter: (id < 1000)
8580+(42 rows)
8581+
8582+/*+ IndexScan(p2 p2_id_val_idx) */
8583+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
8584+UNION
8585+SELECT val::int FROM p2 WHERE id < 1000;
8586+LOG: available indexes for IndexScan(p2): p2_id_val_idx
8587+LOG: available indexes for IndexScan(p2_c1): p2_c1_id_val_idx
8588+LOG: available indexes for IndexScan(p2_c2): p2_c2_id_val_idx
8589+LOG: available indexes for IndexScan(p2_c3): p2_c3_id_val_idx
8590+LOG: available indexes for IndexScan(p2_c4): p2_c4_id_val_idx
8591+LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_id_val_idx
8592+LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_id_val_idx
8593+LOG: available indexes for IndexScan(p2_c3_c1): p2_c3_c1_id_val_idx
8594+LOG: available indexes for IndexScan(p2_c3_c2): p2_c3_c2_id_val_idx
8595+LOG: pg_hint_plan:
8596+used hint:
8597+IndexScan(p2 p2_id_val_idx)
8598+not used hint:
8599+duplication hint:
8600+error hint:
8601+
8602+ QUERY PLAN
8603+--------------------------------------------------------------------------
8604+ HashAggregate
8605+ Group Key: p1.val
8606+ -> Append
8607+ -> Append
8608+ -> Seq Scan on p1
8609+ Filter: (val < 1000)
8610+ -> Seq Scan on p1_c1
8611+ Filter: (val < 1000)
8612+ -> Seq Scan on p1_c2
8613+ Filter: (val < 1000)
8614+ -> Seq Scan on p1_c3
8615+ Filter: (val < 1000)
8616+ -> Seq Scan on p1_c4
8617+ Filter: (val < 1000)
8618+ -> Seq Scan on p1_c1_c1
8619+ Filter: (val < 1000)
8620+ -> Seq Scan on p1_c1_c2
8621+ Filter: (val < 1000)
8622+ -> Seq Scan on p1_c3_c1
8623+ Filter: (val < 1000)
8624+ -> Seq Scan on p1_c3_c2
8625+ Filter: (val < 1000)
8626+ -> Result
8627+ -> Append
8628+ -> Index Scan using p2_id_val_idx on p2
8629+ Index Cond: (id < 1000)
8630+ -> Index Scan using p2_c1_id_val_idx on p2_c1
8631+ Index Cond: (id < 1000)
8632+ -> Index Scan using p2_c2_id_val_idx on p2_c2
8633+ Index Cond: (id < 1000)
8634+ -> Index Scan using p2_c3_id_val_idx on p2_c3
8635+ Index Cond: (id < 1000)
8636+ -> Index Scan using p2_c4_id_val_idx on p2_c4
8637+ Index Cond: (id < 1000)
8638+ -> Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1
8639+ Index Cond: (id < 1000)
8640+ -> Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2
8641+ Index Cond: (id < 1000)
8642+ -> Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1
8643+ Index Cond: (id < 1000)
8644+ -> Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2
8645+ Index Cond: (id < 1000)
8646+(42 rows)
8647+
8648+/*+ IndexScan(p1 p1_val2) IndexScan(p2 p2_id_val_idx) */
8649+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
8650+UNION
8651+SELECT val::int FROM p2 WHERE id < 1000;
8652+LOG: available indexes for IndexScan(p2): p2_id_val_idx
8653+LOG: available indexes for IndexScan(p2_c1): p2_c1_id_val_idx
8654+LOG: available indexes for IndexScan(p2_c2): p2_c2_id_val_idx
8655+LOG: available indexes for IndexScan(p2_c3): p2_c3_id_val_idx
8656+LOG: available indexes for IndexScan(p2_c4): p2_c4_id_val_idx
8657+LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_id_val_idx
8658+LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_id_val_idx
8659+LOG: available indexes for IndexScan(p2_c3_c1): p2_c3_c1_id_val_idx
8660+LOG: available indexes for IndexScan(p2_c3_c2): p2_c3_c2_id_val_idx
8661+LOG: available indexes for IndexScan(p1): p1_val3 p1_val2 p1_val1
8662+LOG: available indexes for IndexScan(p1_c1): p1_c1_val3 p1_c1_val2 p1_c1_val1
8663+LOG: available indexes for IndexScan(p1_c2): p1_c2_val3 p1_c2_val2 p1_c2_val1
8664+LOG: available indexes for IndexScan(p1_c3): p1_c3_val3 p1_c3_val2 p1_c3_val1
8665+LOG: available indexes for IndexScan(p1_c4): p1_c4_val3 p1_c4_val2 p1_c4_val1
8666+LOG: available indexes for IndexScan(p1_c1_c1): p1_c1_c1_val3 p1_c1_c1_val2 p1_c1_c1_val1
8667+LOG: available indexes for IndexScan(p1_c1_c2): p1_c1_c2_val3 p1_c1_c2_val2 p1_c1_c2_val1
8668+LOG: available indexes for IndexScan(p1_c3_c1): p1_c3_c1_val3 p1_c3_c1_val2 p1_c3_c1_val1
8669+LOG: available indexes for IndexScan(p1_c3_c2): p1_c3_c2_val3 p1_c3_c2_val2 p1_c3_c2_val1
8670+LOG: pg_hint_plan:
8671+used hint:
8672+IndexScan(p1 p1_val2)
8673+IndexScan(p2 p2_id_val_idx)
8674+not used hint:
8675+duplication hint:
8676+error hint:
8677+
8678+ QUERY PLAN
8679+--------------------------------------------------------------------------
8680+ HashAggregate
8681+ Group Key: p1.val
8682+ -> Append
8683+ -> Append
8684+ -> Index Scan using p1_val3 on p1
8685+ Index Cond: (val < 1000)
8686+ -> Index Scan using p1_c1_val3 on p1_c1
8687+ Index Cond: (val < 1000)
8688+ -> Index Scan using p1_c2_val3 on p1_c2
8689+ Index Cond: (val < 1000)
8690+ -> Index Scan using p1_c3_val3 on p1_c3
8691+ Index Cond: (val < 1000)
8692+ -> Index Scan using p1_c4_val3 on p1_c4
8693+ Index Cond: (val < 1000)
8694+ -> Index Scan using p1_c1_c1_val3 on p1_c1_c1
8695+ Index Cond: (val < 1000)
8696+ -> Index Scan using p1_c1_c2_val3 on p1_c1_c2
8697+ Index Cond: (val < 1000)
8698+ -> Index Scan using p1_c3_c1_val3 on p1_c3_c1
8699+ Index Cond: (val < 1000)
8700+ -> Index Scan using p1_c3_c2_val3 on p1_c3_c2
8701+ Index Cond: (val < 1000)
8702+ -> Result
8703+ -> Append
8704+ -> Index Scan using p2_id_val_idx on p2
8705+ Index Cond: (id < 1000)
8706+ -> Index Scan using p2_c1_id_val_idx on p2_c1
8707+ Index Cond: (id < 1000)
8708+ -> Index Scan using p2_c2_id_val_idx on p2_c2
8709+ Index Cond: (id < 1000)
8710+ -> Index Scan using p2_c3_id_val_idx on p2_c3
8711+ Index Cond: (id < 1000)
8712+ -> Index Scan using p2_c4_id_val_idx on p2_c4
8713+ Index Cond: (id < 1000)
8714+ -> Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1
8715+ Index Cond: (id < 1000)
8716+ -> Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2
8717+ Index Cond: (id < 1000)
8718+ -> Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1
8719+ Index Cond: (id < 1000)
8720+ -> Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2
8721+ Index Cond: (id < 1000)
8722+(42 rows)
8723+
83468724 --
83478725 -- Rows hint tests
83488726 --
--- a/pg_hint_plan.c
+++ b/pg_hint_plan.c
@@ -307,6 +307,7 @@ struct HintState
307307 /* for scan method hints */
308308 ScanMethodHint **scan_hints; /* parsed scan hints */
309309 int init_scan_mask; /* initial value scan parameter */
310+ PlannerInfo *current_root; /* PlannerInfo for the followings */
310311 Index parent_relid; /* inherit parent table relid */
311312 ScanMethodHint *parent_hint; /* inherit parent table scan hint */
312313 List *parent_index_infos; /* information of inherit parent table's
@@ -858,6 +859,7 @@ HintStateCreate(void)
858859 memset(hstate->num_hints, 0, sizeof(hstate->num_hints));
859860 hstate->scan_hints = NULL;
860861 hstate->init_scan_mask = 0;
862+ hstate->current_root = NULL;
861863 hstate->parent_relid = 0;
862864 hstate->parent_hint = NULL;
863865 hstate->parent_index_infos = NIL;
@@ -3201,7 +3203,11 @@ pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId,
32013203 return;
32023204 }
32033205
3204- /* Find the parent for this relation */
3206+ /* Forget about the parent of another subquery */
3207+ if (root != current_hint->current_root)
3208+ current_hint->parent_relid = 0;
3209+
3210+ /* Find the parent for this relation other than the registered parent */
32053211 foreach (l, root->append_rel_list)
32063212 {
32073213 AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(l);
@@ -3209,7 +3215,10 @@ pg_hint_plan_get_relation_info(PlannerInfo *root, Oid relationObjectId,
32093215 if (appinfo->child_relid == rel->relid)
32103216 {
32113217 if (current_hint->parent_relid != appinfo->parent_relid)
3218+ {
32123219 new_parent_relid = appinfo->parent_relid;
3220+ current_hint->current_root = root;
3221+ }
32133222 break;
32143223 }
32153224 }
--- a/sql/pg_hint_plan.sql
+++ b/sql/pg_hint_plan.sql
@@ -1003,6 +1003,36 @@ SELECT pg_sleep(1);
10031003 -- the index scan happened while planning.
10041004 SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE schemaname = 'public' AND (relname = 'p1_c1' OR relname = 'p1_c2');
10051005
1006+-- Subqueries on inheritance tables under UNION
1007+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
1008+UNION ALL
1009+SELECT val::int FROM p2 WHERE id < 1000;
1010+
1011+/*+ IndexScan(p1 p1_val2) */
1012+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
1013+UNION ALL
1014+SELECT val::int FROM p2 WHERE id < 1000;
1015+
1016+/*+ IndexScan(p1 p1_val2) IndexScan(p2 p2_id_val_idx) */
1017+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
1018+UNION ALL
1019+SELECT val::int FROM p2 WHERE id < 1000;
1020+
1021+-- union all case
1022+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
1023+UNION
1024+SELECT val::int FROM p2 WHERE id < 1000;
1025+
1026+/*+ IndexScan(p2 p2_id_val_idx) */
1027+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
1028+UNION
1029+SELECT val::int FROM p2 WHERE id < 1000;
1030+
1031+/*+ IndexScan(p1 p1_val2) IndexScan(p2 p2_id_val_idx) */
1032+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
1033+UNION
1034+SELECT val::int FROM p2 WHERE id < 1000;
1035+
10061036 --
10071037 -- Rows hint tests
10081038 --
Show on old repository browser