向我解释一个 postgresql EXPLAIN
在这里得到这个查询：
EXPLAIN
SELECT persons.id AS id, ppm.first
FROM myschema.persons
INNER JOIN myotherschema.ppm ON persons.key = ppm.pid
WHERE persons.id = 279759;
列ppm.pid是主键并且在索引中：
CREATE INDEX ppm_pkey_index
ON myotherschema.ppm
USING btree
(pid);
所以这里是解释：
Hash Join (cost=8.31..3105.40 rows=306 width=23)
Hash Cond: (textin(int4out(ppm.pid)) = persons.key)
-> Seq Scan on ppm (cost=0.00..2711.33 rows=61233 width=23)
-> Hash (cost=8.29..8.29 rows=1 width=12)
-> Index Scan using pskey on persons (cost=0.00..8.29 rows=1 width=12)
Index Cond: (id = 279759)
它似乎没有使用ppm_pkey_index all：它似乎仍在扫描 61,233 行。这是为什么呢？难道是我读错了？推论：postgresql 中主键不是自动索引的吗？那么我的索引是多余的吗？
Got this here query:
EXPLAIN
SELECT persons.id AS id, ppm.first
FROM myschema.persons
INNER JOIN myotherschema.ppm ON persons.key = ppm.pid
WHERE persons.id = 279759;
The column ppm.pid is a primary key AND in the index:
CREATE INDEX ppm_pkey_index
ON myotherschema.ppm
USING btree
(pid);
And so here's the EXPLAIN:
Hash Join (cost=8.31..3105.40 rows=306 width=23)
Hash Cond: (textin(int4out(ppm.pid)) = persons.key)
-> Seq Scan on ppm (cost=0.00..2711.33 rows=61233 width=23)
-> Hash (cost=8.29..8.29 rows=1 width=12)
-> Index Scan using pskey on persons (cost=0.00..8.29 rows=1 width=12)
Index Cond: (id = 279759)
It doesn't seem to be using the ppm_pkey_index at all: it still seems to be scanning 61,233 rows. Why is this? Am I misreading it? Corollary: aren't primary keys automatically indexed in postgresql? Is my index then redundant?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
主键在您的键上创建唯一索引。所以你的索引确实是多余的。
创建索引后，您是否在表上运行了真空分析？
我现在看到另一个问题：
ppm.pid和persons.key是否具有相同的字段类型？由于不必要的数据转换，您可能会遇到性能问题，并且无法使用索引，因为您没有对连接时需要使用的转换函数建立索引...Primary keys create UNIQUE INDEXES on your key. So your index is redundant indeed.
Did you run
vacuum analyzeon your table after creating the index?I see another problem now: are
ppm.pidandpersons.keyof the same field type? You may run into perfomance issues due to unnecessary data conversions, and inability to use indexes because you are not indexing on casting functions you need to use on join...如果将其更改为以下内容会发生什么：
What happens if you change it to: