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.

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.

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: Fix #2757 - Use full text search ranking when searching in list views. by jezdez · Pull Request #2798 · getredash/redash · GitHub and will be part of the next release.

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.

1 Like

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.

This still seems to be an issue - having upgraded from v5 to v7 and removed the change to the queries_search_vector_update() function, I’m still finding that the results are just ordered by descending creation date rather than by the best match.

This makes the ‘Query Based Dropdown List’ query selection unusable for me :frowning:

I’ll have a dig as to whether this didn’t get fixed by pull 2798 or whether it was subsequently broken again.

I’m rather confused by this and why it’s not something being reported by more people…

@arikfr Arik: Shall I just push a merge request with a fix? Did this ever work?? Seems there’s some good logic in there with the intention of only using the search result weight for ordering when you’ve actually got a search term - but the logic is inverted! So if you’ve got a search term then it won’t use the weight. (!)

Created: https://github.com/getredash/redash/pull/3706

That’s a very good question :man_shrugging:

Thanks!