连接表的索引

但是,当实际运行解释时,我得到的结果与每种情况完全相同(每列上有一个索引,两列上有一个索引).

我一直在阅读文档PostgreSQL的关于索引,并没有作出,我得到的结果任何意义:

Hash Join  (cost=15.05..42.07 rows=11 width=36) (actual time=0.024..0.025 rows=1 loops=1)
  Hash Cond: (artists.artist_id = albums_artists.artist_id)
  ->  Seq Scan on artists  (cost=0.00..22.30 rows=1230 width=36) (actual time=0.006..0.006 rows=1 loops=1)
  ->  Hash  (cost=14.91..14.91 rows=11 width=4) (actual time=0.009..0.009 rows=1 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 1kB
        ->  Bitmap Heap Scan on albums_artists  (cost=4.24..14.91 rows=11 width=4) (actual time=0.008..0.009 rows=1 loops=1)
              Recheck Cond: (album_id = 1)
              Heap Blocks: exact=1
              ->  Bitmap Index Scan on albums_artists_album_id_index  (cost=0.00..4.24 rows=11 width=0) (actual time=0.005..0.005 rows=1 loops=1)
                    Index Cond: (album_id = 1)

在使用由2个不同列组成的索引时,我希望在最后一步不会得到索引扫描(因为我只在WHERE子句中使用其中一个).

我打算在ORM库中打开一个错误,为连接表的两列添加一个索引,但现在我不太确定.任何人都可以帮助我理解为什么两种情况下的行为相似,实际上有什么区别,如果有的话？

在键列上添加一个NOT NULL约束(一个带有NULL的元组在这里没有意义)

添加一个PRIMARY KEY(在两个关键字段上强制使用UNIQUE索引)

作为FK查找的支持:以相反的顺序为PK字段添加复合索引

在创建/添加PK和索引之后,您可能想要对表进行分析(只有键列具有统计信息)

CREATE TABLE albums_artists
    ( album_id integer NOT NULL REFERENCES albums
    , artist_id integer NOT NULL REFERENCES artists
    , PRIMARY KEY (album_id, artist_id)
    );

CREATE UNIQUE INDEX ON albums_artists (artist_id, album_id);

观察到的行为背后的原因是计划器/优化器是基于信息的,由启发式驱动.如果没有任何关于给定条件实际需要的行数的信息,或实际生成的行的比例(在JOIN的情况下),计划程序会猜测:(例如:范围查询的10%) .对于小型查询,散列连接将始终是获胜方案,它确实意味着从两个表中获取所有元组,但是连接本身非常有效.

对于属于键或索引的列,将收集统计信息,以便规划人员可以对所涉及的行数进行更实际的估计.Ald通常会产生索引计划,因为这可能需要更少的页面.

外键是一个非常特殊的情况; 因为计划者将知道参考表中的所有值都将出现在参考表中.(即100%,假设NOT NULL)