The problem I’m having
Hello everyone,
I am no expert in dbt nor PostgreSQL so my apologies if my question is trivial.
I would like to build indexes on materialized tables at creation time so that the child tables in the DAG can benefit from the indexing during their own creation. But in practice that seems to be impossible with dbt that apparently builds all the tables and then adds the indexes.
The context of why I’m trying to do this
I’m having performance troubles trying to set up a dbt pipeline that processes geospatial data (more specifically polygons). I would like for each materialized table to have its own geospatial index in order to accelerate downstream model builds.
What I’ve already tried
I have tried using a post-hook that creates the index, as well as the indexes keyword in the YAML model config file. While both work and create the index required to accelerate later user queries, the index does not appear to be available for building downstream models in the DAG.
It is possible to leverage the sources’ existing index by using only views in the pipeline. But the pipeline build and downstream query runtime become prohibitive.
What I found in the docs is that " If one or more indexes are configured on a resource, dbt will run create index DDL statement(s) as part of that resource’s materialization, within the same transaction as its main create statement." So my understanding is that the indexes keyword may accomplish exactly what I need, unless the SELECT statement of a child table is ran before the CREATE INDEX statement of the parent table.
Thank you in advance for your help!