CTE and temporary index on PostgreSQL

Question

I work on postgres database where I'll using a mix of relational tables and jsonb tables (which can be indexed).

I have being using a lot of CTE queries to insert or update data selecting from a temporary table, like:

WITH information as (
    select fieldA, fieldB, fieldC from tableA
)
insert (fieldA, fieldB, fieldC)
SELECT inf.fieldA, inf.fieldB, inf.fieldC
from information inf

Well, I would like to know if it's possible create temporary index in this kind of tables and if is, is possible create index in jsonb type fieds too? (considering this temporary tables)

Answer 1

Pretty sure there's no way to create an index on a CTE, because it's basically just a sub-query, not a table - it's not persisted in memory anywhere, just rolled into the query plan as needed.

But you could do Create Temp Table information As instead, then index that, with very little change to your queries.

You can also index a Materialized View, but if you have temp data underneath, just creating another temp table probably makes most sense.