sql - 始终让特定查询的 postgres 查询计划使用索引而不是扫描

Question

一段时间以来，我一直在努力弄清楚如何让查询计划更智能一些，但现在很不成功。我与 work_mem 和 friend 搞混了，多次运行 vacumm analyze 并尝试使用 order by 更改查询。我已经包含了 3 次具有不同偏移量的相同查询。我的印象是这个查询的性能几乎没有达到应有的水平。有什么想法吗？

以防万一它不会突然出现在你面前——以下查询之间的唯一变化是 offset

bloomapi=# explain analyze SELECT * FROM npis WHERE provider_last_name_legal_name = 'THOMPSON' offset 250 limit 10;
                                                                             QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=965.13..998.97 rows=10 width=2589) (actual time=568.458..577.507 rows=10 loops=1)
   ->  Bitmap Heap Scan on npis  (cost=119.15..20382.11 rows=5988 width=2589) (actual time=58.140..577.027 rows=260 loops=1)
         Recheck Cond: ((provider_last_name_legal_name)::text = 'THOMPSON'::text)
         ->  Bitmap Index Scan on npis_temp_provider_last_name_legal_name_idx1  (cost=0.00..117.65 rows=5988 width=0) (actual time=36.819..36.819 rows=5423 loops=1)
               Index Cond: ((provider_last_name_legal_name)::text = 'THOMPSON'::text)
 Total runtime: 578.301 ms
(6 rows)

bloomapi=# explain analyze SELECT * FROM npis WHERE provider_last_name_legal_name = 'THOMPSON' offset 100 limit 10;
                                                                             QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=395.81..435.40 rows=10 width=2589) (actual time=0.397..0.440 rows=10 loops=1)
   ->  Index Scan using npis_temp_provider_last_name_legal_name_idx1 on npis  (cost=0.00..23701.38 rows=5988 width=2589) (actual time=0.063..0.293 rows=110 loops=1)
         Index Cond: ((provider_last_name_legal_name)::text = 'THOMPSON'::text)
 Total runtime: 0.952 ms
(4 rows)

bloomapi=# explain analyze SELECT * FROM npis WHERE provider_last_name_legal_name = 'THOMPSON' offset 4100 limit 10;
                                                                            QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=13993.25..14027.09 rows=10 width=2589) (actual time=9356.723..9400.021 rows=10 loops=1)
   ->  Bitmap Heap Scan on npis  (cost=119.15..20382.11 rows=5988 width=2589) (actual time=2.968..9393.327 rows=4110 loops=1)
         Recheck Cond: ((provider_last_name_legal_name)::text = 'THOMPSON'::text)
         ->  Bitmap Index Scan on npis_temp_provider_last_name_legal_name_idx1  (cost=0.00..117.65 rows=5988 width=0) (actual time=1.943..1.943 rows=5423 loops=1)
               Index Cond: ((provider_last_name_legal_name)::text = 'THOMPSON'::text)
 Total runtime: 9400.426 ms
(6 rows)

一些相关说明:

• 我在运行第一个查询之前清除了系统上的共享内存，因此第一个查询的某些实际时间可能会受到索引加载的影响
• 数据广泛且稀疏 -- 329 列，其中许多是空字符 varying(30ish)
• 数据几乎是只读的 -- 每周更新一次另外 15000 行。
• 当 ubuntu ppa 附带默认数据库设置时，这些查询的性能实际上更高(我目前没有这些查询计划，但如果没有明显的跳出，可以深入研究否则)。已从默认值更改的参数:shared_buffers = 256MB，effective_cache_size = 512MB，checkpoint_segments = 64，checkpoint_completion_target = 0.9，default_statistics_target = 500
• 表本身的实际数据约为 400 万行/1.29GB，provider_last_name_legal_name 是 btree 索引——索引大小为 95mb。该列中大约 3/4 的行具有非空值，并且整个表具有 488k 个不同的值

Answer 1

我有根据的猜测是大抵消触发了这些计划。即使您将结果限制为十行，PostgreSQL 也必须考虑前面的所有行。我怀疑当您删除 offset(例如，在第一个查询中使用 limit 260)时，您会看到类似的运行时间。

您可以使用 configuration parameters 禁用某些计划类型直到查询共享相似的计划。这可能会帮助您了解为什么一个计划比另一个更好。

set enable_bitmapscan = false;