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

--0000000000006f40300634f1960b
Content-Type: text/plain; charset="UTF-8"

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 GUC
is about the only way to overrule the choice to use the custom plan.

David

>

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

<div dir=3D"auto"><div><div class=3D"gmail_quote gmail_quote_container"><di=
v dir=3D"ltr" class=3D"gmail_attr">On Mon, 12 May 2025, 05:08 Andrei Lepikh=
ov, &lt;<a href=3D"mailto:lepihov@gmail.com">lepihov@gmail.com</a>&gt; wrot=
e:</div><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border=
-left:1px #ccc solid;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 dir=
=3D"auto"><br></div><div dir=3D"auto">David</div><div dir=3D"auto"><div cla=
ss=3D"gmail_quote gmail_quote_container"><blockquote class=3D"gmail_quote" =
style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
</blockquote></div></div></div>

--0000000000006f40300634f1960b--