From: Arne Roland <A.Roland@index.de>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Julien Rouhaud <rjuju123@gmail.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: missing indexes in indexlist with partitioned tables
Date: Mon, 17 Jan 2022 20:32:40 +0000
Message-ID: <2641568c18de40e8b1528fc9d4d80127@index.de> (raw)
In-Reply-To: <202201171816.r6tkrn7drkm3@alvherre.pgsql>
References: <405b11a5a0504b76b997e312e543a69b@index.de>
<202201171816.r6tkrn7drkm3@alvherre.pgsql>
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 short for partitioned tables, since the optimizer fails to prove uniqueness there.
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 September.
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 almost 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?
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"Saca el libro que tu religión considere como el indicado para encontrar la
oración que traiga paz a tu alma. Luego rebootea el computador
y ve si funciona" (Carlos Duclós)
Attachments:
[application/sql] indexlist_to_late.sql (296B, ../2641568c18de40e8b1528fc9d4d80127@index.de/3-indexlist_to_late.sql)
download
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: pgsql-hackers@postgresql.org
Cc: A.Roland@index.de, alvherre@alvh.no-ip.org, rjuju123@gmail.com
Subject: Re: missing indexes in indexlist with partitioned tables
In-Reply-To: <2641568c18de40e8b1528fc9d4d80127@index.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox