我有一张拥有28列和7M记录但没有主键的表。
CREATE TABLE records (
direction smallint,
exporters_id integer,
time_stamp integer
...
)
我在这个表上创建了索引,并在此之后对表进行了清理(自动清理已开启)
CREATE INDEX exporter_dir_time_only_index ON sacopre_records
USING btree (exporters_id, direction, time_stamp);
我想执行这个查询。
SELECT count(exporters_id) FROM records WHERE exporters_id = 50
该表中有6982224条记录,导出者ID为50。我期望此查询使用索引唯一扫描来获取结果,但它使用了顺序扫描。 |
以下是"EXPLAIN ANALYZE"输出: |
Aggregate (cost=204562.25..204562.26 rows=1 width=4) (actual time=1521.862..1521.862 rows=1 loops=1)
-> Seq Scan on sacopre_records (cost=0.00..187106.88 rows=6982149 width=4) (actual time=0.885..1216.211 rows=6982224 loops=1)
Filter: (exporters_id = 50)
Rows Removed by Filter: 2663
Total runtime: 1521.886 ms
但是当我将exporters_id更改为另一个id时,查询会使用索引扫描。
Aggregate (cost=46.05..46.06 rows=1 width=4) (actual time=0.321..0.321 rows=1 loops=1)
-> Index Only Scan using exporter_dir_time_only_index on sacopre_records (cost=0.43..42.85 rows=1281 width=4) (actual time=0.313..0.315 rows=4 loops=1)
Index Cond: (exporters_id = 47)
Heap Fetches: 0
Total runtime: 0.358 ms
问题出在哪里?
SELECT COUNT(exporters_id=50) FROM records
吗? - Tordek