Postgresql索引顺序扫描100万行

Question

我遇到了一个问题，即一个已被索引的查询拒绝使用索引，因为它不够具有选择性（假设符合条件的仅有1.3亿行中的60行），所以决定使用顺序扫描。

我面临的问题是，在这种情况下，顺序扫描确实不是最佳选择，由于某种原因，它得分很高，但事实上，只有在之前查询过并且能够从缓存中加载所有内容时才能快速运行。

如果两者都在缓存中，索引扫描可能会稍微慢一些与顺序扫描相比，但这种情况很少发生，并且当两个查询都是“cold”时，索引扫描仍然比顺序扫描快得多（毫秒和秒的差距）。

请注意，索引扫描更优越，因为我使用了限制子句，所以它应该能够非常快地获取那些少数行。

我将统计信息设置为1000（默认值为100）并进行了清理，但结果相同。

总之，对于选择性较低的索引，顺序扫描与索引扫描的比较中，规划程序会错误地选择顺序扫描。只有当它被缓存时，顺序扫描才更好，否则它会更糟糕。

查询和计划，请注意索引一个从缓存中加载而顺序扫描没有完全加载。

explain (analyze, buffers)
select *
from identities_identity
where email_domain = 'live.com'
limit 100


'Limit  (cost=0.00..63.50 rows=100 width=573) (actual time=75215.573..75215.640 rows=100 loops=1)'
'  Buffers: shared hit=75113 read=588870'
'  ->  Seq Scan on identities_identity  (cost=0.00..2980008.00 rows=4692733 width=573) (actual time=75215.571..75215.604 rows=100 loops=1)'
'        Filter: ((email_domain)::text = 'live.com'::text)'
'        Rows Removed by Filter: 54464136'
'        Buffers: shared hit=75113 read=588870'
'Planning time: 0.097 ms'
'Execution time: 75215.675 ms'


'Limit  (cost=0.57..187.26 rows=100 width=573) (actual time=0.027..0.090 rows=100 loops=1)'
'  Buffers: shared hit=6'
'  ->  Index Scan using identities_identity_email_domain_9056bd28 on identities_identity  (cost=0.57..8760978.66 rows=4692733 width=573) (actual time=0.026..0.057 rows=100 loops=1)'
'        Index Cond: ((email_domain)::text = 'live.com'::text)'
'        Buffers: shared hit=6'
'Planning time: 0.078 ms'
'Execution time: 0.124 ms'

更新:

表定义(包含电子邮件和电子邮件域的索引，分别是标准索引和varchar_pattern_ops索引)

CREATE TABLE public.identities_identity
(
    id bigint NOT NULL DEFAULT nextval('identities_identity_id_seq'::regclass),
    email character varying(1000) COLLATE pg_catalog."default",
    email_domain character varying(1000) COLLATE pg_catalog."default",
    leak_id bigint NOT NULL,
    CONSTRAINT identities_identity_pkey PRIMARY KEY (id),
    CONSTRAINT identities_identity_leak_id_87e1ae4e_fk_identities_leak_id FOREIGN KEY (leak_id)
        REFERENCES public.identities_leak (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED
)

表统计信息（执行vacuum analyze之后）

attname, avg_width, n_distinct, correlation
'id',8,'-1','0.999988'
'email',23,'-0.636853','-0.020479'
'email_domain',10,'3876','0.696452'
'leak_id',8,'1','1'

- Cristiano Coelho

---

我在想这个问题可能与数据不均匀分布有关，因为它是按域名插入的（例如，先插入所有gmail.com的数据，然后是live.com等等）。 - Cristiano Coelho

你尝试过使用 set enable_seqscan = false; 吗？https://www.postgresql.org/docs/current/static/runtime-config-query.html - Dave Gray

DaveGray 是的，这是我获取索引扫描计划的方式，否则它将始终使用序列扫描。@LaurenzAlbe 这就是我想的，Postgres 不应该更加聪明吗？我已经尝试过将 set statistics 设置为 10000 的 vacuum analyze，但结果仍然相同。有哪些选项可以修复这个问题？数据插入以及自动 id 排序基本上都将那大约 5000 万行分组在一起，我应该尝试在其他索引上进行 CLUSTER 吗？如何随机分配数据？ - Cristiano Coelho

无法想到比为此查询设置enable_seqscan=off更好的方法... - Laurenz Albe

0. 表定义和统计数据，请！
1. 在添加索引后，你进行了分析吗？
2. email_domain 的基数是多少？
3. 你的行相当臃肿，即使对于索引也是如此。

- wildplasser

Answer 1

你可以使用一种巧妙的方法来强制进行索引扫描：

SELECT *
FROM identities_identity
WHERE email_domain IN ('live.com', NULL)
ORDER BY email_domain
LIMIT 100;

如果PostgreSQL需要排序，使用索引始终会更便宜。 如果您使用了“WHERE email_domain ='live.com'”，PostgreSQL足够聪明，知道不必进行排序，这就是为什么我添加了第二个无用项来欺骗它的原因。

- Laurenz Albe

---

这是一个很好的强制索引的技巧，看起来很有效！但是，如果还有LIKE 'xxx%'查询怎么办？我也有一个使用varchar_pattern_ops的索引，它遇到了完全相同的问题，上述解决方案无法使用，因为order_by会阻止模式ops索引的使用。 我想我也应该在问题中提到这一点。 - Cristiano Coelho

那是不同的。你试过 WHERE email_domain LIKE 'live%' AND email_domain ~>=~ 'live' 吗？ - Laurenz Albe

它仍然会选择一个seqscan。没关系，只要数据均匀分布，Postgres似乎选择了正确的计划。 - Cristiano Coelho

Answer 2

好的，解决方案是对数据进行物理重新排序，使得特殊情况下的顺序扫描不会失败。

基本上，在一个使数据均匀分布的列上运行CLUSTER identities_identity USING index_name;（例如电子邮件域名前面的值）。

现在，即使缓冲区冷启动，顺序扫描也能正常运行。

但是，@Laurenz Albe的答案对于我发布的特定情况非常好，并且是一个很好的技巧，如果无法进行聚类，则可以使用该技巧。

- Cristiano Coelho