sql - 如何为连接表创建索引

Question

我是索引新手，遇到了一个问题，Postgresql 一直说我的代码有错误。

我是否需要在引用 members 表的主键的 bookings 表上添加外键。

如果我的 SQL 表结构如下，并且我有一个使用 joindate 的 WHERE 子句的 JOIN 查询，我该如何实现索引

SELECT *
FROM bookings b
JOIN members m ON m.memid = b.memid WHERE joindate > '2012-08-01';

bookings                                   members
---------                                 ----------
PK: bookid                                 PK: memid
facid                                      surname
memid                                      firstname
starttime                                  address
slots                                      joindate
                                           recommendedby

我尝试的查询

CREATE JOIN INDEX join_memid_index FOR members JOIN bookings ON  bookings.memid = members.memid

这是我在运行上述查询时得到的错误

ERROR: syntax error at or near "JOIN"
LINE 1: CREATE JOIN INDEX join_memid_index FOR members JOIN bookings...
^
SQL state: 42601
Character: 8

Answer 1

索引是表的局部结构。它只会改进查询，不会执行操作。

您必须分别为每个表编制索引:

CREATE INDEX idx_members_memid ON members (memid);
CREATE INDEX idx_bookings_memid ON bookings (memid);

如果服务器认为这将提高性能，它将使用该索引来实现更高效的连接过程。如果它不决定这样做，它将忽略索引的存在并使用表扫描。

顺便说一句，服务器可能会做出错误的决定...

也许包含 joindate 列的 members 复合索引比 memid 的单个索引更有效:

CREATE INDEX idx_members_memid_joindate_1 ON members (memid, joindate);
CREATE INDEX idx_members_memid_joindate_1 ON members (joindate, memid);

只有执行计划调查才能显示成员(或无)的上述 3 个索引中的哪些将被服务器使用。它的决定可能会根据数据统计(行数，总计和匹配条件)和参数值而改变。