PostgreSQL benchmark: eval plainto_tsquery 6 times or eval it once and cache by WITH statement

README.md

###Results for real full-text search query

See tsquery_staight_eval.sql and tsquery_with_cache.sql. Cache by WITH statement is 1.2-1.6 times faster than straight plainto_tsquery eval with simple text query.

tsquery_selects-only.md

####About Sintetic example. Maybe using UNION here is a stupid idea.

####Using WITH

Planning time: ~0.242 ms
Execution time: ~0.241 ms

explain(analyze, buffers)
WITH cache AS (SELECT plainto_tsquery(COALESCE('english', 'simple')::regconfig, 'Sample text text textttt') AS tsquery)
SELECT tsquery FROM cache
UNION
SELECT tsquery FROM cache
UNION
SELECT tsquery FROM cache
UNION
SELECT tsquery FROM cache
UNION
SELECT tsquery FROM cache
UNION
SELECT tsquery FROM cache;

####Without WITH

Planning time: ~0.286 ms
Execution time: ~0.565 ms

explain(analyze, buffers)
SELECT plainto_tsquery(COALESCE('english', 'simple')::regconfig, 'Sample text text textttt')
UNION
SELECT plainto_tsquery(COALESCE('english', 'simple')::regconfig, 'Sample text text textttt')
UNION
SELECT plainto_tsquery(COALESCE('english', 'simple')::regconfig, 'Sample text text textttt')
UNION
SELECT plainto_tsquery(COALESCE('english', 'simple')::regconfig, 'Sample text text textttt')
UNION
SELECT plainto_tsquery(COALESCE('english', 'simple')::regconfig, 'Sample text text textttt')
UNION
SELECT plainto_tsquery(COALESCE('english', 'simple')::regconfig, 'Sample text text textttt');

tsquery_staight_eval.sql

explain(analyze, buffers)
SELECT id, lang1, lang2,
    CASE lang1
        WHEN 'en' THEN ts_headline(COALESCE('english', 'simple')::regconfig, text1, plainto_tsquery(COALESCE('english', 'simple')::regconfig, 'And this one too, of course.'), 'StartSel = <***, StopSel = ***>')
        WHEN 'et' THEN text1
    END AS text1,
    CASE lang1
        WHEN 'en' THEN text2
        WHEN 'et' THEN ts_headline(COALESCE('english', 'simple')::regconfig, text2, plainto_tsquery(COALESCE('english', 'simple')::regconfig, 'And this one too, of course.'), 'StartSel = <***, StopSel = ***>')
    END AS text2,
    topic, tm_id
FROM (
    SELECT "tus".id, lang1, lang2, text1, text2, topic, tm_id,
        CASE "tus".lang1
            WHEN 'en' THEN ts_rank_cd(text1_tsvector, plainto_tsquery(COALESCE('english', 'simple')::regconfig, 'And this one too, of course.'))
            WHEN 'et' THEN ts_rank_cd(text2_tsvector, plainto_tsquery(COALESCE('english', 'simple')::regconfig, 'And this one too, of course.'))
        END AS rank
    FROM "tus"
    WHERE ("tus".lang1 = 'en' AND "tus".lang2 = 'et' AND text1_tsvector @@ plainto_tsquery(COALESCE('english', 'simple')::regconfig, 'And this one too, of course.') AND length(text1) < length('And this one too, of course.') + 50)
    OR ("tus".lang1 = 'et' AND "tus".lang2 = 'en' AND text2_tsvector @@ plainto_tsquery(COALESCE('english', 'simple')::regconfig, 'And this one too, of course.') AND length(text2) < length('And this one too, of course.') + 50)
    ORDER BY rank DESC
    LIMIT 10 OFFSET 0
) AS foo;

tsquery_with_cache.sql

explain(analyze, buffers)
WITH cache AS (SELECT plainto_tsquery(COALESCE('english', 'simple')::regconfig, 'And this one too, of course.') AS tsquery)
SELECT id, lang1, lang2,
    CASE lang1
        WHEN 'en' THEN ts_headline(COALESCE('english', 'simple')::regconfig, text1, (SELECT tsquery FROM cache), 'StartSel = <***, StopSel = ***>')
        WHEN 'et' THEN text1
    END AS text1,
    CASE lang1
        WHEN 'en' THEN text2
        WHEN 'et' THEN ts_headline(COALESCE('english', 'simple')::regconfig, text2, (SELECT tsquery FROM cache), 'StartSel = <***, StopSel = ***>')
    END AS text2,
    topic, tm_id
FROM (
    SELECT "tus".id, lang1, lang2, text1, text2, topic, tm_id,
        CASE "tus".lang1
            WHEN 'en' THEN ts_rank_cd(text1_tsvector, (SELECT tsquery FROM cache))
            WHEN 'et' THEN ts_rank_cd(text2_tsvector, (SELECT tsquery FROM cache))
        END AS rank
    FROM "tus"
    WHERE ("tus".lang1 = 'en' AND "tus".lang2 = 'et' AND text1_tsvector @@ (SELECT tsquery FROM cache) AND length(text1) < length('And this one too, of course.') + 50)
    OR ("tus".lang1 = 'et' AND "tus".lang2 = 'en' AND text2_tsvector @@ (SELECT tsquery FROM cache) AND length(text2) < length('And this one too, of course.') + 50)
    ORDER BY rank DESC
    LIMIT 10 OFFSET 0
) AS foo;