sql - 如何找到需要索引的列？

Question

我开始学习 SQL 和关系数据库。下面是我的表格，它有大约 1000 万条记录。我的组合键是 (reltype, from_product_id, to_product_id)。

在选择需要索引的列时应该遵循什么策略？此外，我还记录了将在表上执行的操作。请帮助确定需要索引哪些列或列组合？

表DDL如下所示。

表名:prod_rel。

数据库架构名称:public

CREATE TABLE public.prod_rel (
reltype varchar NULL,
assocsequence float4 NULL,
action varchar NULL,
from_product_id varchar NOT NULL,
to_product_id varchar NOT NULL,
status varchar NULL,
starttime varchar NULL,
endtime varchar null,
primary key reltype, from_product_id, to_product_id)
);

对表执行的操作:

select distinct(reltype ) 
from public.prod_rel; 

update public.prod_rel  
   set status = ? , starttime = ? 
where from_product_id = ?;

update public.prod_rel  
   set status = ? , endtime = ? 
where from_product_id = ?;

select * 
from public.prod_rel  
where from_product_id  in (select distinct (from_product_id) 
                           from public.prod_rel 
                           where status = ? 
                           and action in ('A', 'E', 'C', 'P') 
                           and reltype = ? 
                           fetch first 1000 rows only);

注意:我没有执行任何 JOIN 操作。另外请忽略表名或列名的大写字母。我才刚刚开始。

Answer 1

理想的是两个索引:

CREATE INDEX ON prod_rel (from_product_id);

CREATE INDEX ON prod_rel (status, reltype)
   WHERE action IN ('A', 'E', 'C', 'P');

您的主键(也是使用索引实现的)不支持查询 2 和 3，因为 from_product_id 不在开头。如果将主键重新定义为 from_product_id, to_product_id, reltype，则不需要我建议的第一个索引。

为什么顺序很重要？想象一下，您正在图书馆中查找一本书，其中的书籍按“姓氏、名字”排序。您可以使用此顺序快速查找“Dickens”的所有书籍，但不是任何“Charles”的所有书籍。

但让我也对您的疑问发表评论。

如果有很多不同的 reltype 值，第一个会表现很差；在这种情况下尝试提高 work_mem。它总是对整个表进行顺序扫描，没有索引可以提供帮助。