在pgsql中,考虑到表可以增长,查看表BLOG_POST
是否至少有一次列company_id=5
和status_id=3
的最快请求是什么?
我有很多公司使用这个表,他们可以有很多条目,我的最终目标也是创建一个名为hasCompanyAlreadyPublishedABlogPost(companyId)
的方法。
EXISTS
条件可以实现:
select exists (select *
from blog_post
where company_id = 5
and status_id = 3);
显然,您想要blog_post(company_id, status_id)
上的索引
为了进一步提高性能,我们可以这样做:
select exists (select id
from blog_post
where company_id = 5
and status_id = 3 limit 1);
- 在嵌套查询中添加限制
- 我们可以选择any_column而不是选择*
我用一个示例数据库尝试过:
带id和限制:
explain analyze (select exists (select user_id from sample_table where sample_ids=4 and user_id=5 limit 1));
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Result (cost=8.17..8.18 rows=1 width=1) (actual time=0.014..0.014 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Index Scan using sample_table_pkey on sample_table (cost=0.15..8.17 rows=1 width=0) (actual time=0.012..0.012 rows=1 loops=1)
Index Cond: (user_id = 5)
Filter: (sample_ids = 4)
Planning Time: 0.091 ms
Execution Time: 0.032 ms
没有id和*
explain analyze (select exists (select * from sample_table where sample_ids=4 and user_id=5));
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Result (cost=8.17..8.18 rows=1 width=1) (actual time=0.014..0.014 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Index Scan using sample_table_pkey on sample_table (cost=0.15..8.17 rows=1 width=0) (actual time=0.012..0.012 rows=1 loops=1)
Index Cond: (user_id = 5)
Filter: (sample_ids = 4)
Planning Time: 0.084 ms
Execution Time: 0.034 ms