CTE and PostgreSQL temporary index
I am working on a postgres database where I will be using a mix of relational tables and jsonb tables (which can be indexed).
I use many CTE queries to insert or update data fetched from a temporary table, for example:
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 is possible to create a temporary index on such tables, and if there is, is it possible to create an index in jsonb type fieds too? (given these temporary tables)
source to share
Pretty sure there is no way to create an index for the CTE, because it's basically just a subquery, not a table - it's not stored in memory anywhere, just rolled into the query plan as needed.
But you could do Create Temp Table information As
this instead, and then index that, with very little change to your queries.
You can index the materialized view as well, but if you have temporary data underneath it, just creating another temp table probably makes the most sense.
source to share
No, you cannot create an index by parts of the query at query time.
CTEs (Common Table Expressions), also called Factoring Subquery. The concept allows the optimizer to generate an execution plan for a complex query, allowing it to reduce the repetition of subqueries by placing it in temporary space instead of repeating the same steps. Putting it into a single request generates one large list of steps that are executed as a normal request, not as a procedure. It can be used for presentation and have an execution plan stored in memory.
Part of the choice of not allowing indexing is that it will need to invoke indexing, which will then need to be optimized for the main query, or worse, the execution plan will have to leave room to guess at which stage to execute. Since we already have temporary tables that can be indexed, I believe it keeps it clean to exclude this indexing activity from the execution plan. Create a temporary table, index it, then when you run your query, it will have the execution plan you just optimized. Also, the temp table can persist throughout your procedure, the cte time data is deleted after the query completes.
But you can still use a temporary table in the CTE so it's not all or nothing.
source to share