pg_hint_plan
pg_hint_plan copied to clipboard
Published
20 hours ago •
ossc-db
ossc-db
Hinting estimation with duplicate alias names doesn't seem to be supported (?)
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?)
