sql - 复杂的反连接显然返回表 2 中不为空的行

Question

我有这些表......

CREATE TABLE segmented_text (
    id          SERIAL PRIMARY KEY,
    hash        BYTEA  NOT NULL UNIQUE,
    text        TEXT   NOT NULL,
    segmented   JSONB
);
CREATE TABLE translations (
    lang TEXT NOT NULL,
    word TEXT NOT NULL,
    engl TEXT NOT NULL,
    PRIMARY KEY (lang, word)
);

“分段”字段中的 JSON 结构始终采用以下格式:

[ { "l":"it", "t":["aforismi", "un", "gatto", "migliora", "il", "muro", ...] },
  { "l":"fr", "t":["beaujolais", "bordeaux", "borgogna", "champagne", ...] },
  ... ]

客户端应用程序正在尝试扫描 segmented_text 以查找每种语言中尚未在 translations 表中包含条目的每个单词。它将在外部语言到英语词典服务中查找这些单词，并将结果插入到 translations 表中，基本上如下 (Python/psycopg2):

cur = db.cursor()
# see below
cur.execute("[get all the untranslated words]", (can_translate,))

engl = collections.defaultdict(dict)
for lang, word in cur:
    if word not in english[lang]:
        english[lang][word] = get_translation(lang, word)

for lang, words in english.items():
    for word, engl in words.items():
        cur.execute("INSERT INTO translations VALUES (%s, %s, %s)",
                    (lang, word, engl))
db.commit()

我的问题与“[获取所有未翻译的词]”查询有关。我一直在测试两种变体。 (查询参数 can_translate 是词典服务提供的所有语言代码的数组。)

此版本具有可接受的性能，扫描整个 segmented_text 表大约需要 10 分钟，但它似乎不正确。

SELECT w.lang, w.word
FROM (
  SELECT DISTINCT 
     chunk->>'l' AS lang,
     jsonb_array_elements_text(chunk->'t') AS word
  FROM (
    SELECT jsonb_array_elements(segmented) AS chunk
    FROM segmented_text 
    WHERE segmented IS NOT NULL) _
  WHERE chunk->>'l' = ANY(%s)) w
LEFT JOIN translations t ON w.lang = t.lang AND w.word = t.word
WHERE t.word IS NULL;

问题在于，大约每 200 个后续 INSERT 中就有一个会引发约束冲突，声称翻译表中已经存在对 (lang, word)。应用程序注意不要多次插入任何这样的对，所以一定是这个查询中的反连接以某种方式没有过滤掉所有已经翻译的单词。

我得到的唯一其他线索是此调整消除了问题——唯一的区别是 DISTINCT 修饰符的位置。但是，这样做的代价是完全不能接受的性能；扫描表格需要六个多小时。

SELECT DISTINCT w.lang, w.word
FROM (
  SELECT 
     chunk->>'l' AS lang,
     jsonb_array_elements_text(chunk->'t') AS word
  FROM (
    SELECT jsonb_array_elements(segmented) AS chunk
    FROM segmented_text 
    WHERE segmented IS NOT NULL) _
  WHERE chunk->>'l' = ANY(%s)) w
LEFT JOIN translations t ON w.lang = t.lang AND w.word = t.word
WHERE t.word IS NULL;

当我进行小规模抽查时，两个查询似乎 都在做正确的事情，但全面的操作肯定以某种方式搞砸了。 (为了了解问题的规模，80 种语言中约有 200,000 个单词需要翻译，translations 表中已有约 11,000,000 个单词。)

我可以掩盖插入端的问题，但字典服务会按每个单词收费，所以我宁愿修复初始查询，使其可靠地只返回尚未翻译的单词。

Answer 1

您的连接逻辑似乎很合理(假设该词声明为非空)所以我唯一能看到的可能是连接逻辑失败。要在值实际存在时发生这种情况，您可能必须处理查询第一部分中数据的类型(或内容)。您正在那里做一些花哨的 json 内容，结果可能不“等于”翻译表中的文本值。然后，当您插入它们时，它们会被强制/转换为正确的类型(如果您尚未处理)并匹配现有值。

当有 CHAR 类型与 VARCHAR 类型连接时，我见过这种情况。您可能想检查 JSON 中这些违规词中常见的可疑字符，例如空格或不可打印字符。