• R/O
  • HTTP
  • SSH
  • HTTPS

pg_hint_plan: Commit

firtst release


Commit MetaInfo

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