Performance of WHERE clause

Get Help
1

I have a question in a dashboard where there are multiple values wanted for one of the columns.

Metabase creates this as part of the where clause:

AND (
        (
          "bi"."careslot"."care_type" <> 'Care (Live in Client)'
        )
        OR ("bi"."careslot"."care_type" IS NULL)
      )
      AND (
        ("bi"."careslot"."care_type" <> 'Live In')
        OR ("bi"."careslot"."care_type" IS NULL)
      )
      AND (
        (
          "bi"."careslot"."care_type" <> 'Live in (Change Over)'
        )
        OR ("bi"."careslot"."care_type" IS NULL)
      )

There are many, many more like that. Itā€™s really inefficient as Postgres wonā€™t use any index. Even worse, that column canā€™t be null (this is a cleaned up materialised view).
Is there a way to stop this happening? Maybe in the options for the database connection?

2

So this is a multi-select filter with an Is Not operator, right?

Not sure there is any way this can be further optimized using the notebook editor. This type of condition works best paired with a sargeable inclusion predicate that gets used for the index lookup. If its a table scan with exclusions thereā€™s no way to optimize it.

On top of that matviews donā€™t support not-null constraints, otherwise the ā€˜is nullā€™ part of the condition can be optimized out.

Can you share more of the WHERE clause? Maybe we can see if another part can be enlisted to help.

3

Thereā€™s no ā€˜NOTā€™, this is the inclusion criteria.

Iā€™m asking the customer if there are ā€˜setsā€™ of these so I can just extend the MV or add a lookup.

Failing that, Iā€™ll have to create a view on top of the MV and move the caretype into a lookup table where I could define it as non-nullable.

Iā€™d not appreciated the problem with MVs not having the notion of nullable columns. I suppose I could do it more manually into a table but thereā€™s a lot of records and refreshed regularly.

Frustrating.

4

The predicates you pasted are not-equal comparisons (<>), thatā€™s where the ā€œNOTā€ Iā€™m referencing is coming from. The fragment you pasted is checking that care_type is not set to one of ā€œCare (Live in Client)ā€, ā€œLive Inā€, or ā€œLive in (Change Over)ā€.

That also doesnā€™t match with your first sentence in your first post, where you say you have ā€œmultiple values wanted.ā€

What you have here is multiple values NOT wanted.

At this point I donā€™t understand the problem youā€™re describing. Start at the beginning, show your inputs to Metabase, then show what output isnā€™t what you think it should be. If Metabase is generating the wrong query, thatā€™s a bug that can be fixed. If query performance is poor, we can look at the question and its query and figure out how to improve performance.

5

That was quick - sorry, I just checked and it is a ā€˜Is Notā€™ filter. I thought Iā€™d have time to edit my response after having a shower (it is 7am)! Itā€™s not one of my dashboards, so Iā€™m not completely up to speed with it.

There are about 30 care types of which 5 are unwanted in this query. Theyā€™re always adding additional care types which is why theyā€™re using a not filter.

I think I need to add an extra column with index that can be used to include the filter values. They all contain ā€˜Live Inā€™ so not a big deal to add.

Thanks for the help.

1 Like