sql - 为什么增加索引会增加成本？

Question

我正在使用 postgresql 9.4.6。

有以下实体:

CREATE TABLE user (id CHARACTER VARYING NOT NULL PRIMARY KEY);

CREATE TABLE group (id CHARACTER VARYING NOT NULL PRIMARY KEY);

CREATE TABLE group_member (
       id CHARACTER VARYING NOT NULL PRIMARY KEY,
       gid CHARACTER VARYING REFERENCES group(id),
       uid CHARACTER VARYING REFERENCES user(id));

我分析了那个查询:

explain analyze select x2."gid" from "group_member" x2 where x2."uid" = 'a1';

我有几个结果。在每个结果之前，我刷新操作系统缓存并重新启动 postgres:

# /etc/init.d/postgresql stop
# sync
# echo 3 > /proc/sys/vm/drop_caches
# /etc/init.d/postgresql start

分析结果为:

1) cost=4.17..11.28 带索引:

create index "group_member_gid_idx" on "group_member" ("gid");
create index "group_member_uid_idx" on "group_member" ("uid");

 Bitmap Heap Scan on group_member x2  (cost=4.17..11.28 rows=3 width=32) (actual time=0.021..0.021 rows=0 loops=1)
   Recheck Cond: ((uid)::text = 'a1'::text)
   ->  Bitmap Index Scan on group_member_uid_idx  (cost=0.00..4.17 rows=3 width=0) (actual time=0.005..0.005 rows=0 loops=1)
         Index Cond: ((uid)::text = 'a1'::text)
 Planning time: 28.641 ms
 Execution time: 0.359 ms

2) cost=7.97..15.08 带索引:

create unique index "group_member_gid_uid_idx" on "group_member" ("gid","uid");

 Bitmap Heap Scan on group_member x2  (cost=7.97..15.08 rows=3 width=32) (actual time=0.013..0.013 rows=0 loops=1)
   Recheck Cond: ((uid)::text = 'a1'::text)
   ->  Bitmap Index Scan on group_member_gid_uid_idx  (cost=0.00..7.97 rows=3 width=0) (actual time=0.006..0.006 rows=0 loops=1)
         Index Cond: ((uid)::text = 'a1'::text)
 Planning time: 0.132 ms
 Execution time: 0.047 ms

3) cost=0.00..16.38 没有任何索引:

 Seq Scan on group_member x2  (cost=0.00..16.38 rows=3 width=32) (actual time=0.002..0.002 rows=0 loops=1)
   Filter: ((uid)::text = 'a1'::text)
 Planning time: 42.599 ms
 Execution time: 0.402 ms

---

结果 #3 是否更有效？为什么？

---

编辑实际上，表(group、user、group_members)中会有很多行。大约 > 100 万。

Answer 1

在分析查询时，小型数据集的成本和查询计划通常不是大型数据集性能的可靠指南。而且，SQL 更关注较大的数据集，而不是一般的小数据集。

从磁盘读取数据通常是查询性能的驱动因素。使用索引的主要目的是减少读取的数据页数。如果表中的所有数据都放在一个数据页上，那么减少页读取次数的机会就不多了:读取一页所需的时间相同，无论该页有一条记录还是 100 条记录. (通读页面以找到正确的记录也会产生开销，而索引会标识页面上的特定记录。)

索引会产生开销，但通常比读取数据页要少得多。索引本身需要读入内存——这意味着两页而不是一页被读入内存。有人可能会争辩说，对于适合一两页的表格，使用索引可能不是一个很大的优势。

虽然使用索引(在这种情况下)确实需要更长的时间，但以毫秒为单位衡量的性能差异通常与大多数数据库任务无关。如果您想查看索引的工作情况，请在表中放入 100,000 行并运行相同的测试。您会看到没有索引的版本大致与表中的数据量成比例；带有索引的版本相对恒定(好吧，实际上缩放更像是表中记录数的日志)。