MIME-Version: 1.0
References: <20250430151647.7kootztymzznydn5@victor>
In-Reply-To: <20250430151647.7kootztymzznydn5@victor>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Wed, 30 Apr 2025 15:58:59 -0400
Message-ID: <CANzqJaAaU9RJU8G4hDpAHPB+f6xP4Gf-A_=YAk=E-PZgSVhkdg@mail.gmail.com>
Subject: Re: Index not used in certain nested views but not in others
To: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000c56fd50634045955"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaAaU9RJU8G4hDpAHPB%2Bf6xP4Gf-A_%3DYAk%3DE-PZgSVhkdg%40mail.gmail.com>
Precedence: bulk

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

On Wed, Apr 30, 2025 at 3:51=E2=80=AFPM Markus Demleitner <
msdemlei@ari.uni-heidelberg.de> wrote:

> Dear List,
>
> I know how tedious mails with a subject of the type "I don't understand
> what the planner does" are, but on this one I'm really stumped.
> Regrettably, the situation is also a bit complex.  Hopefully, someone
> will bear with me.
>
> So, in a PostgreSQL 15.12 I have a view over a single table with ~20
> columns (the only relevant columns here are the ones that somehow
> contain "pub[lisher]_did", the others are just there for context; I'm
> going to call these "pubdids" from here on in the prose):
>
[snip]

> Is there anything that would explain that behaviour given I've switched
> off the genetic optimiser and postgres has hopefully exhaustively
> searched the space of plans in both cases?
>

Are the tables regularly analyzed and vacuumed?  (The default autovacuum
analyze threshold of 20% is pretty high.)

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

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

<div dir=3D"ltr"><div dir=3D"ltr">On Wed, Apr 30, 2025 at 3:51=E2=80=AFPM M=
arkus Demleitner &lt;<a href=3D"mailto:msdemlei@ari.uni-heidelberg.de">msde=
mlei@ari.uni-heidelberg.de</a>&gt; wrote:</div><div class=3D"gmail_quote gm=
ail_quote_container"><blockquote class=3D"gmail_quote" style=3D"margin:0px =
0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Dear=
 List,<br>
<br>
I know how tedious mails with a subject of the type &quot;I don&#39;t under=
stand<br>
what the planner does&quot; are, but on this one I&#39;m really stumped.<br=
>
Regrettably, the situation is also a bit complex.=C2=A0 Hopefully, someone<=
br>
will bear with me.<br>
<br>
So, in a PostgreSQL 15.12 I have a view over a single table with ~20<br>
columns (the only relevant columns here are the ones that somehow<br>
contain &quot;pub[lisher]_did&quot;, the others are just there for context;=
 I&#39;m<br>
going to call these &quot;pubdids&quot; from here on in the prose):<br></bl=
ockquote><div>[snip]=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"=
margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-lef=
t:1ex">Is there anything that would explain that behaviour given I&#39;ve s=
witched<br>
off the genetic optimiser and postgres has hopefully exhaustively<br>
searched the space of plans in both cases?<br></blockquote><div></div></div=
><div><br></div><div>Are the tables regularly analyzed and vacuumed?=C2=A0 =
(The default=C2=A0autovacuum analyze threshold of 20% is pretty high.)</div=
><div><br></div><span class=3D"gmail_signature_prefix">-- </span><br><div d=
ir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">Death to &lt;Redacted=
&gt;, and butter sauce.<div>Don&#39;t boil me, I&#39;m still alive.<br><div=
><div>&lt;Redacted&gt; lobster!</div></div></div></div></div></div>

--000000000000c56fd50634045955--