Performance Considerations with Suggest Filters

  • 8 January 2015
  • 26 replies

When you open a filter in Looker (i.e. on a string field), Looker runs a simple SELECT DISTINCT <field name> FROM <table> LIMIT 1000 query to get the first 1000 distinct options as suggestions.

If that first query returns before the user starts / stops typing in the filter box, and there are fewer than 1000 results, Looker will make subsequent suggestions very quickly because it knows the universe of possibilities for that field.

If some other text is written in the filter box (e.g. “goodlooker”) and the user stops typing (for 300ms), any previous running suggestion query will get killed and a new one will run with a filter on it: WHERE <field> ilike 'goodlooker%'

If this is a concern because you are suggesting on large fact tables, or don’t want extra queries running on your database, you have a few options:

  1. Add suggestable: false in the LookML for a dimension to turn off suggestions for that field

  2. Manually list the suggestion options you want with suggestions: [foo, bar]

  3. Point to a different table for the suggestion lookup using suggest_explore: and suggest_dimension: in the dimension’s LookML

26 replies

We used the same PDT + suggest_explore approach to increase performance with loading filter suggestions, but this setup sort of messes up the linked filter feature on our dashboard. The child filter just loads up all suggestions from suggest_explore/dimension and does not inherit the filter value from the parent filter.  Any updates on a workaround?