MIME-Version: 1.0
References: 
 <CAK-MWwS_2UwF7XPy-8XpcVqUjEveDjDQRbXxPyzvJM+ooWeh9A@mail.gmail.com>
 <b7da2330-1054-4bb8-939a-cb05d377f70f@gmail.com>
 <CAApHDvrduvaF6nN9ZQSfOdE0Na9SwPOkwYKn=Rb2a=Q9B72MAQ@mail.gmail.com>
In-Reply-To: 
 <CAApHDvrduvaF6nN9ZQSfOdE0Na9SwPOkwYKn=Rb2a=Q9B72MAQ@mail.gmail.com>
From: Maxim Boguk <maxim.boguk@gmail.com>
Date: Mon, 12 May 2025 18:18:43 +0300
Message-ID: 
 <CAK-MWwTL=_NjJc9v241VQdavOC4deWgPjoqDxLbvm=wUFKq=ug@mail.gmail.com>
Subject: Re: inefficient/wrong plan cache mode selection for queries with
 partitioned tables (postgresql 17)
To: David Rowley <dgrowleyml@gmail.com>
Cc: Andrei Lepikhov <lepihov@gmail.com>,
	Pgsql Performance <pgsql-performance@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000000af0a0634f1d7dc"
Archived-At: 
 <https://www.postgresql.org/message-id/CAK-MWwTL%3D_NjJc9v241VQdavOC4deWgPjoqDxLbvm%3DwUFKq%3Dug%40mail.gmail.com>
Precedence: bulk

--00000000000000af0a0634f1d7dc
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, May 12, 2025 at 6:01=E2=80=AFPM David Rowley <dgrowleyml@gmail.com>=
 wrote:

> On Mon, 12 May 2025, 05:08 Andrei Lepikhov, <lepihov@gmail.com> wrote:
>
>> Thanks for this puzzle!
>> I suppose, in case generic planning is much faster than custom one,
>> there are two candidates exist:
>> 1. Touching the index during planning causes too much overhead - see
>> get_actual_variable_range
>> 2. You have a massive default_statistics_target for a table involved.
>>
>
> This is just an artifact of the fact that runtime pruning is not factored
> into the costs. Note the cost of the generic plan. The plan_cache_mode GU=
C
> is about the only way to overrule the choice to use the custom plan.
>

Situation quite the opposite - I need to force a generic plan because it
has the same execution time as a custom plan but performs 20-50x faster
(because in custom plan case - 95-98% time spent in planning not in
execution).

And the problem is that the cost of a custom plan ignores the cost of
planning itself (which is like 2x orders of magnitude worse than the cost
of real time partition pruning of a generic plan). I started thinking of
something like cost_planner GUC to help with similar issues (where planning
cost calculated as cost_planned*(some heuristic function with amount
involved in query tables).

In my case the high cost of planning itself should force the database to
use generic plan.

--=20
Maxim Boguk
Senior Postgresql DBA

Phone UA: +380 99 143 0000
Phone AU: +61  45 218 5678

--00000000000000af0a0634f1d7dc
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div class=3D"gmail_defa=
ult" style=3D"font-family:monospace,monospace;font-size:large"><br></div></=
div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On=
 Mon, May 12, 2025 at 6:01=E2=80=AFPM David Rowley &lt;<a href=3D"mailto:dg=
rowleyml@gmail.com" target=3D"_blank">dgrowleyml@gmail.com</a>&gt; wrote:<b=
r></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex=
;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"auto"=
><div><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On M=
on, 12 May 2025, 05:08 Andrei Lepikhov, &lt;<a href=3D"mailto:lepihov@gmail=
.com" target=3D"_blank">lepihov@gmail.com</a>&gt; wrote:</div><blockquote c=
lass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px soli=
d rgb(204,204,204);padding-left:1ex">
Thanks for this puzzle!<br>
I suppose, in case generic planning is much faster than custom one, <br>
there are two candidates exist:<br>
1. Touching the index during planning causes too much overhead - see <br>
get_actual_variable_range<br>
2. You have a massive default_statistics_target for a table involved.<br></=
blockquote></div></div><div dir=3D"auto"><br></div><div dir=3D"auto">This i=
s just an artifact of the fact that runtime pruning is not factored into th=
e costs. Note the cost of the generic plan. The plan_cache_mode GUC is abou=
t the only way to overrule the choice to use the custom plan.</div></div></=
blockquote><div><br></div><div style=3D"font-family:monospace,monospace;fon=
t-size:large" class=3D"gmail_default">Situation quite the opposite - I need=
 to force a generic plan because it has the same execution time as a custom=
 plan but performs 20-50x faster (because in custom plan case - 95-98% time=
 spent in planning not in execution). <br></div><div style=3D"font-family:m=
onospace,monospace;font-size:large" class=3D"gmail_default"><br></div><div =
style=3D"font-family:monospace,monospace;font-size:large" class=3D"gmail_de=
fault">And the problem is that the cost of a custom plan ignores the cost o=
f planning itself (which is like 2x orders of magnitude worse than the cost=
 of real time partition pruning of a generic plan). I started thinking of s=
omething like cost_planner GUC to help with similar issues (where planning =
cost calculated as cost_planned*(some heuristic function with amount involv=
ed in query tables).</div><div style=3D"font-family:monospace,monospace;fon=
t-size:large" class=3D"gmail_default"><br></div><div style=3D"font-family:m=
onospace,monospace;font-size:large" class=3D"gmail_default">In my case the =
high cost of planning itself should force the database to use generic plan.=
</div><div style=3D"font-family:monospace,monospace;font-size:large" class=
=3D"gmail_default"><br></div></div><span class=3D"gmail_signature_prefix">-=
- </span><br><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr"><d=
iv><div dir=3D"ltr"><div><div dir=3D"ltr"><div><div dir=3D"ltr"><div><div d=
ir=3D"ltr"><div><div dir=3D"ltr"><div><div dir=3D"ltr"><span style=3D"font-=
family:monospace,monospace">Maxim Boguk<br>Senior Postgresql DBA<br><br>Pho=
ne UA: +380 99 143 0000<br>Phone AU: +61=C2=A0 45 218 5678<br></span><br></=
div></div></div></div></div></div></div></div></div></div></div></div></div=
></div></div>
</div>

--00000000000000af0a0634f1d7dc--