Searching for Queries after upgrade to v5


#1

I’ve been using Redash for a little while and have never had a problem with the search functionality for queries. Having upgrade to v5 it’s absolutely and totally useless.

Previously it seems there was a simple implementation so that searches just used a “LIKE” on the name & description fields of the ‘queries’ table. Great.

Now it seems things have migrated to PostgreSQL full text search - which is being populated with the name, the id, the description and the query body. This makes search worse then useless for me; and I can’t believe I’m the only one in this situation; I have a whole load of queries that are named and titled based upon what they’re for which usually means listing what information they take as parameters to perform analysis - however these are fields in plenty of other queries, often multiple times.

Worse still, I have a bunch of queries that are used for populating query-based drop-down boxes - since they’re for selecting a constant they’re called:

Select: A Country
Select: A Product
Select: A Supplier
etc…

In older redash versions I’d have chosen a parameter to be a “Query Based Dropdown List” typed “Select” into the query name box and be given a list of the above queries… however in Redash v5 I type “Select” and can choose between ~25 recent unpublished queries none of which are what I’m looking for. Even if I type in the full name of the query I want “Select: A supplier” I get 25 unrelated queries and no way to pick what I want.

Has something gone wrong with my upgrade or is this now the intended behaviour?

At this rate I’m going to locally alter the schema of the redash Postgres DB so that the query body isn’t included in the full-text index.


#2

Looking a bit deeper here, there’s a bunch of issues:

  • The DB upgrade path leads to a different schema to the create_tables script. Databases that have been upgraded don’t have the ‘weights’ described in models.py (which make everything more important than the body of the query).

  • The weights are useless anyway as the search query sorts everything by “created_at desc” - rather than by how closely the entry matched the keyword. This remains a particular issue for the search box used to select a query to be used as a drop-down parameter.


#3

Thank you for the detailed report and the extra details. While we didn’t have many such reports, I myself encountered the exact issue you have and agree that it’s frustrating.

This is interesting. Can you elaborate on this?

This was actually fixed in: https://github.com/getredash/redash/pull/2798 and will be part of the next release.


#4

I’ve created an issue @ https://github.com/getredash/redash/issues/3028 with the details.

Thanks for the pointer on the other pull request - I’ll have a look there.


#5

Quick update from me - the changes to make better use of the full-text search (and order by relevance) are quite large; and I’ve wanted to deploy v5.0.2 to my organisation.

So instead I’ve simply executed:

CREATE OR REPLACE FUNCTION public.queries_search_vector_update() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
            BEGIN
                NEW.search_vector = ((setweight(to_tsvector('pg_catalog.simple', regexp_replace(coalesce(CAST(NEW.id AS TEXT), ''), '[-@.]', ' ', 'g')), 'B') || setweight(to_tsvector('pg_catalog.simple', regexp_replace(coalesce(NEW.name, ''), '[-@.]', ' ', 'g')), 'A')) || setweight(to_tsvector('pg_catalog.simple', regexp_replace(coalesce(NEW.description, ''), '[-@.]', ' ', 'g')), 'C'));
                RETURN NEW;
            END
            $$;
UPDATE queries set version=version;

to my local redash Postgres DB in order to enable the weighting features that the update process failed to put in place and to remove the contents of the queries from the full-text vectors. So search functionality is now similar to how it was prior to v5.

When v6 comes out hopefully the DB upgrade scripts will overwrite this function anyway (in order to address issue 3028 and then the Redash UI will make better use of the full-text index anyway.