Original-Subject: Re: Any way to get nested loop index joins on CTEs?
Original-From: Jean-Christophe BOGGIO <postgresql@thefreecat.org>
Message-ID: <e2dd3659-50e4-4727-a11c-f23b8d5c254d@thefreecat.org>
Date: Wed, 16 Jul 2025 13:24:54 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Any way to get nested loop index joins on CTEs?
To: pgsql-performance@lists.postgresql.org
References: 
 <CAFvQSYQAJWioHyUNUOjhj0_P0Z9_f_G_5B=9XyYZyPUpzsWW=A@mail.gmail.com>
Content-Language: en-US, fr
From: Jean-Christophe BOGGIO <postgresql@thefreecat.org>
In-Reply-To: 
 <CAFvQSYQAJWioHyUNUOjhj0_P0Z9_f_G_5B=9XyYZyPUpzsWW=A@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/e2dd3659-50e4-4727-a11c-f23b8d5c254d%40thefreecat.org>
Precedence: bulk

Following up on this, I very often have to create PLPgSql functions to 
workaround this problem: create one (or several) temp table(s) (with ON 
COMMIT DROP), analyze it/them and create indices on some field(s).

Being able to write something like:

WITH xxx AS MATERIALIZED ANALYZED INDEXED ON field1 (
     SELECT DISTINCT myfield AS field1
     FROM table1
)
SELECT field2
FROM table2
JOIN xxx USING(field1);

would help a lot in some cases. Of course, the syntax is just a thought 
but the general idea is there.