我使用的是postgres 9.4
select version();
version
---------------------------------------------------------------------------------------------------------------
PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-11), 64-bit
我的数据库中有一个视图，它有两列，一列是整数，一列为文本。
d+ gff_attributes
+----------------+---------+-------------+-----------+---------------+
| Column | Type | Modifiers | Storage | Description |
|----------------+---------+-------------+-----------+---------------|
| seqfeature_id | integer | | plain | <null> |
| gff_attributes | text | | extended | <null> |
+----------------+---------+-------------+-----------+---------------+
View definition:
SELECT qv.seqfeature_id,
string_agg((t.name::text || '='::text) || qv.value, ';'::text
ORDER BY t.name) AS gff_attributes
FROM term t,
seqfeature_qualifier_value qv
WHERE qv.term_id = t.term_id
GROUP BY qv.seqfeature_id;
它组合了来自两个表seqfeature_qualifier_value（约55000000行）和term（约11000行）的数据
d+ seqfeature_qualifier_value
Table "public.seqfeature_qualifier_value"
Column | Type | Modifiers | Storage | Description
---------------+---------+--------------------+----------+-------------
seqfeature_id | integer | not null | plain |
term_id | integer | not null | plain |
rank | integer | not null default 0 | plain |
value | text | not null | extended |
Indexes:
"seqfeature_qualifier_value_pkey" PRIMARY KEY, btree (seqfeature_id, term_id, rank)
"seqfeaturequal_sfid" btree (seqfeature_id)
"seqfeaturequal_trm" btree (term_id)
"seqfeaturequal_type_value" btree (term_id, value)
Foreign-key constraints:
"fkseqfeature_featqual" FOREIGN KEY (seqfeature_id) REFERENCES seqfeature(seqfeature_id) ON DELETE CASCADE
"fkterm_featqual" FOREIGN KEY (term_id) REFERENCES term(term_id)
Rules:
rule_seqfeature_qualifier_value_i AS
ON INSERT TO seqfeature_qualifier_value
WHERE (( SELECT seqfeature_qualifier_value.seqfeature_id
FROM seqfeature_qualifier_value
WHERE seqfeature_qualifier_value.seqfeature_id = new.seqfeature_id AND seqfeature_qualifier_value.term_id = new.term_id AND seqfeature_qualifier_value.rank = new.rank)) IS NOT NULL DO INSTEAD NOTHING
Has OIDs: no
d+ term
Table "public.term"
Column | Type | Modifiers | Storage | Description
-------------+------------------------+---------------------------------------------------+----------+-------------
term_id | integer | not null default nextval('term_pk_seq'::regclass) | plain |
name | character varying(255) | not null | extended |
definition | text | | extended |
identifier | character varying(40) | | extended |
is_obsolete | character(1) | | extended |
ontology_id | integer | not null | plain |
Indexes:
"term_pkey" PRIMARY KEY, btree (term_id)
"term_identifier_key" UNIQUE, btree (identifier)
"term_name_ontology_id_is_obsolete_key" UNIQUE, btree (name, ontology_id, is_obsolete)
"term_ont" btree (ontology_id)
Foreign-key constraints:
"fkont_term" FOREIGN KEY (ontology_id) REFERENCES ontology(ontology_id) ON DELETE CASCADE
Rules:
rule_term_i1 AS
ON INSERT TO term
WHERE (( SELECT term.term_id
FROM term
WHERE term.identifier::text = new.identifier::text)) IS NOT NULL DO INSTEAD NOTHING
rule_term_i2 AS
ON INSERT TO term
WHERE (( SELECT term.term_id
FROM term
WHERE term.name::text = new.name::text AND term.ontology_id = new.ontology_id AND term.is_obsolete = new.is_obsolete)) IS NOT NULL DO INSTEAD NOTHING
Has OIDs: no
现在，如果我想根据seqfeature_id列选择行的子集，我可以使用显式比较很快得到结果：
explain (analyze, verbose) select *
from gff_attributes
where seqfeature_id = 3596159;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| GroupAggregate (cost=337.27..734.68 rows=1 width=24) (actual time=11.690..11.690 rows=1 loops=1) |
| Output: qv.seqfeature_id, string_agg((((t.name)::text || '='::text) || qv.value), ';'::text ORDER BY t.name) |
| Group Key: qv.seqfeature_id |
| -> Hash Join (cost=337.27..733.56 rows=110 width=24) (actual time=11.600..11.628 rows=6 loops=1) |
| Output: t.name, qv.seqfeature_id, qv.value |
| Hash Cond: (qv.term_id = t.term_id) |
| -> Index Scan using seqfeaturequal_sfid on public.seqfeature_qualifier_value qv (cost=0.56..394.66 rows=110 width=17) (actual time=0.036..0.055 rows=6 loops=1) |
| Output: qv.seqfeature_id, qv.term_id, qv.rank, qv.value |
| Index Cond: (qv.seqfeature_id = 3596159) |
| -> Hash (cost=194.09..194.09 rows=11409 width=15) (actual time=11.539..11.539 rows=11413 loops=1) |
| Output: t.name, t.term_id |
| Buckets: 2048 Batches: 1 Memory Usage: 540kB |
| -> Seq Scan on public.term t (cost=0.00..194.09 rows=11409 width=15) (actual time=0.009..5.108 rows=11413 loops=1) |
| Output: t.name, t.term_id |
| Planning time: 0.455 ms |
| Execution time: 11.753 ms |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
但是，当它与使用IN运算符返回许多seqfeature_id的查询相结合时，的速度会显著减慢（~2min）
explain (analyse, verbose)
select * from gff_attributes
where seqfeature_id in (
select seqfeature_id
from seqfeature_qualifier_value
where term_id = (select term_id
from term
where name = 'SRB_ortholog_id')
and value = '1')
;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Merge Join (cost=12911531.62..13619325.85 rows=251228 width=36) (actual time=121504.409..173449.696 rows=102 loops=1) |
| Output: qv.seqfeature_id, (string_agg((((t.name)::text || '='::text) || qv.value), ';'::text ORDER BY t.name)) |
| Merge Cond: (qv.seqfeature_id = seqfeature_qualifier_value.seqfeature_id) |
| InitPlan 1 (returns $0) |
| -> Index Scan using term_name_ontology_id_is_obsolete_key on public.term (cost=0.29..8.30 rows=1 width=4) (actual time=0.036..0.037 rows=1 loops=1) |
| Output: term.term_id |
| Index Cond: ((term.name)::text = 'SRB_ortholog_id'::text) |
| -> GroupAggregate (cost=12905524.15..13607037.46 rows=502457 width=24) (actual time=121295.372..172418.928 rows=3687424 loops=1) |
| Output: qv.seqfeature_id, string_agg((((t.name)::text || '='::text) || qv.value), ';'::text ORDER BY t.name) |
| Group Key: qv.seqfeature_id |
| -> Sort (cost=12905524.15..13044570.67 rows=55618608 width=24) (actual time=121295.315..132671.659 rows=22189814 loops=1) |
| Output: qv.seqfeature_id, t.name, qv.value |
| Sort Key: qv.seqfeature_id |
| Sort Method: external merge Disk: 1639072kB |
| -> Hash Join (cost=336.70..2328594.94 rows=55618608 width=24) (actual time=13.358..41289.820 rows=55545757 loops=1) |
| Output: qv.seqfeature_id, t.name, qv.value |
| Hash Cond: (qv.term_id = t.term_id) |
| -> Seq Scan on public.seqfeature_qualifier_value qv (cost=0.00..1215886.08 rows=55618608 width=17) (actual time=0.063..12230.988 rows=55545757 loops=1) |
| Output: qv.seqfeature_id, qv.term_id, qv.rank, qv.value |
| -> Hash (cost=194.09..194.09 rows=11409 width=15) (actual time=13.278..13.278 rows=11413 loops=1) |
| Output: t.name, t.term_id |
| Buckets: 2048 Batches: 1 Memory Usage: 540kB |
| -> Seq Scan on public.term t (cost=0.00..194.09 rows=11409 width=15) (actual time=0.011..6.207 rows=11413 loops=1) |
| Output: t.name, t.term_id |
| -> Sort (cost=5999.16..5999.20 rows=14 width=4) (actual time=0.404..0.436 rows=102 loops=1) |
| Output: seqfeature_qualifier_value.seqfeature_id |
| Sort Key: seqfeature_qualifier_value.seqfeature_id |
| Sort Method: quicksort Memory: 29kB |
| -> HashAggregate (cost=5998.76..5998.90 rows=14 width=4) (actual time=0.345..0.368 rows=102 loops=1) |
| Output: seqfeature_qualifier_value.seqfeature_id |
| Group Key: seqfeature_qualifier_value.seqfeature_id |
| -> Bitmap Heap Scan on public.seqfeature_qualifier_value (cost=88.22..5994.94 rows=1527 width=4) (actual time=0.102..0.290 rows=102 loops=1) |
| Output: seqfeature_qualifier_value.seqfeature_id, seqfeature_qualifier_value.term_id, seqfeature_qualifier_value.rank, seqfeature_qualifier_value.value |
| Recheck Cond: ((seqfeature_qualifier_value.term_id = $0) AND (seqfeature_qualifier_value.value = '1'::text)) |
| Heap Blocks: exact=102 |
| -> Bitmap Index Scan on seqfeaturequal_type_value (cost=0.00..87.83 rows=1527 width=0) (actual time=0.083..0.083 rows=102 loops=1) |
| Index Cond: ((seqfeature_qualifier_value.term_id = $0) AND (seqfeature_qualifier_value.value = '1'::text)) |
| Planning time: 1.010 ms |
| Execution time: 173942.270 ms |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
注意，当单独运行子查询时，它也很快（<1s）并返回102行
explain (analyse, verbose)
select seqfeature_id
from seqfeature_qualifier_value
where term_id = (select term_id
from term where name = 'SRB_ortholog_id'
)
and value = '1'
;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| QUERY PLAN |
|-----------------------------------------------------------------------------------------------------------------------------------------------------------|
| Bitmap Heap Scan on public.seqfeature_qualifier_value (cost=96.52..6003.24 rows=1527 width=4) (actual time=0.104..0.319 rows=102 loops=1) |
| Output: seqfeature_qualifier_value.seqfeature_id |
| Recheck Cond: ((seqfeature_qualifier_value.term_id = $0) AND (seqfeature_qualifier_value.value = '1'::text)) |
| Heap Blocks: exact=102 |
| InitPlan 1 (returns $0) |
| -> Index Scan using term_name_ontology_id_is_obsolete_key on public.term (cost=0.29..8.30 rows=1 width=4) (actual time=0.035..0.037 rows=1 loops=1) |
| Output: term.term_id |
| Index Cond: ((term.name)::text = 'SRB_ortholog_id'::text) |
| -> Bitmap Index Scan on seqfeaturequal_type_value (cost=0.00..87.83 rows=1527 width=0) (actual time=0.083..0.083 rows=102 loops=1) |
| Index Cond: ((seqfeature_qualifier_value.term_id = $0) AND (seqfeature_qualifier_value.value = '1'::text)) |
| Planning time: 0.215 ms |
| Execution time: 0.368 ms |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
我很困惑，为什么IN运算符会给查询增加这么多时间？有没有一种方法可以重写这个查询以提高性能？
类似的东西？
SELECT
t1.*
FROM
gff_attributes t1
INNER JOIN
(
SELECT DISTINCT t3.seqfeature_id
FROM seqfeature_qualifier_value t3
INNER JOIN term t4 on t3.term_id = t4.term_id AND t4.name = 'SRB_ortholog_id'
WHERE
t3.value = '1'
) t2 ON t1.seqfeature_id = t2.seqfeature_id
怎么样：
select a.*
from
gff_attributes as a
join
seqfeature_qualifier_value as b on
a.seqfeature_id = b.seqfeature_id
and
b.value = '1'
join
term as c on
b.term_id = c.term_id
and
c.name = 'SRB_ortholog_id';
通常，无论使用IN、JOIN还是WHERE EXISTS，嵌套查询/子查询都很昂贵。我在Transact-SQL中尝试过每种方法，发现每种方法都有完全相同的执行计划，所以它们在性能方面是相等的，至少在T-SQL方面是这样。
一个标准的解决方法是将您的第一个查询拉入Andy提到的临时表中，向其中添加索引（使用ALTERTABLE），然后针对索引的临时表运行子查询。这将在大多数SQL中运行得更快。如果你想更深入地挖掘，谷歌"postgresql子查询的性能问题"。你会发现很多帖子试图处理同样的问题。
第一：用EXISTS子句替换IN+标量子查询（yuk！）（并添加一些别名以保持心智健全）：
SELECT *
FROM gff_attributes ga
WHERE EXISTS ( SELECT 13
FROM seqfeature_qualifier_value sqv
JOIN term t ON t.term_id = sqv.term_id
WHERE ga.seqfeature_id = sqv.seqfeature_id
AND sqv.value = '1'
AND t.name = 'SRB_ortholog_id'
);
下一步：在脂肪连接表（或：值表格）中，我建议用一个复合索引替换术语和特征的两个单列索引。这实际上是按相反顺序排列的主键。（BTW确实需要rank字段来强制执行唯一性吗？这意味着什么？）
DROP INDEX seqfeaturequal_sfid; -- (seqfeature_id)
DROP INDEX seqfeaturequal_trm; -- (term_id)
-- WHAT is "rank" ? Why is it needed?
CREATE UNIQUE INDEX seqfeaturequal_trm_sfid
ON seqfeature_qualifier_value (term_id,seqfeature_id,rank);
当然，您还应该在添加索引后运行ANALYZE seqfeature_qualifier_value;，以刷新统计信息。
并且：您可能应该在term.name上添加一个UNIQUE约束；uou在标量子查询中使用它，所以您假设它是唯一的。