search

ossc-db / pg_hint_plan

Extension adding support for optimizer hints in PostgreSQL
Other
710 stars 103 forks source link

Hinting estimation with duplicate alias names doesn't seem to be supported (?) #153

Closed jakubwartakEDB closed 1 month ago

jakubwartakEDB commented 1 year ago

Hi, I'm trying to use pg_hint_plan to hint estimations using the Rows() hint in case of duplicate rel aliases? E.g. given the sample like this:

DROP TABLE t1, t2, t3;
CREATE TABLE t1 (id int PRIMARY KEY, val int);
CREATE TABLE t2 (id int PRIMARY KEY, val int);
CREATE TABLE t3 (id int PRIMARY KEY, val int);
INSERT INTO t1 SELECT i, i % 100 FROM (SELECT generate_series(1, 10000) i) t;
INSERT INTO t2 SELECT i, i % 10 FROM (SELECT generate_series(1, 1000) i) t;
INSERT INTO t3 SELECT i, i FROM (SELECT generate_series(1, 100) i) t;
​CREATE INDEX t1_val ON t1 (val);
CREATE INDEX t2_val ON t2 (val);
CREATE INDEX t3_id1 ON t3 (id);
CREATE INDEX t3_val ON t3 (val);
ANALYZE t1, t2, t3;

