如何强制PostgreSQL使用我的索引?
CREATE TABLE product ( product_id SERIAL,factory_key VARCHAR(60),relevant BOOLEAN ) Indexes: "product_factory_key_key" btree (factory_key); "product_factory_key_relevant_key" btree (factory_key,relevant) WHERE relevant = false; "product_relevant_key" btree (relevant); 事实: >产品表中有大约1亿条记录 这是问题查询: SELECT * FROM product WHERE factory_key='some_product_key' AND relevant=false LIMIT 10; 解释分析: QUERY PLAN --------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..23.06 rows=10 width=188) (actual time=2709.654..32252.961 rows=10 loops=1) -> Seq Scan on product (cost=0.00..7366785.34 rows=3194759 width=188) (actual time=2709.634..32252.904 rows=10 loops=1) Filter: ((NOT relevant) AND ((product_key)::text = 'some_product_key'::text)) Rows Removed by Filter: 449486 Total runtime: 32253.150 ms (5 rows) 问题: 这是有问题的,因为: >我相信规划师选择使用seq扫描,因为有很多行与这个工厂相匹配. (约320万行与此工厂匹配或约3%) 我已经创建了一个复合索引product_factory_key_relevant_key,但它没有利用索引. 编辑: 我试图强制postgres使用复合键:product_factory_key_relevant_key SET enable_seqscan = off 虽然,它现在使用索引扫描.它实际上仍然比seqscan慢. (所以我猜计划器在进行seq扫描时是正确的) QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.57..34.03 rows=10 width=188) (actual time=8.088..469974.692 rows=10 loops=1) -> Index Scan using product_factory_key_relevant_key on product (cost=0.57..10689307.49 rows=3194776 width=188) (actual time=8.083..469974.655 rows=10 loops=1) Index Cond: (relevant = false) Filter: ((NOT relevant) AND ((product_key)::text = 'some_product_key'::text)) Rows Removed by Filter: 2205295 Total runtime: 469974.820 ms (6 rows) 解决方法
覆盖成本参数
你不能强迫PostgreSQL使用特定的索引,或者完全阻止它进行seqscan. 但是,如果可能,可以通过将相关的enable_参数设置为off来告诉它避免执行某些扫描类型.它真的是一个仅用于调试的功能. 要进行测试,请尝试: SET enable_seqscan = off; 如果Pg可以使用索引扫描(或其他东西),它会. 您可能还想考虑: SET random_page_cost = 1.1 即告诉PostgreSQL,随机I / O仅比顺序I / O略贵.这通常适用于具有SSD的系统,或者大多数DB都缓存在RAM中的情况.在这种情况下,更有可能选择一个索引. 当然,如果您的系统的随机I / O实际上更昂贵,那么使用索引可能会更慢. 选择性,部分指标 你应该做的是遵循你已经给出的建议.按选择性顺序创建索引 – 如果相关性不太常见,请使用它.您甚至可以更进一步创建部分索引: CREATE INDEX idx_name_blah ON tbl_name_blah (factory_key) WHERE (NOT relevant); 该索引仅包含相关=’f’的值.它只能用于规划器知道相关的查询是错误的查询.另一方面,它将是一个更小,更快的索引. 统计 您可能还有不准确的统计信息,导致PostgreSQL认为价值频率与您的桌子实际不同.解释分析将有助于表明这一点. 如果统计数据刚刚过时,你也可以只分析my_table;如果是这样,增加autovacuum运行的频率,因为它没有跟上. 如果统计数据是最新的,但规划人员仍在进行基于统计数据的误估计,那么增加表格的统计目标(参见手册)并重新分析可能会有所帮助,如果它实际上是统计误估计问题. 版本 较旧的PostgreSQL版本往往不太了解成本估算,查询优化,统计,查询执行方法以及其他所有内容. 如果您没有使用最新版本,请升级. 例如,9.2的仅索引扫描将允许您创建部分索引 (product_id,factory_key)WHERE(不相关) 然后运行查询: SELECT product_id,factory_key FROM my_table WHERE NOT relevant; 应该只读取索引,根本没有堆访问权限. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |