From: Arne Roland <A.Roland@index.de>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
CC: Julien Rouhaud <rjuju123@gmail.com>, pgsql-hackers
	<pgsql-hackers@postgresql.org>
Subject: Re: missing indexes in indexlist with partitioned tables
Thread-Topic: missing indexes in indexlist with partitioned tables
Thread-Index: 
 AQHXsJoeXw6ojQG4NkOLdiX4KUBdD6vkTOpcgIAWJQCAA2SO7oAAbRh////16wCAADDYxQ==
Date: Mon, 17 Jan 2022 20:32:40 +0000
Message-ID: <2641568c18de40e8b1528fc9d4d80127@index.de>
References: 
 <405b11a5a0504b76b997e312e543a69b@index.de>,<202201171816.r6tkrn7drkm3@alvherre.pgsql>
In-Reply-To: <202201171816.r6tkrn7drkm3@alvherre.pgsql>
Accept-Language: en-US, de-DE
Content-Language: en-US
Content-Type: multipart/mixed;
	boundary="_004_2641568c18de40e8b1528fc9d4d80127indexde_"
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/2641568c18de40e8b1528fc9d4d80127%40index.de>
Precedence: bulk

--_004_2641568c18de40e8b1528fc9d4d80127indexde_
Content-Type: multipart/alternative;
	boundary="_000_2641568c18de40e8b1528fc9d4d80127indexde_"

--_000_2641568c18de40e8b1528fc9d4d80127indexde_
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable

Hi!

Afaiac the join pruning where the outer table is a partitioned table is the=
 relevant case.

I am not sure whether there are other cases.
The join pruning, which works great for plain relations since 9.0, falls sh=
ort for partitioned tables, since the optimizer fails to prove uniqueness t=
here.


In practical cases inner and outer tables are almost surely different ones,=
 but I reattached a simpler example. It's the one, I came up with back in S=
eptember.

I've seen this can be a reason to avoid partitioning for the time being, if=
 the application relies on join pruning. I think generic views make it almo=
st necessary to have it. If you had a different answer in mind, please don'=
t hesitate to ask again.


Regards
Arne


________________________________
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Sent: Monday, January 17, 2022 7:16:08 PM
To: Arne Roland
Cc: Julien Rouhaud; pgsql-hackers
Subject: Re: missing indexes in indexlist with partitioned tables

Hmm, can you show cases of queries for which having this new
partIndexlist changes plans?

--
=C1lvaro Herrera         PostgreSQL Developer  =97  https://www.EnterpriseD=
B.com/
"Saca el libro que tu religi=F3n considere como el indicado para encontrar =
la
oraci=F3n que traiga paz a tu alma. Luego rebootea el computador
y ve si funciona" (Carlos Ducl=F3s)


--_000_2641568c18de40e8b1528fc9d4d80127indexde_
Content-Type: text/html; charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable

<html>
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3DWindows-1=
252">
<meta name=3D"Generator" content=3D"Microsoft Exchange Server">
<!-- converted from text --><style><!-- .EmailQuote { margin-left: 1pt; pad=
ding-left: 4pt; border-left: #800000 2px solid; } --></style>
</head>
<body>
<meta content=3D"text/html; charset=3DUTF-8">
<style type=3D"text/css" style=3D"">
<!--
p
	{margin-top:0;
	margin-bottom:0}
-->
</style>
<div dir=3D"ltr">
<div id=3D"x_divtagdefaultwrapper" dir=3D"ltr" style=3D"font-size:12pt; col=
or:#000000; font-family:Calibri,Helvetica,sans-serif">
<p>Hi!<br>
<br>
Afaiac the join pruning where the outer table is a partitioned table is the=
 relevant case.</p>
<p>I am not sure whether there are other cases.<br>
The join pruning, which works great for plain relations since 9.0, falls sh=
ort for partitioned tables, since the optimizer fails to prove uniqueness t=
here.</p>
<p><br>
</p>
<p>In practical cases inner and outer tables are almost surely different on=
es, but I reattached a simpler example. It's the one, I came up with back i=
n September.</p>
<p>I've seen this can be a reason to avoid partitioning for the time being,=
 if the application relies on join pruning. I think generic views make it a=
lmost necessary to have it. If you had a different answer in mind, please d=
on't hesitate to ask again.<br>
</p>
<p><br>
</p>
<p>Regards<br>
Arne</p>
<p><br>
</p>
</div>
<hr tabindex=3D"-1" style=3D"display:inline-block; width:98%">
<div id=3D"x_divRplyFwdMsg" dir=3D"ltr"><font face=3D"Calibri, sans-serif" =
color=3D"#000000" style=3D"font-size:11pt"><b>From:</b> Alvaro Herrera &lt;=
alvherre@alvh.no-ip.org&gt;<br>
<b>Sent:</b> Monday, January 17, 2022 7:16:08 PM<br>
<b>To:</b> Arne Roland<br>
<b>Cc:</b> Julien Rouhaud; pgsql-hackers<br>
<b>Subject:</b> Re: missing indexes in indexlist with partitioned tables</f=
ont>
<div>&nbsp;</div>
</div>
</div>
<font size=3D"2"><span style=3D"font-size:10pt;">
<div class=3D"PlainText">Hmm, can you show cases of queries for which havin=
g this new<br>
partIndexlist changes plans?<br>
<br>
-- <br>
=C1lvaro Herrera&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PostgreSQL=
 Developer&nbsp; =97&nbsp; <a href=3D"https://www.EnterpriseDB.com/">
https://www.EnterpriseDB.com/</a><br>
&quot;Saca el libro que tu religi=F3n considere como el indicado para encon=
trar la<br>
oraci=F3n que traiga paz a tu alma. Luego rebootea el computador<br>
y ve si funciona&quot; (Carlos Ducl=F3s)<br>
<br>
<br>
<br>
</div>
</span></font>
</body>
</html>

--_000_2641568c18de40e8b1528fc9d4d80127indexde_--

--_004_2641568c18de40e8b1528fc9d4d80127indexde_
Content-Type: application/sql; name="indexlist_to_late.sql"
Content-Description: indexlist_to_late.sql
Content-Disposition: attachment; filename="indexlist_to_late.sql"; size=296;
	creation-date="Mon, 17 Jan 2022 20:17:24 GMT";
	modification-date="Mon, 17 Jan 2022 20:17:24 GMT"
Content-Transfer-Encoding: base64

Y3JlYXRlIHRhYmxlIHB1YmxpYy5hIChpZCBiaWdpbnQgbm90IG51bGwgcHJpbWFyeSBrZXkpIHBh
cnRpdGlvbiBieSByYW5nZSAoaWQpOwpjcmVhdGUgdGFibGUgYiBwYXJ0aXRpb24gb2YgYSBmb3Ig
dmFsdWVzIGZyb20gKDApIHRvICgxMDAwKTsKCi0tIHdvcmtzIGFzIGV4cGVjdGVkCmV4cGxhaW4g
c2VsZWN0IGEuaWQgZnJvbSBhIGxlZnQgam9pbiBiIHVzaW5nIChpZCk7Ci0tIGluZGV4bGlzdCBp
cyBzZXQgdG8gbGF0ZQpleHBsYWluIHNlbGVjdCBhLmlkIGZyb20gYSBsZWZ0IGpvaW4gYSBhMSB1
c2luZyAoaWQpOwo=

--_004_2641568c18de40e8b1528fc9d4d80127indexde_--