This is a special case of relational-division. Here is an arsenal of query techniques:
The special difficulty of your case is to filter on the combination of two attributes, but the principle is the same.
You can make this fully dynamic with plain SQL, without string concatenation and dynamic SQL:
But performance won't come close to the following solution with dynamic SQL.
For best performance, have this (UNIQUE
) multicolumn index:
CREATE [UNIQUE] INDEX ON tags (tag_name, tag_value, game_id);
Maybe your PRIMARY KEY
on tags
already spans these columns. For best performance you need index columns in the demonstrated order. Create an additional index if the PK does not match or change the column order of the PK unless you need columns in a different order (too). Related:
The basic query technique I chose uses the pattern:
SELECT game_id
FROM tags t
WHERE (tag_name, tag_value) = ('Event', 'EUR-ASIA Rapid Match')
AND EXISTS (SELECT FROM tags WHERE game_id = t.game_id AND (tag_name, tag_value) = ('Round', '5'))
AND EXISTS (SELECT FROM tags WHERE game_id = t.game_id AND (tag_name, tag_value) = ('some_tag', 'some value'))
AND ...
This query is already optimized for performance.
Function
Using a user-defined row type as input like you have in your answer (optional, but convenient for the function design). I chose the name game_tag
because tag
felt too generic:
CREATE TYPE game_tag AS(
tag_name text
, tag_value text
);
Note the subtle differences in syntax for these two row values:
'(Event,"EUR-ASIA Rapid Match")'::game_tag
('Event', 'EUR-ASIA Rapid Match')
The first one is a string literal for the registered row type game_tag
, the second is a ROW
constructor on two string literals building an anonymous row, short for:
ROW('Event', 'EUR-ASIA Rapid Match')
Either works for our purpose and gets index support. Just don't confuse the different syntax requirements. Related:
The shortcuts for just 1 or 2 parameters are optional but should further improve performance.
CREATE OR REPLACE FUNCTION f_games_by_tags(VARIADIC _filters game_tag[])
RETURNS table (game_id int) AS
$func$
BEGIN
CASE cardinality(_filters)
-- WHEN 0 THEN -- impossible
WHEN 1 THEN
RETURN QUERY
SELECT t.game_id
FROM tags t
WHERE (tag_name, tag_value) = _filters[1];
WHEN 2 THEN
RETURN QUERY
SELECT t.game_id
FROM tags t
WHERE (tag_name, tag_value) = _filters[1]
AND EXISTS (
SELECT FROM tags t1
WHERE t1.game_id = t.game_id
AND (tag_name, tag_value) = _filters[2]
);
ELSE
RETURN QUERY EXECUTE
(SELECT 'SELECT game_id FROM tags t WHERE (tag_name, tag_value) = $1[1] AND '
|| string_agg('EXISTS (SELECT FROM tags WHERE game_id = t.game_id AND (tag_name, tag_value) = $1[' || g || '])', ' AND ')
FROM generate_series (2, cardinality(_filters)) g)
USING _filters;
END CASE;
END
$func$ LANGUAGE plpgsql;
db<>fiddle here
Should be faster by orders of magnitude than what you have in your answer.
Call:
SELECT * FROM f_games_by_tags('(Event,"EUR-ASIA Rapid Match")');
SELECT * FROM f_games_by_tags('(Round,5)', '(Event,"EUR-ASIA Rapid Match")', '(some_tag,"some value")');
You can also pass an actual array to a VARIADIC
function. Related: