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.

Answer 2

No you can not create an index on parts of a query, during the query.

CTE (common table expressions), is also called Subquery Factoring. The concept allows the optimizer to generate the execution plan for a complex query while allowing it to reduce the repetition of the sub-queries by putting it into temp space instead of re-executing the same steps. Putting it in a single query generates one big list of steps that are all executed as a regular query instead of as a procedure. It can there for be used as a view and have a stored execution plan in memory.
Part of the choice of not to allowing the indexing is that it would require a call out for the indexing that would then have to be re-optimized for the main query or worse, the execution plan would have to leave space to guess on which step to execute. Since we already have temp tables that can be indexed, I believe it keeps it clean to keep this indexing activity out of the execution plan. Build a temp table, index it, then when you run the query it will have freshly optimized execution plan. Plus the temp table can persist for the duration of your procedure, the cte temp data is dropped after the completion of the query.

But you can still use a temp table in a CTE so its not all or nothing.