还有EXISTS
函数:
SELECT count(*) AS post_ct
FROM posts p
WHERE EXISTS (SELECT FROM votes v WHERE v.post_id = p.id);
在Postgres中,如果你的n端有多个条目,通常比count(DISTINCT post_id)
更快:
SELECT count(DISTINCT p.id) AS post_ct
FROM posts p
JOIN votes v ON v.post_id = p.id;
votes
中每个帖子的行数越多,性能差异就越大。使用
EXPLAIN ANALYZE
进行测试。
count(DISTINCT post_id)
必须读取
所有 的行,对它们进行排序或哈希,然后仅考虑相同集合中的第一个。而
EXISTS
只会扫描
votes
(或最好是在
post_id
上建立的索引),直到找到第一个匹配项。
如果 在表
posts
中保证存在每个
votes
中的
post_id
(通过外键约束执行参照完整性),则这个
简短形式 等效于较长的形式:
SELECT count(DISTINCT post_id) AS post_ct
FROM votes;
对于每个帖子不存在或仅有少量条目的情况,此方法可能实际上比没有EXISTS
查询更快。
你提供的查询也可以以更简单的形式工作:
SELECT count(*) AS post_ct
FROM (
SELECT FROM posts
JOIN votes ON votes.post_id = posts.id
GROUP BY posts.id
) sub;
基准测试
为了验证我的说法,我在资源有限的测试服务器上运行了一个基准测试。所有测试都在单独的模式下进行:
测试设置
模拟典型的帖子/投票情况:
CREATE SCHEMA y;
SET search_path = y;
CREATE TABLE posts (
id int PRIMARY KEY
, post text
);
INSERT INTO posts
SELECT g, repeat(chr(g%100 + 32), (random()* 500)::int)
FROM generate_series(1,10000) g;
DELETE FROM posts WHERE random() > 0.9;
CREATE TABLE votes (
vote_id serial PRIMARY KEY
, post_id int REFERENCES posts(id)
, up_down bool
);
INSERT INTO votes (post_id, up_down)
SELECT g.*
FROM (
SELECT ((random()* 21)^3)::int + 1111 AS post_id
, random()::int::bool AS up_down
FROM generate_series(1,70000)
) g
JOIN posts p ON p.id = g.post_id;
以下所有查询返回的结果相同(9107个帖子中有8093个帖子被投票)。
我在Postgres 9.1.4上对每个查询都运行了4次EXPLAIN ANALYZE
,并从中选择了最好的一次,并附加了结果的总运行时间。
原样。
在 ...后
ANALYZE posts;
ANALYZE votes;
之后,您可以通过调用submitForm()
函数来提交表单。
CREATE INDEX foo on votes(post_id);
在...
VACUUM FULL ANALYZE posts;
CLUSTER votes using foo;
count(*) ... WHERE EXISTS
- 253 ms
- 220 ms
- 85 ms -- winner (seq scan on posts, index scan on votes, nested loop)
- 85 ms
count(DISTINCT x)
- 带连接的长格式
- 354 ms
- 358 ms
- 373 ms -- (posts 上的索引扫描,votes 上的索引扫描,合并连接)
- 330 ms
count(DISTINCT x)
- 不带连接的短格式
- 164 ms
- 164 ms
- 164 ms -- (总是顺序扫描)
- 142 ms
原始查询的最佳时间:
简化版本的最佳时间:
@wildplasser的CTE查询使用与长格式相同的计划(在post上进行索引扫描,在votes上进行索引扫描,合并连接),并额外增加了一些开销。最佳时间:
即将推出的PostgreSQL 9.2中的只索引扫描可以改善每个查询的结果,尤其是EXISTS
。
与Postgres 9.5相关的更详细的基准测试(实际检索不同行,而不仅仅是计数):
SELECT COUNT(*) from POSTS
是否有效? - rogerdpack