sql - 加速 PostgreSQL 查询(检查条目是否存在于另一个表中)
问题描述
我需要一些帮助来提高查询速度。
我有 3 个表:
1-pairTable2 一个 4 列表:
-基因组访问:分组列(不关心这个问题)
-组装:分组列(不关心这个问题)
-product_accession:用于在其他中搜索的列表
- tmpcol:用于在其他表中搜索的列
2- SBPDB 一个 1 列表:
- product_accession:用于在其他表中搜索的列
3- cacheDB 一个 1 列表:- product_accession:用于在其他表中搜索的列
这个想法是在表 1 中创建一个名为 SBP 的布尔列,TRUE
如果列上的值product_accession
和/或tmpcol
在唯一列中SBPDB
;
并且,在表 1 中创建一个名为 SBP 的布尔列,TRUE
如果列上的值product_accession
和/或tmpcol
在cacheDB
.
我将 R 与DBI
anddplyr
作为后端一起使用,那么查询可能看起来很奇怪。但是,我想要做的查询是:
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";
(检查解释)
QUERY PLAN
----------------------------------------------------------------------------------------
Subquery Scan on dbplyr_031 (cost=3242.27..3846856408.45 rows=500000 width=59)
-> Limit (cost=0.00..10666.17 rows=500000 width=57)
-> Seq Scan on "pairTable2" (cost=0.00..781515.16 rows=36635216 width=57)
SubPlan 1
-> Seq Scan on "cacheDB" (cost=0.00..1394.91 rows=90491 width=14)
SubPlan 2
-> Seq Scan on "cacheDB" "cacheDB_1" (cost=0.00..1394.91 rows=90491 width=14)
SubPlan 3
-> Materialize (cost=0.00..7001.57 rows=276838 width=14)
-> Seq Scan on "SBPDB" (cost=0.00..4265.38 rows=276838 width=14)
SubPlan 4
-> Materialize (cost=0.00..7001.57 rows=276838 width=14)
-> Seq Scan on "SBPDB" "SBPDB_1" (cost=0.00..4265.38 rows=276838 width=14)
(13 rows)
因此,这只是 500k 行的样本,并且在 1 小时后仍在运行。总行数为:
genomes=> select count(*) from "pairTable2";
count
----------
36633962
(1 row)
我至少需要一些建议来找出更好的查询来加快我的需求。
表格示例:
(1)
genomic_accession | assembly | product_accession | tmpcol
-------------------+-----------------+-------------------+----------------
NC_007777.1 | GCF_000013345.1 | WP_011437108.1 | WP_011437109.1
NC_007777.1 | GCF_000013345.1 | WP_011437109.1 | WP_011437110.1
NC_007777.1 | GCF_000013345.1 | WP_011437110.1 | WP_011437113.1
NC_007777.1 | GCF_000013345.1 | WP_011437113.1 | WP_011437114.1
NC_007777.1 | GCF_000013345.1 | WP_011437114.1 | WP_011437116.1
NC_007777.1 | GCF_000013345.1 | WP_011437116.1 | WP_011437117.1
NC_007777.1 | GCF_000013345.1 | WP_011437117.1 | WP_011437118.1
NC_007777.1 | GCF_000013345.1 | WP_011437118.1 | WP_011437120.1
NC_007777.1 | GCF_000013345.1 | WP_011437120.1 | WP_011437121.1
NC_007777.1 | GCF_000013345.1 | WP_011437121.1 | WP_011437123.1
(10 rows)
(2)
product_accession
-------------------
WP_005887071.1
WP_005913801.1
WP_002804432.1
WP_010366489.1
WP_012444785.1
NP_636898.1
WP_046342269.1
WP_074057745.1
WP_039420813.1
WP_005932253.1
(10 rows)
(3)
product_accession
-------------------
ABG85315.1
ABG85570.1
ABG86033.1
ABG86301.1
ABG87594.1
ACX82524.1
ACX83274.1
ACX83416.1
ADX79866.1
ADX79880.1
(10 rows)
输出示例:
genomic_accession | assembly | product_accession | tmpcol | CACHE | SBP
-------------------+-----------------+-------------------+----------------+-------+-----
NC_007899.1 | GCF_000009945.1 | WP_011457581.1 | WP_011457582.1 | f | f
NC_007899.1 | GCF_000009945.1 | WP_011457582.1 | WP_011457583.1 | f | f
NC_007899.1 | GCF_000009945.1 | WP_011457583.1 | WP_011457584.1 | f | f
NC_007899.1 | GCF_000009945.1 | WP_011457584.1 | WP_011457585.1 | f | f
NC_007899.1 | GCF_000009945.1 | WP_011457585.1 | WP_011457586.1 | f | f
NC_007899.1 | GCF_000009945.1 | WP_011457586.1 | WP_011457587.1 | f | f
NC_007899.1 | GCF_000009945.1 | WP_011457587.1 | WP_011457588.1 | f | f
NC_007899.1 | GCF_000009945.1 | WP_011457588.1 | WP_011457589.1 | f | f
NC_007899.1 | GCF_000009945.1 | WP_011457589.1 | WP_011457590.1 | f | f
NC_007899.1 | GCF_000009945.1 | WP_011457590.1 | WP_011457592.1 | f | f
NC_007899.1 | GCF_000009945.1 | WP_011457592.1 | WP_011457593.1 | f | f
NC_007899.1 | GCF_000009945.1 | WP_011457593.1 | WP_011457594.1 | f | f
NC_007899.1 | GCF_000009945.1 | WP_011457594.1 | WP_011457596.1 | f | f
NC_007899.1 | GCF_000009945.1 | WP_011457596.1 | WP_011457597.1 | f | f
NC_007899.1 | GCF_000009945.1 | WP_011457597.1 | WP_011457598.1 | f | f
NC_007899.1 | GCF_000009945.1 | WP_011457598.1 | WP_011457600.1 | f | f
NC_007899.1 | GCF_000009945.1 | WP_011457600.1 | WP_011457601.1 | f | f
NC_007899.1 | GCF_000009945.1 | WP_011457601.1 | WP_011457602.1 | f | f
NC_007899.1 | GCF_000009945.1 | WP_011457602.1 | WP_011457603.1 | f | f
NC_007899.1 | GCF_000009945.1 | WP_011457603.1 | WP_011457604.1 | f | f
提前致谢
解决方案
这是您的查询:
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)
。
推荐阅读
- cassandra - 时间序列数据的 Cassandra 数据模型
- java - 如何使用 Apache pdfbox 2.0.8 在 Java Swing 框架中可视化 pdf,但不将其转换为图像
- google-apps-script - 如何使用行、列表示法将值放入数组中
- java - 将 HashMap 转换为 JSONArray,值不正确
- wordpress - WordPress 特色图片未显示在 Windows PC 的 Chrome 浏览器上
- python - 如何防止python中的字符引用?
- c# - 如何禁用 DataGrid 中的某些单元格?
- php - 在PHPExcel中按数字设置列值
- c# - 如何从我调用的方法中获取值?C#
- npm - 如何为 node-red 安装 node-red-admin?