test=# /*+ Rows(t1 t2 t3 #1234) */ explain (analyze, verbose,buffers, timing, settings) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id and t2.id = t3.id;
LOG:  pg_hint_plan[qno=0x2]: planner: enable_hint=1, hint_inhibit_level=1
LOG:  pg_hint_plan[qno=0x2]: planner: no valid hint
LOG:  pg_hint_plan[qno=0x3]: no match found in table:  application name = "psql", normalized_query="/*+ Rows(t1 t2 t3 #1234) */ explain (analyze, verbose,buffers, timing, settings) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id and t2.id = t3.id;"
LOG:  hints in comment="Rows(t1 t2 t3 #1234) ", query="/*+ Rows(t1 t2 t3 #1234) */ explain (analyze, verbose,buffers, timing, settings) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id and t2.id = t3.id;", debug_query_string="/*+ Rows(t1 t2 t3 #1234) */ explain (analyze, verbose,buffers, timing, settings) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id and t2.id = t3.id;"
LOG:  pg_hint_plan[qno=0x2]: planner
LOG:  pg_hint_plan[qno=0x2]: setup_hint_enforcement no hint applied: relation=34869(t1), inhparent=0, current_hint=0x1c764f8, hint_inhibit_level=0, scanmask=0x1f
LOG:  pg_hint_plan[qno=0x2]: setup_hint_enforcement no hint applied: relation=34874(t2), inhparent=0, current_hint=0x1c764f8, hint_inhibit_level=0, scanmask=0x1f
LOG:  pg_hint_plan[qno=0x2]: setup_hint_enforcement no hint applied: relation=34879(t3), inhparent=0, current_hint=0x1c764f8, hint_inhibit_level=0, scanmask=0x1f
DEBUG:  RowsHint() trying joinrels '(b 1 2)' vs '#1234'
DEBUG:  RowsHint() trying joinrels '(b 1 3)' vs '#1234'
DEBUG:  RowsHint() trying joinrels '(b 2 3)' vs '#1234'
DEBUG:  RowsHint() trying joinrels '(b 1 2 3)' vs '#1234'
DEBUG:  adjusted rows 1 to 1234
DEBUG:  RowsHint() trying joinrels '(b 1 2 3)' vs '#1234'
DEBUG:  RowsHint() trying joinrels '(b 1 2 3)' vs '#1234'
LOG:  pg_hint_plan[qno=0x2]: HintStateDump: {used hints:Rows(t1 t2 t3 #1234)}, {not used hints:(none)}, {duplicate hints:(none)}, {error hints:(none)}
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.29..2.98 rows=1234 width=24) (actual time=0.006..0.007 rows=0 loops=1)

[..] -> got 1234 so it works (it was a sanity check).

Lets start introducing aliases:

/*+ Rows(t1 t2 t3 #1234) */ explain (analyze, verbose,buffers, timing, settings) SELECT * FROM (select * from t1 a) a, t2 b, t3 c WHERE a.id = b.id and b.id = c.id;
LOG:  pg_hint_plan[qno=0x3]: planner: enable_hint=1, hint_inhibit_level=1
LOG:  pg_hint_plan[qno=0x3]: planner: no valid hint
LOG:  pg_hint_plan[qno=0x4]: no match found in table:  application name = "psql", normalized_query="/*+ Rows(t1 t2 t3 #1234) */ explain (analyze, verbose,buffers, timing, settings) SELECT * FROM (select * from t1 a) a, t2 b, t3 c WHERE a.id = b.id and b.id = c.id;"
LOG:  hints in comment="Rows(t1 t2 t3 #1234) ", query="/*+ Rows(t1 t2 t3 #1234) */ explain (analyze, verbose,buffers, timing, settings) SELECT * FROM (select * from t1 a) a, t2 b, t3 c WHERE a.id = b.id and b.id = c.id;", debug_query_string="/*+ Rows(t1 t2 t3 #1234) */ explain (analyze, verbose,buffers, timing, settings) SELECT * FROM (select * from t1 a) a, t2 b, t3 c WHERE a.id = b.id and b.id = c.id;"
LOG:  pg_hint_plan[qno=0x3]: planner
LOG:  pg_hint_plan[qno=0x3]: setup_hint_enforcement no hint applied: relation=34874(t2), inhparent=0, current_hint=0x1c764f8, hint_inhibit_level=0, scanmask=0x1f
LOG:  pg_hint_plan[qno=0x3]: setup_hint_enforcement no hint applied: relation=34879(t3), inhparent=0, current_hint=0x1c764f8, hint_inhibit_level=0, scanmask=0x1f
LOG:  pg_hint_plan[qno=0x3]: setup_hint_enforcement no hint applied: relation=34869(t1), inhparent=0, current_hint=0x1c764f8, hint_inhibit_level=0, scanmask=0x1f
LOG:  pg_hint_plan[qno=0x3]: HintStateDump: {used hints:(none)}, {not used hints:Rows(t1 t2 t3 #1234)}, {duplicate hints:(none)}, {error hints:(none)}
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.29..2.98 rows=1 width=24) (actual time=0.006..0.008 rows=0 loops=1)

[..] -> ok, it doesn't work because as per docs we need aliases if those are used (it's fair). So let's switch to hinting aliases:

test=# /*+ Rows(a b c #1234) */ explain (analyze, verbose,buffers, timing, settings) SELECT * FROM (select * from t1 a) a, t2 b, t3 c WHERE a.id = b.id and b.id = c.id;
LOG:  pg_hint_plan[qno=0x1]: planner: enable_hint=1, hint_inhibit_level=1
LOG:  pg_hint_plan[qno=0x1]: planner: no valid hint
LOG:  pg_hint_plan[qno=0x2]: no match found in table:  application name = "psql", normalized_query="/*+ Rows(a b c #1234) */ explain (analyze, verbose,buffers, timing, settings) SELECT * FROM (select * from t1 a) a, t2 b, t3 c WHERE a.id = b.id and b.id = c.id;"
LOG:  hints in comment="Rows(a b c #1234) ", query="/*+ Rows(a b c #1234) */ explain (analyze, verbose,buffers, timing, settings) SELECT * FROM (select * from t1 a) a, t2 b, t3 c WHERE a.id = b.id and b.id = c.id;", debug_query_string="/*+ Rows(a b c #1234) */ explain (analyze, verbose,buffers, timing, settings) SELECT * FROM (select * from t1 a) a, t2 b, t3 c WHERE a.id = b.id and b.id = c.id;"
LOG:  pg_hint_plan[qno=0x1]: planner
LOG:  pg_hint_plan[qno=0x1]: setup_hint_enforcement no hint applied: relation=35070(t2), inhparent=0, current_hint=0x10f3f08, hint_inhibit_level=0, scanmask=0x1f
LOG:  pg_hint_plan[qno=0x1]: setup_hint_enforcement no hint applied: relation=35075(t3), inhparent=0, current_hint=0x10f3f08, hint_inhibit_level=0, scanmask=0x1f
LOG:  pg_hint_plan[qno=0x1]: setup_hint_enforcement no hint applied: relation=35065(t1), inhparent=0, current_hint=0x10f3f08, hint_inhibit_level=0, scanmask=0x1f
DEBUG:  RowsHint() trying joinrels '(b 2 4)' vs '#1234'
DEBUG:  RowsHint() trying joinrels '(b 3 4)' vs '#1234'
DEBUG:  RowsHint() trying joinrels '(b 2 3)' vs '#1234'
DEBUG:  RowsHint() trying joinrels '(b 2 3 4)' vs '#1234'
DEBUG:  adjusted rows 10 to 1234
DEBUG:  RowsHint() trying joinrels '(b 2 3 4)' vs '#1234'
DEBUG:  RowsHint() trying joinrels '(b 2 3 4)' vs '#1234'
LOG:  pg_hint_plan[qno=0x1]: HintStateDump: {used hints:Rows(a b c #1234)}, {not used hints:(none)}, {duplicate hints:(none)}, {error hints:(none)}
                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=5.88..7.40 rows=1234 width=24) (actual time=0.075..0.236 rows=100 loops=1)

[..] -> it worked, cool.

OK, but now the main problem enters the stage - let's change query to have duplicate aliases:

test=# /*+ Rows(a b #1234) */ explain (analyze, verbose,buffers, timing, settings) SELECT * FROM (select * from t1 a join t2 b using (id)) a, t2 b, t3 c WHERE a.id = b.id and b.id = c.id;
LOG:  pg_hint_plan[qno=0x2]: planner: enable_hint=1, hint_inhibit_level=1
LOG:  pg_hint_plan[qno=0x2]: planner: no valid hint
LOG:  pg_hint_plan[qno=0x3]: no match found in table:  application name = "psql", normalized_query="/*+ Rows(a b #1234) */ explain (analyze, verbose,buffers, timing, settings) SELECT * FROM (select * from t1 a join t2 b using (id)) a, t2 b, t3 c WHERE a.id = b.id and b.id = c.id;"
LOG:  hints in comment="Rows(a b #1234) ", query="/*+ Rows(a b #1234) */ explain (analyze, verbose,buffers, timing, settings) SELECT * FROM (select * from t1 a join t2 b using (id)) a, t2 b, t3 c WHERE a.id = b.id and b.id = c.id;", debug_query_string="/*+ Rows(a b #1234) */ explain (analyze, verbose,buffers, timing, settings) SELECT * FROM (select * from t1 a join t2 b using (id)) a, t2 b, t3 c WHERE a.id = b.id and b.id = c.id;"
LOG:  pg_hint_plan[qno=0x2]: planner
LOG:  pg_hint_plan[qno=0x2]: setup_hint_enforcement no hint applied: relation=35070(t2), inhparent=0, current_hint=0x1133940, hint_inhibit_level=0, scanmask=0x1f
LOG:  pg_hint_plan[qno=0x2]: setup_hint_enforcement no hint applied: relation=35075(t3), inhparent=0, current_hint=0x1133940, hint_inhibit_level=0, scanmask=0x1f
LOG:  pg_hint_plan[qno=0x2]: setup_hint_enforcement no hint applied: relation=35065(t1), inhparent=0, current_hint=0x1133940, hint_inhibit_level=0, scanmask=0x1f
LOG:  pg_hint_plan[qno=0x2]: setup_hint_enforcement no hint applied: relation=35070(t2), inhparent=0, current_hint=0x1133940, hint_inhibit_level=0, scanmask=0x1f
INFO:  pg_hint_plan: hint syntax error at or near "Rows(a b #1234) "
DETAIL:  Relation name "b" is ambiguous.

[..] -> ok, it's fair

Enter duplicate aliases

And we cannot use /+ Rows(t1 t2 #1234) / as per above and thus need to use aliases (but they are duplicate...), so let's try ANY_subquery just for start

test=# /*+ Rows(ANY_subquery b c #1234) */ explain (analyze, verbose,buffers, timing, settings) SELECT * FROM (select * from t1 a join t2 b using (id)) a, t2 b, t3 c WHERE a.id = b.id and b.id = c.id;
LOG:  pg_hint_plan[qno=0x4]: planner: enable_hint=1, hint_inhibit_level=1
LOG:  pg_hint_plan[qno=0x4]: planner: no valid hint
LOG:  pg_hint_plan[qno=0x5]: no match found in table:  application name = "psql", normalized_query="/*+ Rows(ANY_subquery b c #1234) */ explain (analyze, verbose,buffers, timing, settings) SELECT * FROM (select * from t1 a join t2 b using (id)) a, t2 b, t3 c WHERE a.id = b.id and b.id = c.id;"
LOG:  hints in comment="Rows(ANY_subquery b c #1234) ", query="/*+ Rows(ANY_subquery b c #1234) */ explain (analyze, verbose,buffers, timing, settings) SELECT * FROM (select * from t1 a join t2 b using (id)) a, t2 b, t3 c WHERE a.id = b.id and b.id = c.id;", debug_query_string="/*+ Rows(ANY_subquery b c #1234) */ explain (analyze, verbose,buffers, timing, settings) SELECT * FROM (select * from t1 a join t2 b using (id)) a, t2 b, t3 c WHERE a.id = b.id and b.id = c.id;"
LOG:  pg_hint_plan[qno=0x4]: planner
LOG:  pg_hint_plan[qno=0x4]: setup_hint_enforcement no hint applied: relation=35070(t2), inhparent=0, current_hint=0x1133940, hint_inhibit_level=0, scanmask=0x1f
LOG:  pg_hint_plan[qno=0x4]: setup_hint_enforcement no hint applied: relation=35075(t3), inhparent=0, current_hint=0x1133940, hint_inhibit_level=0, scanmask=0x1f
LOG:  pg_hint_plan[qno=0x4]: setup_hint_enforcement no hint applied: relation=35065(t1), inhparent=0, current_hint=0x1133940, hint_inhibit_level=0, scanmask=0x1f
LOG:  pg_hint_plan[qno=0x4]: setup_hint_enforcement no hint applied: relation=35070(t2), inhparent=0, current_hint=0x1133940, hint_inhibit_level=0, scanmask=0x1f
LOG:  pg_hint_plan[qno=0x4]: HintStateDump: {used hints:(none)}, {not used hints:Rows(ANY_subquery b c #1234)}, {duplicate hints:(none)}, {error hints:(none)}
                                                                  QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=6.16..7.23 rows=1 width=28) (actual time=0.138..0.375 rows=100 loops=1)
   Output: a.id, a.val, b_1.val, b.id, b.val, c.id, c.val
   Inner Unique: true
   Merge Cond: (a.id = c.id)
   Buffers: shared hit=10
   ->  Merge Join  (cost=0.84..56.57 rows=100 width=24) (actual time=0.097..0.270 rows=101 loops=1)
         Output: a.id, a.val, b_1.val, b_1.id, b.id, b.val
         Inner Unique: true
         Merge Cond: (a.id = b.id)
         Buffers: shared hit=9
         ->  Merge Join  (cost=0.56..90.36 rows=1000 width=16) (actual time=0.022..0.125 rows=101 loops=1)
               Output: a.id, a.val, b_1.val, b_1.id
               Inner Unique: true
               Merge Cond: (a.id = b_1.id)
               Buffers: shared hit=6
               ->  Index Scan using t1_pkey on public.t1 a  (cost=0.29..318.29 rows=10000 width=8) (actual time=0.012..0.039 rows=101 loops=1)
                     Output: a.id, a.val
                     Buffers: shared hit=3
               ->  Index Scan using t2_pkey on public.t2 b_1  (cost=0.28..43.27 rows=1000 width=8) (actual time=0.006..0.030 rows=101 loops=1)
                     Output: b_1.id, b_1.val
                     Buffers: shared hit=3
         ->  Index Scan using t2_pkey on public.t2 b  (cost=0.28..43.27 rows=1000 width=8) (actual time=0.072..0.095 rows=101 loops=1)
               Output: b.id, b.val
               Buffers: shared hit=3
   ->  Sort  (cost=5.32..5.57 rows=100 width=8) (actual time=0.038..0.050 rows=100 loops=1)
         Output: c.id, c.val
         Sort Key: c.id
         Sort Method: quicksort  Memory: 29kB
         Buffers: shared hit=1
         ->  Seq Scan on public.t3 c  (cost=0.00..2.00 rows=100 width=8) (actual time=0.008..0.018 rows=100 loops=1)
               Output: c.id, c.val
               Buffers: shared hit=1
 Settings: max_parallel_workers_per_gather = '0', work_mem = '9MB'
 Query Identifier: 846249015203497391
 Planning:
   Buffers: shared hit=81
 Planning Time: 1.575 ms
 Execution Time: 0.424 ms

[..] -> thus ANY_subquery is not helping here

Also , the optimizer was free and renamed b to b_1 as per above, so let's try hint it using that : 

test=# /*+ Rows(a b_1 #1234) */ explain (analyze, verbose,buffers, timing, settings) SELECT * FROM (select * from t1 a join t2 b using (id)) a, t2 b, t3 c WHERE a.id = b.id and b.id = c.id;
LOG:  pg_hint_plan[qno=0x7]: no match found in table:  application name = "psql", normalized_query="/*+ Rows(a b_1 #1234) */ explain (analyze, verbose,buffers, timing, settings) SELECT * FROM (select * from t1 a join t2 b using (id)) a, t2 b, t3 c WHERE a.id = b.id and b.id = c.id;"
LOG:  hints in comment="Rows(a b_1 #1234) ", query="/*+ Rows(a b_1 #1234) */ explain (analyze, verbose,buffers, timing, settings) SELECT * FROM (select * from t1 a join t2 b using (id)) a, t2 b, t3 c WHERE a.id = b.id and b.id = c.id;", debug_query_string="/*+ Rows(a b_1 #1234) */ explain (analyze, verbose,buffers, timing, settings) SELECT * FROM (select * from t1 a join t2 b using (id)) a, t2 b, t3 c WHERE a.id = b.id and b.id = c.id;"
LOG:  pg_hint_plan[qno=0x6]: planner
LOG:  pg_hint_plan[qno=0x6]: setup_hint_enforcement no hint applied: relation=35070(t2), inhparent=0, current_hint=0x10f3f60, hint_inhibit_level=0, scanmask=0x1f
LOG:  pg_hint_plan[qno=0x6]: setup_hint_enforcement no hint applied: relation=35075(t3), inhparent=0, current_hint=0x10f3f60, hint_inhibit_level=0, scanmask=0x1f
LOG:  pg_hint_plan[qno=0x6]: setup_hint_enforcement no hint applied: relation=35065(t1), inhparent=0, current_hint=0x10f3f60, hint_inhibit_level=0, scanmask=0x1f
LOG:  pg_hint_plan[qno=0x6]: setup_hint_enforcement no hint applied: relation=35070(t2), inhparent=0, current_hint=0x10f3f60, hint_inhibit_level=0, scanmask=0x1f
LOG:  pg_hint_plan[qno=0x6]: HintStateDump: {used hints:(none)}, {not used hints:Rows(a b_1 #1234)}, {duplicate hints:(none)}, {error hints:(none)}
                                                                  QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=6.16..7.23 rows=1 width=28) (actual time=0.124..0.361 rows=100 loops=1)

[..] -> it's not used... why?

I've cannot also reference the "b" as it is ambiguous

So i've added some little elog(bmsToString() at the start of make_join_rel() to see how code is launched , but it says:

test=# /*+ Rows(a b_1 #1234) */ explain (analyze, verbose,buffers, timing, settings) SELECT * FROM (select * from t1 a join t2 b using (id)) a, t2 b, t3 c WHERE a.id = b.id and b.id = c.id;
LOG:  pg_hint_plan[qno=0x5]: planner: enable_hint=1, hint_inhibit_level=1
LOG:  pg_hint_plan[qno=0x5]: planner: no valid hint
LOG:  pg_hint_plan[qno=0x6]: no match found in table:  application name = "psql", normalized_query="/*+ Rows(a b_1 #1234) */ explain (analyze, verbose,buffers, timing, settings) SELECT * FROM (select * from t1 a join t2 b using (id)) a, t2 b, t3 c WHERE a.id = b.id and b.id = c.id;"
LOG:  hints in comment="Rows(a b_1 #1234) ", query="/*+ Rows(a b_1 #1234) */ explain (analyze, verbose,buffers, timing, settings) SELECT * FROM (select * from t1 a join t2 b using (id)) a, t2 b, t3 c WHERE a.id = b.id and b.id = c.id;", debug_query_string="/*+ Rows(a b_1 #1234) */ explain (analyze, verbose,buffers, timing, settings) SELECT * FROM (select * from t1 a join t2 b using (id)) a, t2 b, t3 c WHERE a.id = b.id and b.id = c.id;"
LOG:  pg_hint_plan[qno=0x5]: planner
LOG:  pg_hint_plan[qno=0x5]: setup_hint_enforcement no hint applied: relation=35070(t2), inhparent=0, current_hint=0x1c21b50, hint_inhibit_level=0, scanmask=0x1f
LOG:  pg_hint_plan[qno=0x5]: setup_hint_enforcement no hint applied: relation=35075(t3), inhparent=0, current_hint=0x1c21b50, hint_inhibit_level=0, scanmask=0x1f
LOG:  pg_hint_plan[qno=0x5]: setup_hint_enforcement no hint applied: relation=35065(t1), inhparent=0, current_hint=0x1c21b50, hint_inhibit_level=0, scanmask=0x1f
LOG:  pg_hint_plan[qno=0x5]: setup_hint_enforcement no hint applied: relation=35070(t2), inhparent=0, current_hint=0x1c21b50, hint_inhibit_level=0, scanmask=0x1f
DEBUG:  make_join_rel() starting with '(b 4 5)'
DEBUG:  make_join_rel() starting with '(b 2 4)'
DEBUG:  make_join_rel() starting with '(b 3 4)'
DEBUG:  make_join_rel() starting with '(b 2 5)'
DEBUG:  make_join_rel() starting with '(b 3 5)'
DEBUG:  make_join_rel() starting with '(b 2 3)'
DEBUG:  make_join_rel() starting with '(b 2 4 5)'
DEBUG:  make_join_rel() starting with '(b 3 4 5)'
DEBUG:  make_join_rel() starting with '(b 2 4 5)'
DEBUG:  make_join_rel() starting with '(b 2 3 4)'
DEBUG:  make_join_rel() starting with '(b 3 4 5)'
DEBUG:  make_join_rel() starting with '(b 2 3 4)'
DEBUG:  make_join_rel() starting with '(b 2 4 5)'
DEBUG:  make_join_rel() starting with '(b 2 3 5)'
DEBUG:  make_join_rel() starting with '(b 3 4 5)'
DEBUG:  make_join_rel() starting with '(b 2 3 5)'
DEBUG:  make_join_rel() starting with '(b 2 3 4)'
DEBUG:  make_join_rel() starting with '(b 2 3 5)'
DEBUG:  make_join_rel() starting with '(b 2 3 4 5)'
DEBUG:  make_join_rel() starting with '(b 2 3 4 5)'
DEBUG:  make_join_rel() starting with '(b 2 3 4 5)'
DEBUG:  make_join_rel() starting with '(b 2 3 4 5)'
DEBUG:  make_join_rel() starting with '(b 2 3 4 5)'
DEBUG:  make_join_rel() starting with '(b 2 3 4 5)'
DEBUG:  make_join_rel() starting with '(b 2 3 4 5)'
LOG:  pg_hint_plan[qno=0x5]: HintStateDump: {used hints:(none)}, {not used hints:Rows(a b_1 #1234)}, {duplicate hints:(none)}, {error hints:(none)}
                                                                  QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=6.16..7.23 rows=1 width=28) (actual time=0.069..0.403 rows=100 loops=1)
   Output: a.id, a.val, b_1.val, b.id, b.val, c.id, c.val
   Inner Unique: true
   Merge Cond: (a.id = c.id)
   Buffers: shared hit=10
   ->  Merge Join  (cost=0.84..56.57 rows=100 width=24) (actual time=0.032..0.211 rows=101 loops=1)
         Output: a.id, a.val, b_1.val, b_1.id, b.id, b.val
         Inner Unique: true
         Merge Cond: (a.id = b.id)
         Buffers: shared hit=9
         ->  Merge Join  (cost=0.56..90.36 rows=1000 width=16) (actual time=0.025..0.131 rows=101 loops=1)
               Output: a.id, a.val, b_1.val, b_1.id
               Inner Unique: true
               Merge Cond: (a.id = b_1.id)
               Buffers: shared hit=6
               ->  Index Scan using t1_pkey on public.t1 a  (cost=0.29..318.29 rows=10000 width=8) (actual time=0.014..0.043 rows=101 loops=1)
                     Output: a.id, a.val
                     Buffers: shared hit=3
               ->  Index Scan using t2_pkey on public.t2 b_1  (cost=0.28..43.27 rows=1000 width=8) (actual time=0.006..0.031 rows=101 loops=1)
                     Output: b_1.id, b_1.val
                     Buffers: shared hit=3
         ->  Index Scan using t2_pkey on public.t2 b  (cost=0.28..43.27 rows=1000 width=8) (actual time=0.005..0.028 rows=101 loops=1)
               Output: b.id, b.val
               Buffers: shared hit=3
   ->  Sort  (cost=5.32..5.57 rows=100 width=8) (actual time=0.034..0.046 rows=100 loops=1)
         Output: c.id, c.val
         Sort Key: c.id
         Sort Method: quicksort  Memory: 29kB
         Buffers: shared hit=1
         ->  Seq Scan on public.t3 c  (cost=0.00..2.00 rows=100 width=8) (actual time=0.008..0.017 rows=100 loops=1)
               Output: c.id, c.val
               Buffers: shared hit=1

So , is it possible to hint with estimation a query with duplicate aliases inside? (even though internally optimizer renames those?)

michaelpq commented 1 month ago

So , is it possible to hint with estimation a query with duplicate aliases inside? (even though internally optimizer renames those?)

Don't think so as contrary to Oracle that enforces the hint to be after SELECT or one of the three DML keywords, pg_hint_plan can only define a single hint placed anywhere in the query, fails if there is a second hint string, and cannot attach the context of a query to a given hint.

Short answer: no by design, I am afraid.