我认为没有必要让数据库执行WHERE子句的额外工作。既然你确实想要所有记录,那么你将不得不去获取它们。如果你从表中选择一条记录,它将按表顺序检索它们,并且你可以自己分区。如果你选择WHERE male和WHERE female,你将不得不为每个操作命中一个索引,并且你将失去一些数据局部性。
例如,如果您的磁盘上的记录是交替的男女,并且数据集比内存大得多,则如果您执行两个单独的查询,您可能需要两次读取整个数据库,而同时选择则是单个表扫描。
编辑:由于我被downmodded到了无人问津,所以我决定实际运行测试。
我生成了一个表
CREATE TEMPORARY TABLE gender_test (some_data DOUBLE PRECISION, gender CHARACTER VARYING(20));
我生成了一些随机数据,
select gender, count(*) from gender_test group by gender;
gender | count
--------+----------
female | 12603133
male | 10465539
(2 rows)
首先,让我们在没有索引的情况下运行这些测试,这种情况下我非常确定我是正确的...
EXPLAIN ANALYSE SELECT * FROM gender_test WHERE gender='male';
QUERY PLAN
Seq Scan on gender_test (cost=0.00..468402.00 rows=96519 width=66) (actual time=0.030..4595.367 rows=10465539 loops=1)
Filter:
((gender)::text = 'male'::text)
Total runtime: 5150.263 ms
EXPLAIN ANALYSE SELECT * FROM gender_test WHERE gender='female';
QUERY PLAN
Seq Scan on gender_test
(cost=0.00..468402.00 rows=96519 width=66) (actual time=0.029..4751.219 rows=12603133 loops=1)
Filter: ((gender)::text = 'female'::text)
Total runtime: 5418.891 ms
EXPLAIN ANALYSE SELECT * FROM gender_test;
QUERY PLAN
Seq Scan on gender_test
(cost=0.00..420142.40 rows=19303840 width=66) (actual time=0.021..3326.164 rows=23068672 loops=1)
Total runtime: 4543.393 ms
有趣的是,没有筛选条件地扫描表格获取数据确实更快!实际上,快了两倍以上!(5150 + 5418 > 4543)就像我预测的那样!:-p
现在,让我们创建一个索引并看看它是否改变了结果...
CREATE INDEX test_index ON gender_test(gender);
现在重新运行同样的查询...
test=> EXPLAIN ANALYSE SELECT FROM gender_test WHERE gender='male';
查询计划
---------------------------
Bitmap Heap Scan on gender_test (cost=2164.69..195922.27 rows=115343 width=66) (actual time=2008.877..4388.348 rows=10465539 loops=1)
Recheck Cond: ((gender)::text = 'male'::text)
-> Bitmap Index Scan on test_index (cost=0.00..2135.85 rows=115343 width=0) (actual time=2006.047..2006.047 rows=10465539 loops=1)
Index Cond: ((gender)::text = 'male'::text)
Total runtime: 4941.64 ms
test => EXPLAIN ANALYSE SELECT * FROM gender_test WHERE gender='female';
查询计划
---------------------------
Bitmap Heap Scan on gender_test (cost=2164.69..195922.27 rows=115343 width=66) (actual time=1915.385..4269.933 rows=12603133 loops=1)
Recheck Cond: ((gender)::text = 'female'::text)
-> Bitmap Index Scan on test_index (cost=0.00..2135.85 rows=115343 width=0) (actual time=1912.587..1912.587 rows=12603133 loops=1)
Index Cond: ((gender)::text = 'female'::text)
Total runtime: 4931.555 ms (5 rows)
test=> EXPLAIN ANALYSE SELECT * FROM gender_test;
查询计划
---------------------------
Seq Scan on gender_test (cost=0.00..457790.72 rows=23068672 width=66) (actual time=0.021..3304.836 rows=23068672 loops=1)
Total runtime: 4523.754 ms
有趣....一次性扫描整个表仍然比索引快
两倍!(4941 + 4931 vs 4523)
注意:这种方法在科学上有各种不足之处。我使用了16GB的RAM,因此整个数据集都可以放入内存中。Postgres没有配置使用那么多的内存,但磁盘缓存仍然有所帮助...我假设(但不想试一试)一旦到达磁盘,效果只会更差。我只尝试了默认的btree Postgres索引。我假设PHP分区不需要时间 - 这不是真的,但可能是一个相当合理的近似值。
所有测试都在Mac Pro 8路2.66 Xeon 16GB RAID-0 7200rpm上运行
此外,这个数据集有2600万行,可能比大多数人关心的要大一些...
显然,原始速度并不是您关心的唯一事情。在许多应用程序中,您更关心单独获取它们的逻辑“正确性”。但是,当老板说“我们需要加快速度”时,这将(显然)使您的速度提高2倍。OP明确询问效率。满意吗?