• R/O
  • HTTP
  • SSH
  • HTTPS

pg_hint_plan: Commit

firtst release


Commit MetaInfo

Revision0eda42ba5ec6e360acab1458ca72345b8c8923a8 (tree)
Time2018-12-05 17:31:53
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
@@ -8357,6 +8357,384 @@ SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE schemaname = '
83578357 p1_c2 | 0 | 2
83588358 (2 rows)
83598359
8360+-- Subqueries on inheritance tables under UNION
8361+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
8362+UNION ALL
8363+SELECT val::int FROM p2 WHERE id < 1000;
8364+ QUERY PLAN
8365+-----------------------------------------
8366+ Append
8367+ -> Append
8368+ -> Seq Scan on p1
8369+ Filter: (val < 1000)
8370+ -> Seq Scan on p1_c1
8371+ Filter: (val < 1000)
8372+ -> Seq Scan on p1_c2
8373+ Filter: (val < 1000)
8374+ -> Seq Scan on p1_c3
8375+ Filter: (val < 1000)
8376+ -> Seq Scan on p1_c4
8377+ Filter: (val < 1000)
8378+ -> Seq Scan on p1_c1_c1
8379+ Filter: (val < 1000)
8380+ -> Seq Scan on p1_c1_c2
8381+ Filter: (val < 1000)
8382+ -> Seq Scan on p1_c3_c1
8383+ Filter: (val < 1000)
8384+ -> Seq Scan on p1_c3_c2
8385+ Filter: (val < 1000)
8386+ -> Result
8387+ -> Append
8388+ -> Seq Scan on p2
8389+ Filter: (id < 1000)
8390+ -> Seq Scan on p2_c1
8391+ Filter: (id < 1000)
8392+ -> Seq Scan on p2_c2
8393+ Filter: (id < 1000)
8394+ -> Seq Scan on p2_c3
8395+ Filter: (id < 1000)
8396+ -> Seq Scan on p2_c4
8397+ Filter: (id < 1000)
8398+ -> Seq Scan on p2_c1_c1
8399+ Filter: (id < 1000)
8400+ -> Seq Scan on p2_c1_c2
8401+ Filter: (id < 1000)
8402+ -> Seq Scan on p2_c3_c1
8403+ Filter: (id < 1000)
8404+ -> Seq Scan on p2_c3_c2
8405+ Filter: (id < 1000)
8406+(40 rows)
8407+
8408+/*+ IndexScan(p1 p1_val2) */
8409+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
8410+UNION ALL
8411+SELECT val::int FROM p2 WHERE id < 1000;
8412+LOG: available indexes for IndexScan(p1): p1_val3 p1_val2 p1_val1
8413+LOG: available indexes for IndexScan(p1_c1): p1_c1_val3 p1_c1_val2 p1_c1_val1
8414+LOG: available indexes for IndexScan(p1_c2): p1_c2_val3 p1_c2_val2 p1_c2_val1
8415+LOG: available indexes for IndexScan(p1_c3): p1_c3_val3 p1_c3_val2 p1_c3_val1
8416+LOG: available indexes for IndexScan(p1_c4): p1_c4_val3 p1_c4_val2 p1_c4_val1
8417+LOG: available indexes for IndexScan(p1_c1_c1): p1_c1_c1_val3 p1_c1_c1_val2 p1_c1_c1_val1
8418+LOG: available indexes for IndexScan(p1_c1_c2): p1_c1_c2_val3 p1_c1_c2_val2 p1_c1_c2_val1
8419+LOG: available indexes for IndexScan(p1_c3_c1): p1_c3_c1_val3 p1_c3_c1_val2 p1_c3_c1_val1
8420+LOG: available indexes for IndexScan(p1_c3_c2): p1_c3_c2_val3 p1_c3_c2_val2 p1_c3_c2_val1
8421+LOG: pg_hint_plan:
8422+used hint:
8423+IndexScan(p1 p1_val2)
8424+not used hint:
8425+duplication hint:
8426+error hint:
8427+
8428+ QUERY PLAN
8429+--------------------------------------------------------
8430+ Append
8431+ -> Append
8432+ -> Index Scan using p1_val3 on p1
8433+ Index Cond: (val < 1000)
8434+ -> Index Scan using p1_c1_val3 on p1_c1
8435+ Index Cond: (val < 1000)
8436+ -> Index Scan using p1_c2_val3 on p1_c2
8437+ Index Cond: (val < 1000)
8438+ -> Index Scan using p1_c3_val3 on p1_c3
8439+ Index Cond: (val < 1000)
8440+ -> Index Scan using p1_c4_val3 on p1_c4
8441+ Index Cond: (val < 1000)
8442+ -> Index Scan using p1_c1_c1_val3 on p1_c1_c1
8443+ Index Cond: (val < 1000)
8444+ -> Index Scan using p1_c1_c2_val3 on p1_c1_c2
8445+ Index Cond: (val < 1000)
8446+ -> Index Scan using p1_c3_c1_val3 on p1_c3_c1
8447+ Index Cond: (val < 1000)
8448+ -> Index Scan using p1_c3_c2_val3 on p1_c3_c2
8449+ Index Cond: (val < 1000)
8450+ -> Result
8451+ -> Append
8452+ -> Seq Scan on p2
8453+ Filter: (id < 1000)
8454+ -> Seq Scan on p2_c1
8455+ Filter: (id < 1000)
8456+ -> Seq Scan on p2_c2
8457+ Filter: (id < 1000)
8458+ -> Seq Scan on p2_c3
8459+ Filter: (id < 1000)
8460+ -> Seq Scan on p2_c4
8461+ Filter: (id < 1000)
8462+ -> Seq Scan on p2_c1_c1
8463+ Filter: (id < 1000)
8464+ -> Seq Scan on p2_c1_c2
8465+ Filter: (id < 1000)
8466+ -> Seq Scan on p2_c3_c1
8467+ Filter: (id < 1000)
8468+ -> Seq Scan on p2_c3_c2
8469+ Filter: (id < 1000)
8470+(40 rows)
8471+
8472+/*+ IndexScan(p1 p1_val2) IndexScan(p2 p2_id_val_idx) */
8473+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
8474+UNION ALL
8475+SELECT val::int FROM p2 WHERE id < 1000;
8476+LOG: available indexes for IndexScan(p1): p1_val3 p1_val2 p1_val1
8477+LOG: available indexes for IndexScan(p1_c1): p1_c1_val3 p1_c1_val2 p1_c1_val1
8478+LOG: available indexes for IndexScan(p1_c2): p1_c2_val3 p1_c2_val2 p1_c2_val1
8479+LOG: available indexes for IndexScan(p1_c3): p1_c3_val3 p1_c3_val2 p1_c3_val1
8480+LOG: available indexes for IndexScan(p1_c4): p1_c4_val3 p1_c4_val2 p1_c4_val1
8481+LOG: available indexes for IndexScan(p1_c1_c1): p1_c1_c1_val3 p1_c1_c1_val2 p1_c1_c1_val1
8482+LOG: available indexes for IndexScan(p1_c1_c2): p1_c1_c2_val3 p1_c1_c2_val2 p1_c1_c2_val1
8483+LOG: available indexes for IndexScan(p1_c3_c1): p1_c3_c1_val3 p1_c3_c1_val2 p1_c3_c1_val1
8484+LOG: available indexes for IndexScan(p1_c3_c2): p1_c3_c2_val3 p1_c3_c2_val2 p1_c3_c2_val1
8485+LOG: available indexes for IndexScan(p2): p2_id_val_idx
8486+LOG: available indexes for IndexScan(p2_c1): p2_c1_id_val_idx
8487+LOG: available indexes for IndexScan(p2_c2): p2_c2_id_val_idx
8488+LOG: available indexes for IndexScan(p2_c3): p2_c3_id_val_idx
8489+LOG: available indexes for IndexScan(p2_c4): p2_c4_id_val_idx
8490+LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_id_val_idx
8491+LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_id_val_idx
8492+LOG: available indexes for IndexScan(p2_c3_c1): p2_c3_c1_id_val_idx
8493+LOG: available indexes for IndexScan(p2_c3_c2): p2_c3_c2_id_val_idx
8494+LOG: pg_hint_plan:
8495+used hint:
8496+IndexScan(p1 p1_val2)
8497+IndexScan(p2 p2_id_val_idx)
8498+not used hint:
8499+duplication hint:
8500+error hint:
8501+
8502+ QUERY PLAN
8503+--------------------------------------------------------------------
8504+ Append
8505+ -> Append
8506+ -> Index Scan using p1_val3 on p1
8507+ Index Cond: (val < 1000)
8508+ -> Index Scan using p1_c1_val3 on p1_c1
8509+ Index Cond: (val < 1000)
8510+ -> Index Scan using p1_c2_val3 on p1_c2
8511+ Index Cond: (val < 1000)
8512+ -> Index Scan using p1_c3_val3 on p1_c3
8513+ Index Cond: (val < 1000)
8514+ -> Index Scan using p1_c4_val3 on p1_c4
8515+ Index Cond: (val < 1000)
8516+ -> Index Scan using p1_c1_c1_val3 on p1_c1_c1
8517+ Index Cond: (val < 1000)
8518+ -> Index Scan using p1_c1_c2_val3 on p1_c1_c2
8519+ Index Cond: (val < 1000)
8520+ -> Index Scan using p1_c3_c1_val3 on p1_c3_c1
8521+ Index Cond: (val < 1000)
8522+ -> Index Scan using p1_c3_c2_val3 on p1_c3_c2
8523+ Index Cond: (val < 1000)
8524+ -> Result
8525+ -> Append
8526+ -> Index Scan using p2_id_val_idx on p2
8527+ Index Cond: (id < 1000)
8528+ -> Index Scan using p2_c1_id_val_idx on p2_c1
8529+ Index Cond: (id < 1000)
8530+ -> Index Scan using p2_c2_id_val_idx on p2_c2
8531+ Index Cond: (id < 1000)
8532+ -> Index Scan using p2_c3_id_val_idx on p2_c3
8533+ Index Cond: (id < 1000)
8534+ -> Index Scan using p2_c4_id_val_idx on p2_c4
8535+ Index Cond: (id < 1000)
8536+ -> Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1
8537+ Index Cond: (id < 1000)
8538+ -> Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2
8539+ Index Cond: (id < 1000)
8540+ -> Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1
8541+ Index Cond: (id < 1000)
8542+ -> Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2
8543+ Index Cond: (id < 1000)
8544+(40 rows)
8545+
8546+-- union all case
8547+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
8548+UNION
8549+SELECT val::int FROM p2 WHERE id < 1000;
8550+ QUERY PLAN
8551+-----------------------------------------------
8552+ HashAggregate
8553+ Group Key: p1.val
8554+ -> Append
8555+ -> Append
8556+ -> Seq Scan on p1
8557+ Filter: (val < 1000)
8558+ -> Seq Scan on p1_c1
8559+ Filter: (val < 1000)
8560+ -> Seq Scan on p1_c2
8561+ Filter: (val < 1000)
8562+ -> Seq Scan on p1_c3
8563+ Filter: (val < 1000)
8564+ -> Seq Scan on p1_c4
8565+ Filter: (val < 1000)
8566+ -> Seq Scan on p1_c1_c1
8567+ Filter: (val < 1000)
8568+ -> Seq Scan on p1_c1_c2
8569+ Filter: (val < 1000)
8570+ -> Seq Scan on p1_c3_c1
8571+ Filter: (val < 1000)
8572+ -> Seq Scan on p1_c3_c2
8573+ Filter: (val < 1000)
8574+ -> Result
8575+ -> Append
8576+ -> Seq Scan on p2
8577+ Filter: (id < 1000)
8578+ -> Seq Scan on p2_c1
8579+ Filter: (id < 1000)
8580+ -> Seq Scan on p2_c2
8581+ Filter: (id < 1000)
8582+ -> Seq Scan on p2_c3
8583+ Filter: (id < 1000)
8584+ -> Seq Scan on p2_c4
8585+ Filter: (id < 1000)
8586+ -> Seq Scan on p2_c1_c1
8587+ Filter: (id < 1000)
8588+ -> Seq Scan on p2_c1_c2
8589+ Filter: (id < 1000)
8590+ -> Seq Scan on p2_c3_c1
8591+ Filter: (id < 1000)
8592+ -> Seq Scan on p2_c3_c2
8593+ Filter: (id < 1000)
8594+(42 rows)
8595+
8596+/*+ IndexScan(p2 p2_id_val_idx) */
8597+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
8598+UNION
8599+SELECT val::int FROM p2 WHERE id < 1000;
8600+LOG: available indexes for IndexScan(p2): p2_id_val_idx
8601+LOG: available indexes for IndexScan(p2_c1): p2_c1_id_val_idx
8602+LOG: available indexes for IndexScan(p2_c2): p2_c2_id_val_idx
8603+LOG: available indexes for IndexScan(p2_c3): p2_c3_id_val_idx
8604+LOG: available indexes for IndexScan(p2_c4): p2_c4_id_val_idx
8605+LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_id_val_idx
8606+LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_id_val_idx
8607+LOG: available indexes for IndexScan(p2_c3_c1): p2_c3_c1_id_val_idx
8608+LOG: available indexes for IndexScan(p2_c3_c2): p2_c3_c2_id_val_idx
8609+LOG: pg_hint_plan:
8610+used hint:
8611+IndexScan(p2 p2_id_val_idx)
8612+not used hint:
8613+duplication hint:
8614+error hint:
8615+
8616+ QUERY PLAN
8617+--------------------------------------------------------------------------
8618+ HashAggregate
8619+ Group Key: p1.val
8620+ -> Append
8621+ -> Append
8622+ -> Seq Scan on p1
8623+ Filter: (val < 1000)
8624+ -> Seq Scan on p1_c1
8625+ Filter: (val < 1000)
8626+ -> Seq Scan on p1_c2
8627+ Filter: (val < 1000)
8628+ -> Seq Scan on p1_c3
8629+ Filter: (val < 1000)
8630+ -> Seq Scan on p1_c4
8631+ Filter: (val < 1000)
8632+ -> Seq Scan on p1_c1_c1
8633+ Filter: (val < 1000)
8634+ -> Seq Scan on p1_c1_c2
8635+ Filter: (val < 1000)
8636+ -> Seq Scan on p1_c3_c1
8637+ Filter: (val < 1000)
8638+ -> Seq Scan on p1_c3_c2
8639+ Filter: (val < 1000)
8640+ -> Result
8641+ -> Append
8642+ -> Index Scan using p2_id_val_idx on p2
8643+ Index Cond: (id < 1000)
8644+ -> Index Scan using p2_c1_id_val_idx on p2_c1
8645+ Index Cond: (id < 1000)
8646+ -> Index Scan using p2_c2_id_val_idx on p2_c2
8647+ Index Cond: (id < 1000)
8648+ -> Index Scan using p2_c3_id_val_idx on p2_c3
8649+ Index Cond: (id < 1000)
8650+ -> Index Scan using p2_c4_id_val_idx on p2_c4
8651+ Index Cond: (id < 1000)
8652+ -> Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1
8653+ Index Cond: (id < 1000)
8654+ -> Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2
8655+ Index Cond: (id < 1000)
8656+ -> Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1
8657+ Index Cond: (id < 1000)
8658+ -> Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2
8659+ Index Cond: (id < 1000)
8660+(42 rows)
8661+
8662+/*+ IndexScan(p1 p1_val2) IndexScan(p2 p2_id_val_idx) */
8663+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
8664+UNION
8665+SELECT val::int FROM p2 WHERE id < 1000;
8666+LOG: available indexes for IndexScan(p2): p2_id_val_idx
8667+LOG: available indexes for IndexScan(p2_c1): p2_c1_id_val_idx
8668+LOG: available indexes for IndexScan(p2_c2): p2_c2_id_val_idx
8669+LOG: available indexes for IndexScan(p2_c3): p2_c3_id_val_idx
8670+LOG: available indexes for IndexScan(p2_c4): p2_c4_id_val_idx
8671+LOG: available indexes for IndexScan(p2_c1_c1): p2_c1_c1_id_val_idx
8672+LOG: available indexes for IndexScan(p2_c1_c2): p2_c1_c2_id_val_idx
8673+LOG: available indexes for IndexScan(p2_c3_c1): p2_c3_c1_id_val_idx
8674+LOG: available indexes for IndexScan(p2_c3_c2): p2_c3_c2_id_val_idx
8675+LOG: available indexes for IndexScan(p1): p1_val3 p1_val2 p1_val1
8676+LOG: available indexes for IndexScan(p1_c1): p1_c1_val3 p1_c1_val2 p1_c1_val1
8677+LOG: available indexes for IndexScan(p1_c2): p1_c2_val3 p1_c2_val2 p1_c2_val1
8678+LOG: available indexes for IndexScan(p1_c3): p1_c3_val3 p1_c3_val2 p1_c3_val1
8679+LOG: available indexes for IndexScan(p1_c4): p1_c4_val3 p1_c4_val2 p1_c4_val1
8680+LOG: available indexes for IndexScan(p1_c1_c1): p1_c1_c1_val3 p1_c1_c1_val2 p1_c1_c1_val1
8681+LOG: available indexes for IndexScan(p1_c1_c2): p1_c1_c2_val3 p1_c1_c2_val2 p1_c1_c2_val1
8682+LOG: available indexes for IndexScan(p1_c3_c1): p1_c3_c1_val3 p1_c3_c1_val2 p1_c3_c1_val1
8683+LOG: available indexes for IndexScan(p1_c3_c2): p1_c3_c2_val3 p1_c3_c2_val2 p1_c3_c2_val1
8684+LOG: pg_hint_plan:
8685+used hint:
8686+IndexScan(p1 p1_val2)
8687+IndexScan(p2 p2_id_val_idx)
8688+not used hint:
8689+duplication hint:
8690+error hint:
8691+
8692+ QUERY PLAN
8693+--------------------------------------------------------------------------
8694+ HashAggregate
8695+ Group Key: p1.val
8696+ -> Append
8697+ -> Append
8698+ -> Index Scan using p1_val3 on p1
8699+ Index Cond: (val < 1000)
8700+ -> Index Scan using p1_c1_val3 on p1_c1
8701+ Index Cond: (val < 1000)
8702+ -> Index Scan using p1_c2_val3 on p1_c2
8703+ Index Cond: (val < 1000)
8704+ -> Index Scan using p1_c3_val3 on p1_c3
8705+ Index Cond: (val < 1000)
8706+ -> Index Scan using p1_c4_val3 on p1_c4
8707+ Index Cond: (val < 1000)
8708+ -> Index Scan using p1_c1_c1_val3 on p1_c1_c1
8709+ Index Cond: (val < 1000)
8710+ -> Index Scan using p1_c1_c2_val3 on p1_c1_c2
8711+ Index Cond: (val < 1000)
8712+ -> Index Scan using p1_c3_c1_val3 on p1_c3_c1
8713+ Index Cond: (val < 1000)
8714+ -> Index Scan using p1_c3_c2_val3 on p1_c3_c2
8715+ Index Cond: (val < 1000)
8716+ -> Result
8717+ -> Append
8718+ -> Index Scan using p2_id_val_idx on p2
8719+ Index Cond: (id < 1000)
8720+ -> Index Scan using p2_c1_id_val_idx on p2_c1
8721+ Index Cond: (id < 1000)
8722+ -> Index Scan using p2_c2_id_val_idx on p2_c2
8723+ Index Cond: (id < 1000)
8724+ -> Index Scan using p2_c3_id_val_idx on p2_c3
8725+ Index Cond: (id < 1000)
8726+ -> Index Scan using p2_c4_id_val_idx on p2_c4
8727+ Index Cond: (id < 1000)
8728+ -> Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1
8729+ Index Cond: (id < 1000)
8730+ -> Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2
8731+ Index Cond: (id < 1000)
8732+ -> Index Scan using p2_c3_c1_id_val_idx on p2_c3_c1
8733+ Index Cond: (id < 1000)
8734+ -> Index Scan using p2_c3_c2_id_val_idx on p2_c3_c2
8735+ Index Cond: (id < 1000)
8736+(42 rows)
8737+
83608738 --
83618739 -- Rows hint tests
83628740 --
--- a/pg_hint_plan.c
+++ b/pg_hint_plan.c
@@ -353,6 +353,7 @@ struct HintState
353353 int init_paratup_cost; /* parallel_tuple_cost */
354354 int init_parasetup_cost;/* parallel_setup_cost */
355355
356+ PlannerInfo *current_root; /* PlannerInfo for the followings */
356357 Index parent_relid; /* inherit parent of table relid */
357358 ScanMethodHint *parent_scan_hint; /* scan hint for the parent */
358359 ParallelHint *parent_parallel_hint; /* parallel hint for the parent */
@@ -973,6 +974,7 @@ HintStateCreate(void)
973974 hstate->init_min_para_indexscan_size = 0;
974975 hstate->init_paratup_cost = 0;
975976 hstate->init_parasetup_cost = 0;
977+ hstate->current_root = NULL;
976978 hstate->parent_relid = 0;
977979 hstate->parent_scan_hint = NULL;
978980 hstate->parent_parallel_hint = NULL;
@@ -3634,6 +3636,10 @@ setup_hint_enforcement(PlannerInfo *root, RelOptInfo *rel,
36343636 return 0;
36353637 }
36363638
3639+ /* Forget about the parent of another subquery */
3640+ if (root != current_hint_state->current_root)
3641+ current_hint_state->parent_relid = 0;
3642+
36373643 /* Find the parent for this relation other than the registered parent */
36383644 foreach (l, root->append_rel_list)
36393645 {
@@ -3642,7 +3648,10 @@ setup_hint_enforcement(PlannerInfo *root, RelOptInfo *rel,
36423648 if (appinfo->child_relid == rel->relid)
36433649 {
36443650 if (current_hint_state->parent_relid != appinfo->parent_relid)
3651+ {
36453652 new_parent_relid = appinfo->parent_relid;
3653+ current_hint_state->current_root = root;
3654+ }
36463655 break;
36473656 }
36483657 }
--- a/sql/pg_hint_plan.sql
+++ b/sql/pg_hint_plan.sql
@@ -1008,6 +1008,36 @@ SELECT pg_sleep(1);
10081008 -- the index scan happened while planning.
10091009 SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables WHERE schemaname = 'public' AND (relname = 'p1_c1' OR relname = 'p1_c2');
10101010
1011+-- Subqueries on inheritance tables under UNION
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) */
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+/*+ IndexScan(p1 p1_val2) IndexScan(p2 p2_id_val_idx) */
1022+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
1023+UNION ALL
1024+SELECT val::int FROM p2 WHERE id < 1000;
1025+
1026+-- union all case
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(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+
1036+/*+ IndexScan(p1 p1_val2) IndexScan(p2 p2_id_val_idx) */
1037+EXPLAIN (COSTS off) SELECT val FROM p1 WHERE val < 1000
1038+UNION
1039+SELECT val::int FROM p2 WHERE id < 1000;
1040+
10111041 --
10121042 -- Rows hint tests
10131043 --
Show on old repository browser