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)

Zippora

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.

Zonation

It's unfortunate that PostgreSQL can't currently temporarily index CTE terms. Other database products can do so, and it can be a huge performance win. There's no fundamental reason we couldn't do so, but it'd be complicated and intrusive in PostgreSQL because the planner is stats- and catalog-driven; we'd have to create "possible index" paths then a plan node that generated the real index on demand. – Dextrogyrate

@CraigRinger Huh, I kind of assumed that it wouldn't make any sense in any implementation of CTEs. Turns out my answer was only accidentally right! – Zonation

@Zonation Dynamic indexes can be a real win for plans where hash or merge joins aren't feasible. You just build a b-tree, use it to do your work and throw it away at the end. – Dextrogyrate

@CraigRinger: I'm curious: which DBMS can create a temporary index on a CTE? – Thuggee

The only real problem with not being able to index CTEs is that there's no way to make a RECURSIVE CTE performant. Non-recursive CTEs which can be factored out as TEMP TABLEs with indexes, recursive CTEs cannot. – Issue

"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." This statement is wrong, until Postgres 12 every CTE is persisted in memory (and on disk if too large), it is never "a subquery rolled into the query plan as needed". Postgres 12 makes this behavior configurable, i.e. allows CTEs to be treated as subqueries – Issue

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.

Chinquapin

I am qurious about your comment that we can use a temp table in a CTE. How do you mean? Can we somehow create a temp table in a cte that we use for a while and is then droped at the end of query? – Trothplight

Answer 3

There is no direct way of creating an index on CTE but we can force it to use index through other way in simple queries for example With <CTE> as (Select * from <TableA> where <Conditions> ) select * from <CTE> inner join <TableA> on <Indexed Condition> Join <Other Tables> to <TableA>. The filtered result of CTE should be joined again with actual table and that table should be used for further joins. Do not use CTE further except for the <TableA> join.

Shantae