이 쿼리는 제 설정에서 문제를 발견하는 데 도움이 되었습니다. 꽤 오랜 시간 동안 사용하지 않아서 개선할 부분이 있었는지 기억이 나지 않습니다.
태그나 태그 그룹으로 필터링할 수 있습니다
-- [params]
-- null string :tag_name
-- null string :tag_group
WITH allowed_tags AS (
SELECT ct.category_id, t.id AS tag_id
FROM category_tags ct
JOIN tags t ON t.id = ct.tag_id
UNION
SELECT ctg.category_id, tgm.tag_id
FROM category_tag_groups ctg
JOIN tag_group_memberships tgm ON tgm.tag_group_id = ctg.tag_group_id
),
restricted_categories AS (
SELECT c.id, c.name
FROM categories c
WHERE c.allow_global_tags = FALSE
AND (
EXISTS (SELECT 1 FROM category_tags ct WHERE ct.category_id = c.id)
OR EXISTS (SELECT 1 FROM category_tag_groups ctg WHERE ctg.category_id = c.id)
)
),
all_tag_category_combinations AS (
SELECT t.id AS tag_id, t.name AS tag_name, rc.id AS category_id, rc.name AS category_name
FROM tags t
CROSS JOIN restricted_categories rc
WHERE t.target_tag_id IS NULL
),
not_allowed_tags AS (
SELECT atcc.tag_id, atcc.category_id, atcc.category_name
FROM all_tag_category_combinations atcc
LEFT JOIN allowed_tags al
ON al.tag_id = atcc.tag_id AND al.category_id = atcc.category_id
WHERE al.tag_id IS NULL
)
SELECT
main.id AS tag_id,
main.name AS tag_name,
COALESCE(STRING_AGG(DISTINCT syn.name, ', '), '') AS synonyms,
tg.id AS tag_group_id,
COALESCE(parent_tag.name, '') AS parent_tag_name,
CASE WHEN tg.one_per_topic THEN 'true' ELSE '' END AS one_tag_per_topic,
COALESCE(
STRING_AGG(
DISTINCT
CASE
WHEN crtg.min_count IS NOT NULL THEN crtg_cat.name || ' (' || crtg.min_count || ')'
ELSE NULL
END,
', '
),
''
) AS required_in_categories_with_min,
COALESCE(STRING_AGG(DISTINCT ctg_cat.name, ', '), '') AS tag_group_limited_to_category,
COALESCE(STRING_AGG(DISTINCT ctags_cat.name, ', '), '') AS tag_limited_to_category,
COALESCE(STRING_AGG(DISTINCT nat.category_name, ', '), '') AS not_allowed_in_categories,
CASE
WHEN COUNT(DISTINCT g.name) > 0 THEN COALESCE(STRING_AGG(DISTINCT g_use.name, ', '), '')
ELSE ''
END AS use_limited_to,
COALESCE(STRING_AGG(DISTINCT g.name, ', '), '') AS view_only_groups
FROM tags AS main
LEFT JOIN tags AS syn ON syn.target_tag_id = main.id
LEFT JOIN tag_group_memberships AS tgm ON tgm.tag_id = main.id
LEFT JOIN tag_groups AS tg ON tg.id = tgm.tag_group_id
LEFT JOIN tags AS parent_tag ON parent_tag.id = tg.parent_tag_id
LEFT JOIN tag_group_permissions tgp ON tgp.tag_group_id = tg.id AND tgp.permission_type = 3
LEFT JOIN groups g ON g.id = tgp.group_id
LEFT JOIN category_required_tag_groups AS crtg ON crtg.tag_group_id = tg.id
LEFT JOIN categories AS crtg_cat ON crtg.category_id = crtg_cat.id
LEFT JOIN category_tag_groups AS ctg ON ctg.tag_group_id = tg.id
LEFT JOIN categories AS ctg_cat ON ctg.category_id = ctg_cat.id
LEFT JOIN category_tags AS ctags ON ctags.tag_id = main.id
LEFT JOIN categories AS ctags_cat ON ctags.category_id = ctags_cat.id
LEFT JOIN not_allowed_tags AS nat ON nat.tag_id = main.id
LEFT JOIN tag_group_permissions tgp_use ON tgp_use.tag_group_id = tg.id AND tgp_use.permission_type = 1
LEFT JOIN groups g_use ON g_use.id = tgp_use.group_id
WHERE
main.target_tag_id IS NULL AND
(
:tag_name IS NULL OR
LOWER(main.name) LIKE LOWER('%' || :tag_name || '%') OR
LOWER(syn.name) LIKE LOWER('%' || :tag_name || '%')
) AND
(
:tag_group IS NULL OR
LOWER(tg.name) LIKE LOWER('%' || :tag_group || '%')
)
GROUP BY
main.id, main.name, tg.id, tg.name, parent_tag.name, tg.one_per_topic
ORDER BY main.name, tag_group_id
for non-staff) it should show you some more information that may be able to help track any extra limitations you’ve placed on it. Eg 
), but Moin gave a thorough troubleshooting post for a similar thing here:
