Improve search function performance #54

New Issue

2023-06-02T15:20:22Z

2023-06-02 15:20:22 +00:00

Credit to boiii

SELECT releases.title, doc.release_id, ts_rank(doc.document::tsvector, to_tsquery('english', array_to_string(array(SELECT * FROM regexp_matches('alexis tea', '[A-Za-zÀ-ÖØ-öø-ÿ0-9]+', 'g')), '|'))) as rank 
FROM (
    SELECT releases_search.release_id, document
    FROM releases_search
    WHERE document::tsvector @@ to_tsquery('english', array_to_string(array(SELECT * FROM regexp_matches('alexis tea', '[A-Za-zÀ-ÖØ-öø-ÿ0-9]+', 'g')), '|'))
) AS doc
LEFT JOIN releases ON releases.id = doc.release_id
ORDER BY rank DESC;

Credit to boiii ``` SELECT releases.title, doc.release_id, ts_rank(doc.document::tsvector, to_tsquery('english', array_to_string(array(SELECT * FROM regexp_matches('alexis tea', '[A-Za-zÀ-ÖØ-öø-ÿ0-9]+', 'g')), '|'))) as rank FROM ( SELECT releases_search.release_id, document FROM releases_search WHERE document::tsvector @@ to_tsquery('english', array_to_string(array(SELECT * FROM regexp_matches('alexis tea', '[A-Za-zÀ-ÖØ-öø-ÿ0-9]+', 'g')), '|')) ) AS doc LEFT JOIN releases ON releases.id = doc.release_id ORDER BY rank DESC; ```

DebaucheryLibrarian added the enhancement label 2023-06-02 15:20:22 +00:00

DebaucheryLibrarian commented

2023-06-24 22:59:39 +00:00