代码之家 › 专栏 › 技术社区 › bortzmeyer

“不相等”搜索的SQL索引

indexing postgresql sql

bortzmeyer · 技术社区 · 14 年前

SQL索引允许快速查找与我的查询匹配的字符串。现在,我要在一张大桌子上搜索不比赛。当然,正常的索引没有帮助,我必须进行缓慢的顺序扫描:

essais=> \d phone_idx
Index "public.phone_idx"
 Column | Type 
--------+------
 phone  | text
btree, for table "public.phonespersons"

essais=> EXPLAIN SELECT person FROM PhonesPersons WHERE phone = '+33 1234567';
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 Index Scan using phone_idx on phonespersons  (cost=0.00..8.41 rows=1 width=4)
   Index Cond: (phone = '+33 1234567'::text)
(2 rows)

essais=> EXPLAIN SELECT person FROM PhonesPersons WHERE phone != '+33 1234567';
                              QUERY PLAN                              
----------------------------------------------------------------------
 Seq Scan on phonespersons  (cost=0.00..18621.00 rows=999999 width=4)
   Filter: (phone <> '+33 1234567'::text)
(2 rows)

我理解(见Mark Byers的非常好的解释)PostgreSQL 当看到顺序扫描时,可以决定不使用索引会更快(例如,如果几乎所有元组都匹配)。但是, 在这里,“不相等”的搜索速度真的很慢。

有没有办法让这些“不等于”的搜索速度更快?

下面是另一个例子,来说明马克·拜尔的精彩言论。这个索引用于“=”查询(它返回但不是为'!=查询:

essais=> \d tld_idx
 Index "public.tld_idx"
     Column      | Type 
-----------------+------
 pg_expression_1 | text
btree, for table "public.emailspersons"

essais=> EXPLAIN ANALYZE SELECT person FROM EmailsPersons WHERE tld(email) = 'fr';
                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using tld_idx on emailspersons  (cost=0.25..4010.79 rows=97033 width=4) (actual time=0.137..261.123 rows=97110 loops=1)
   Index Cond: (tld(email) = 'fr'::text)
 Total runtime: 444.800 ms
(3 rows)

essais=> EXPLAIN ANALYZE SELECT person FROM EmailsPersons WHERE tld(email) != 'fr';
                         QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Seq Scan on emailspersons  (cost=0.00..27129.00 rows=2967 width=4) (actual time=1.004..1031.224 rows=2890 loops=1)
   Filter: (tld(email) <> 'fr'::text)
 Total runtime: 1037.278 ms
(3 rows)

DBMS是PostgreSQL 8.3(但我可以升级到8.4)。

2 回复 | 直到 14 年前

araqnid 14 年前

也许这会有助于写:

SELECT person FROM PhonesPersons WHERE phone < '+33 1234567'
UNION ALL
SELECT person FROM PhonesPersons WHERE phone > '+33 1234567'

或者简单地

SELECT person FROM PhonesPersons WHERE phone > '+33 1234567'
                                       OR phone < '+33 1234567'

PostgreSQL应该能够确定范围操作的选择性非常高,并考虑使用索引。

我认为它不能直接使用索引来满足不等于谓词,但是如果它可以在计划期间尝试像上面那样重新编写不等于谓词(如果它有帮助的话),那就更好了。如果可行,请向开发人员建议;)

理由:在索引中搜索不等于某个值的所有值需要扫描完整索引。相反,搜索少于某个键的所有元素意味着在树中找到最大的不匹配项并向后扫描。同样,在相反的方向上搜索所有大于某个键的元素。这些操作使用B树结构很容易实现。此外,PostgreSQL收集的统计数据应该能够指出“+33 1234567”是一个已知的频繁值:通过从1中删除那些和空值的频率,我们可以选择剩余的行的比例:柱状图边界将指示这些行是否偏向一边。但是如果排除空值和频繁的值会使行的比例保持在足够低的水平(ISR大约为20%),那么索引扫描应该是合适的。检查pg_stats中该列的统计信息,查看它实际计算的比例。

更新 :我在一个分布类似的本地表上尝试了这个方法,上面的两种形式都产生了一个普通的seq扫描以外的结果。后者(使用“或”)是一种位图扫描,如果对公共值的偏差特别大,它实际上可能会变成一种序列扫描。虽然规划人员可以看到,但我认为它不会自动重写为“内部附加(索引扫描,索引扫描)”。关闭“启用位图扫描”使其恢复到顺序扫描。

聚苯乙烯 :如果数据库位置不是C,则为文本列编制索引和使用不等式运算符可能是一个问题。您可能需要添加一个使用文本模式或varchar模式的额外索引;这类似于为 column LIKE 'prefix%' 谓语。

替代方案 :可以创建部分索引:

CREATE INDEX PhonesPersonsOthers ON PhonesPersons(phone) WHERE phone <> '+33 1234567'

这将使 <> -使用select语句只需扫描该部分索引:因为它排除了表中的大多数条目,所以它应该很小。

Mark Byers 14 年前

数据库可以使用此查询的索引,但它选择不使用,因为这样会变慢。更新 :这并不完全正确:您必须稍微重写查询。见Araqnid的答案。

WHERE子句几乎选择表中的所有行(行=999999)。在这种情况下,数据库可以看到表扫描速度更快,因此忽略了索引。因为柱子比较快 person 不在索引中,因此必须对每行进行两次查找,一次在索引中检查WHERE子句,然后在主表中再次获取列 人 .

如果有不同类型的数据,其中值最多 foo 只有少数人 bar 你说 WHERE col <> 'foo' 然后它可能会使用索引。

有没有办法让这些“不等于”的搜索速度更快?

任何选择近100万行的查询都会很慢。尝试添加限制子句。