这是您的查询：
SELECT "genomic_accession", "assembly", "product_accession", "tmpcol",
("product_accession" IN ( SELECT product_accession FROM "cacheDB" ) OR
"tmpcol" IN ( SELECT product_accession FROM "cacheDB")
) AS "CACHE",
("product_accession" IN ( SELECT product_accession FROM "SBPDB" ) OR
"tmpcol" IN ( SELECT product_accession FROM "SBPDB" ) AS "SBP"
FROM (SELECT * FROM "pairTable2" LIMIT 500000) "dbplyr_031";
我会删除所有的双引号。不要创建需要转义的列名和表名。然后，EXISTS使用正确的索引通常会更好地执行：
SELECT "genomic_accession", "assembly", "product_accession", "tmpcol",
(EXISTS (SELECT 1
FROM "cacheDB" c
*WHERE c.product_accession IN (pt.product_accession, pt.tmpcol )
)
) AS CACHE,
(EXISTS (SELECT 1
FROM "SBPDB" s
WHERE s.product_accession IN (pt.product_accession, pt.tmpcol )
)
) AS SBP
FROM (SELECT * FROM "pairTable2" LIMIT 500000) pt;
然后，为了提高性能，您需要在cachedb(product_accession)和上建立索引sbpdb(product_accession)。
602392714
清零编程群