public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ajay Pal <[email protected]>
To: Robert Haas <[email protected]>
Cc: Jakub Wartak <[email protected]>
Cc: Lukas Fittl <[email protected]>
Cc: Jacob Champion <[email protected]>
Cc: Dian Fay <[email protected]>
Cc: Matheus Alcantara <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: pg_plan_advice
Date: Tue, 27 Jan 2026 13:19:38 +0530
Message-ID: <CABRHmyvLPcx_K1T9Cwg4tFHiyh95fQU9tUhDwEFtvJiDsQDdaA@mail.gmail.com> (raw)
In-Reply-To: <CA+Tgmoa6EvDrqOfwyBwoVOWL7-y34=FUTwV62Rx32bN0RkDVOQ@mail.gmail.com>
References: <CA+TgmoZ-Jh1T6QyWoCODMVQdhTUPYkaZjWztzP1En4=ZHoKPzw@mail.gmail.com>
<CAKZiRmxtJAFG7e1+Vs9B8ngON=AOzJbuws+1ZeH4LsbJh5AzoQ@mail.gmail.com>
<CA+TgmoY9Ne_Sh10u6LSPc3wvOQPLp3kF9nBp3nqJEG2JGF2QiA@mail.gmail.com>
<CA+Tgmoa57S6mP=aTOXH2-gDAL4TMO1WbGgrHSg0s6J4zUH=04g@mail.gmail.com>
<[email protected]>
<CA+Tgmoaf__2B0BUL+vrg28P+3buX=Ti-kybqkHiLTtFrrCfzuA@mail.gmail.com>
<CA+TgmoYpcLNOuypOTdgCSLW7FuA=t6BtB3meTARHX2-Dj_81xQ@mail.gmail.com>
<[email protected]>
<CA+TgmoZjv9OyFu1Gkt78w0vWEti8S33w8trYHmErf-GMmGSi=w@mail.gmail.com>
<[email protected]>
<CA+TgmoaOSBQD9Ux4eG40w723ZN=c0J7p-+oX4+J8urUeyLMo5w@mail.gmail.com>
<CAOYmi+=g+MMoOpWkk2weXWKJcKH0eKey8gKHHdH0dF4Tiawrhw@mail.gmail.com>
<CA+TgmobwaT=PXPDDrgDup+jA8KHBbkxigtziD-zNzAKKkQYVgQ@mail.gmail.com>
<CAOYmi+mOmEW=amDRQMfw6-Fb3ZmDEQFaJzwk8Bc8W8DzaP85XQ@mail.gmail.com>
<CA+TgmoaX2AMW4cdFM3OngBJxmxpkdmzF33R7-CWhvRLfucbFMg@mail.gmail.com>
<CAOYmi+njnRGcomnxTY6vsEW3wWigc0ruB0EyWFpb+PVVE8sWpw@mail.gmail.com>
<CAOYmi+k4AyWCQHK=XVF99KVDuFkqxcADao61OWGLxu0nRYMONQ@mail.gmail.com>
<CA+TgmoZ0x3ym_oueXRWzbM_=6ucKoPZVGj3rRMLBDC_FnetXDw@mail.gmail.com>
<CAP53Pkycc=7N2bLzVT3x+qE1JamvRZWev5tFjdLJ1+-AV3Di+Q@mail.gmail.com>
<CA+TgmoaKhuD91RnazbRyGkmP7--JdNq8oNDC3UcgTZSWbMxC7w@mail.gmail.com>
<CAP53Pkw5-wMEeDJXFmqo_RTyL_spzCXb7HHKrbSnQqokVoZcNQ@mail.gmail.com>
<CA+Tgmob-69bzbdi3U_QtebqAf6u1y8js=5=oNK639csVe1VbhA@mail.gmail.com>
<CAKZiRmz4zrZfx_wgzkTD88HzKiA22HKPUnubR3is0bjKra6Utg@mail.gmail.com>
<CA+Tgmobp8gcB9DOxNUL10xAXXq=ya6oHDUJqtNLf_3ojxm5-fQ@mail.gmail.com>
<CAKZiRmznM4cc_N5qQBgEdDj9F5J8=zTJbWm9erZzhp9jP7LMbg@mail.gmail.com>
<CA+Tgmoa5HjTnBMNQuW5QrCUacC9gdok+02a2Y9TTGWtTQ6OBvQ@mail.gmail.com>
<CAKZiRmzpO=PpLmXEQUO5CETzT0mmLhT5P656hetseKQUgZ6BQg@mail.gmail.com>
<CA+TgmobRAjY+xs_=fKMJ4NgW_i4bMoD5kW7oKrGACLLcY04ysA@mail.gmail.com>
<CAKZiRmxmhjnKp-GMvdL-2jE-nic5g7-TLdsveirrbRv2zdSdyQ@mail.gmail.com>
<CABRHmyuoOBXG_SVG=xpH+5+hacqfofniYSZvyJBRL7EadphcVw@mail.gmail.com>
<CA+TgmoaDqNB68kN8OO=4rKSALrWQ8LGRpU_kKi_vr9p=YgBqYQ@mail.gmail.com>
<CA+Tgmoa6EvDrqOfwyBwoVOWL7-y34=FUTwV62Rx32bN0RkDVOQ@mail.gmail.com>
Hi,
with v12 patch, found below observations,
#1 Grouped Hash Join, This forces the join of dim1 and dim2 to happen
first, and then places that resulting set on the inner side of a Hash
Join against fact.
but the planner partially matches the generated advice.
CREATE TABLE fact (f_id int, d1_id int, d2_id int, d3_id int);
CREATE TABLE dim1 (id int PRIMARY KEY, val text);
CREATE TABLE dim2 (id int PRIMARY KEY, val text);
CREATE TABLE dim3 (id int PRIMARY KEY, val text);
INSERT INTO fact SELECT g, g%10, g%10, g%10 FROM generate_series(1, 10000) g;
INSERT INTO dim1 SELECT g, 'd1-'||g FROM generate_series(0, 9) g;
INSERT INTO dim2 SELECT g, 'd2-'||g FROM generate_series(0, 9) g;
INSERT INTO dim3 SELECT g, 'd3-'||g FROM generate_series(0, 9) g;
ANALYZE fact, dim1, dim2, dim3;
-- We want (dim1 JOIN dim2) to be the inner side of a Hash Join
SET LOCAL pg_plan_advice.advice = 'HASH_JOIN((dim1 dim2))';
postgres=*# EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM fact
JOIN dim1 ON fact.d1_id = dim1.id
JOIN dim2 ON fact.d2_id = dim2.id;
QUERY PLAN
-----------------------------------------------------------
Nested Loop
Disabled: true
-> Nested Loop
Disabled: true
-> Seq Scan on fact
-> Index Scan using dim1_pkey on dim1
Index Cond: (id = fact.d1_id)
-> Index Scan using dim2_pkey on dim2
Index Cond: (id = fact.d2_id)
Supplied Plan Advice:
HASH_JOIN((dim1 dim2)) /* partially matched */
Generated Plan Advice:
JOIN_ORDER(fact dim1 dim2)
NESTED_LOOP_PLAIN(dim1 dim2)
SEQ_SCAN(fact)
INDEX_SCAN(dim1 public.dim1_pkey dim2 public.dim2_pkey)
NO_GATHER(fact dim1 dim2)
(17 rows)
#2 Multiple Instances of Same Table in Subqueries, here target the
second instance of dim1 inside the subquery 'sq'. both seq_scan and
index_scan advices are not matching.
SET LOCAL pg_plan_advice.advice = 'SEQ_SCAN(dim1#2@sq)
INDEX_SCAN(dim1@sq dim1_pkey)';
postgres=*# EXPLAIN (COSTS OFF, PLAN_ADVICE)
SELECT * FROM fact
JOIN (
SELECT a.id FROM dim1 a
JOIN dim1 b ON a.id = b.id
OFFSET 0
) sq ON fact.d1_id = sq.id;
QUERY PLAN
---------------------------------------------------
Hash Join
Hash Cond: (fact.d1_id = b.id)
-> Seq Scan on fact
-> Hash
-> Seq Scan on dim1 b
Supplied Plan Advice:
SEQ_SCAN(dim1#2@sq) /* not matched */
INDEX_SCAN(dim1@sq dim1_pkey) /* not matched */
Generated Plan Advice:
JOIN_ORDER(fact sq)
HASH_JOIN(sq)
SEQ_SCAN(b@sq fact)
NO_GATHER(fact b@sq)
(13 rows)
Thanks
Ajay
On Mon, Jan 26, 2026 at 9:38 PM Robert Haas <[email protected]> wrote:
>
> Here is v12.
>
> The big change in this version is that I've added extensive SGML
> documentation for v0005. If the README was a little too low-level for
> you, this might work better. If you'd like to view it without
> downloading the patch set, I've put it up here:
>
> https://robertmhaas.github.io/postgresql-static/html-pgpa-v12/pgplanadvice.html
>
> Aside from that:
>
> * Added a new GUC pg_plan_advice.always_store_advice_details. Without
> that, you can't generate advice or see feedback on supplied advice
> when using prepared queries, because we don't know at plan time that
> it's right to incur the overhead of generating that stuff, and most of
> the time it won't be.
> * Revoked privileges on pg_clear_collected_shared_advice() as I had
> already done on pg_get_collected_shared_advice().
> * Removed a bogus elog(ERROR) in pgpa_walker_would_advise() in favor
> of returning 0. I think somebody, likely Jakub, pointed this out
> earlier, but I didn't quite absorb what I was being told until I
> rediscovered the problem.
> * Added a bunch more tests. I think the test coverage is getting
> pretty decent now, but it could still use some tests targeting more
> complex scenarios and corner cases. If you are curious about the
> coverage report, see here:
>
> https://robertmhaas.github.io/postgresql-static/coveragereport-pgpa-v12/contrib/pg_plan_advice/index...
>
> The low number for pgpa_scanner.l is basically bogus, but I don't know
> of a way to make it not bogus. The low number for pgpa_ast.c is due to
> a bunch of things related to bitmap scans not being right, which at
> this point is, I think, the largest outstanding issue with the patch.
> It's probably more interesting to look into ways of covering a few
> more lines from pgpa_planner.c and pgpa_walker.c, which is where a lot
> of the complexity in this code lives. Also, it would be nice to have
> coverage of foreign scan cases, but I'm not quite sure what I need to
> do to create tests for this module that also depend on postgres_fdw.
> Any tips appreciated.
>
> --
> Robert Haas
> EDB: http://www.enterprisedb.com
view thread (143+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: pg_plan_advice
In-Reply-To: <CABRHmyvLPcx_K1T9Cwg4tFHiyh95fQU9tUhDwEFtvJiDsQDdaA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox