Manage Indexes

[xrl]

Please Describe The Problem To Be Solved

We have large tables and materialized views and want to create indexes so we can efficient JOIN. I know DBT offers a proprietary {{ ... }} syntax at the top of the models for managing indexes, what is the solution for Quary?

Optional: Suggest A Solution

Quary could probably just support the same syntax as DBT?

[benfdking]

Hi @xrl appreciate the feedback, we'll have a look into it!

[xrl]

I think indexes are important for performance. What potential workarounds do you have for large table JOINs? Should I be managing indexes outside of Quary?

[benfdking]

Are these indexes on materialised/tables views you're creating?

[xrl]

I believe you can only put indexes on tables and materialized views, but yes. I want to add indexes to objects managed by our Quary model sync

[benfdking]

Hey, we'll probably implement this but unlike dbt, we would put it in the yaml file. In the meantime, if you do have performance issues, you could apply indexes manually by running a quick, let me get back to you on a timeline of us having this done.

psql -f file.sql

after a quary build step where file.sql looks something like

CREATE INDEX IF NOT EXISTS index_name
ON table_name (column1, column2, ...);
...

[benfdking]

https://github.com/quarylabs/quary/pull/433 This is a first draft I did, what do you think?

[benfdking]

This has been fully built and deployed. Here is an example of it being used. Let me know if you get the chance to try it. I will document it fully tomorrow.

sources:
  - name: raw_orders
    path: jaffle_shop.orders
models:
  - name: stg_orders
    materialization: table
    database_config: 
      unlogged: true
      indexes: 
        - columns: 
            - order_id
            - status
        - unique: true
          columns: 
            - order_id

    tests:
      - type: multi_column_unique
        info:
          columns: order_id,status
    columns:
      - name: order_id
        tests:
          - type: unique
          - type: not_null
          - type: gte
            info:
              column: order_id
              value: 1

Here it is also documented https://www.quary.dev/docs/configuration

[benfdking]

Hey, I am going to close this for the time being. Please feel free to reopen it if it doesn't fit your needs.