sql - 为什么我的 SQL 查询没有使用索引？

Question

这是我的查询:

explain analyze SELECT levenshtein('google', lower(s."Name"), 2, 2, 1), d."Domain"
FROM   analyst_sld s, analyst_domain d
WHERE  levenshtein('google', lower(s."Name"), 2, 2, 1) < 4 AND s.id = d."SLDk_id"
ORDER  BY 1;

这是输出:

                                                                  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=5340874.17..5383497.72 rows=17049420 width=46) (actual time=136245.943..138709.585 rows=1022346 loops=1)
   Sort Key: (levenshtein('google'::text, lower((s."Name")::text), 2, 2, 1))
   Sort Method:  external sort  Disk: 78656kB
   ->  Hash Join  (cost=122111.24..1195078.39 rows=17049420 width=46) (actual time=16730.865..133020.419 rows=1022346 loops=1)
         Hash Cond: (d."SLDk_id" = s.id)
         ->  Seq Scan on analyst_domain d  (cost=0.00..417631.20 rows=17049420 width=38) (actual time=0.036..64677.170 rows=17041042 loops=1)
         ->  Hash  (cost=103151.93..103151.93 rows=1090665 width=16) (actual time=16730.443..16730.443 rows=1071 loops=1)
               ->  Seq Scan on analyst_sld s  (cost=0.00..103151.93 rows=1090665 width=16) (actual time=14.742..16726.358 rows=1071 loops=1)
                     Filter: (levenshtein('google'::text, lower(("Name")::text), 2, 2, 1) < 4)
 Total runtime: 139557.853 ms

为什么不用索引，而是顺序扫描呢？另外，“Hash Join”和“Hash Cond”是什么意思？

编辑_1:索引:

                                      Table "public.analyst_domain"
     Column     |           Type           |                          Modifiers
----------------+--------------------------+-------------------------------------------------------------
 ID             | integer                  | not null default nextval('analyst_domain_id_seq'::regclass)
 Domain         | character varying(255)   | not null
 SLDk_id        | integer                  |
Indexes:
    "analyst_domain_pkey" PRIMARY KEY, btree ("ID")
    "analyst_domain_Domain_key" UNIQUE, btree ("Domain")
    "analyst_domain_sldk" btree ("SLDk_id")



                                      Table "public.analyst_sld"
     Column     |           Type           |                        Modifiers
----------------+--------------------------+----------------------------------------------------------
 id             | integer                  | not null default nextval('analyst_sld_id_seq'::regclass)
 Name           | character varying(255)   | not null
Indexes:
    "analyst_sld_pkey" PRIMARY KEY, btree (id)
    "analyst_sld_Name_key" UNIQUE, btree ("Name") CLUSTER
    "analyst_sld_upper_idx" btree (upper("Name"::text))

Answer 1

它在 analyst_sld 上使用顺序扫描，因为这是进行编辑过滤的唯一方法。如果您认为这是一个重要的过滤器，您可以

CREATE INDEX lev_index on 
 analyst_sld (levenshtein('google', lower("Name"), 2, 2, 1));

就散列而言:Postgres 已决定连接表的最佳方式是在连接列上寻找相等的散列(并在桶有多个条目的情况下解析它们)。您的表有多少个元素，您希望连接有多大？