PostgreSQL -使用索引进行非常慢的提取
PostgreSQL -使用索引进行非常慢的提取
提问于 2017-02-09 04:36:16
我在Centos 6.7上运行PostgreSQL9.4。其中一个表包含数百万条记录，这是DDL：
CREATE TABLE domain.examples (
id SERIAL,
sentence VARCHAR,
product_id BIGINT,
site_id INTEGER,
time_stamp BIGINT,
category_id INTEGER,
CONSTRAINT examples_pkey PRIMARY KEY(id)
)
WITH (oids = false);
CREATE INDEX examples_categories ON domain.examples
USING btree (category_id);
CREATE INDEX examples_site_idx ON domain.examples
USING btree (site_id);使用数据的应用程序使用分页来执行此操作，因此我们将获取1000条记录。但是，即使通过索引列进行提取，提取时间也非常慢：
explain analyze
select *
from domain.examples e
where e.category_id = 105154
order by id asc
limit 1000;
Limit (cost=0.57..331453.23 rows=1000 width=280) (actual time=2248261.276..2248296.600 rows=1000 loops=1)
-> Index Scan using examples_pkey on examples e (cost=0.57..486638470.34 rows=1468199 width=280) (actual time=2248261.269..2248293.705 rows=1000 loops=1)
Filter: (category_id = 105154)
Rows Removed by Filter: 173306740
Planning time: 70.821 ms
Execution time: 2248328.457 ms是什么导致查询速度很慢？如何改进这一点呢？
谢谢!
回答 2
Stack Overflow用户
回答已采纳
发布于 2017-02-09 16:06:18
您可以在category_id和id这两个字段上创建索引：
CREATE INDEX examples_site_idx2 ON domain.examples
USING btree (category_id, id);我尝试使用包含3,000,000行的查询来解释分析。
使用旧索引：
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.43..9234.56 rows=1000 width=60) (actual time=0.655..597.193 rows=322 loops=1)
-> Index Scan using examples_pkey on examples e (cost=0.43..138512.43 rows=15000 width=60) (actual time=0.654..597.142 rows=322 loops=1)
Filter: (category_id = 105154)
Rows Removed by Filter: 2999678
Planning time: 2.295 ms
Execution time: 597.257 ms
(6 rows)使用新索引：
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.43..2585.13 rows=1000 width=60) (actual time=0.027..28.814 rows=322 loops=1)
-> Index Scan using examples_site_idx2 on examples e (cost=0.43..38770.93 rows=15000 width=60) (actual time=0.026..28.777 rows=322 loops=1)
Index Cond: (category_id = 105154)
Planning time: 1.471 ms
Execution time: 28.860 ms
(5 rows)Stack Overflow用户
发布于 2017-02-09 05:59:28
这不是你想要的计划，postgresql正在扫描整个索引examples_pkey，并用条件category_id = 105154过滤掉记录，你可以尝试用ANALYZE或使用系统GUC(我真的不推荐)来获得更好的表统计信息，让规划者挑选正确的索引。
或者，如果category_id = 105154的行数不是太高，我建议先使用CTE，这样计划器就会被强制使用examples_categories索引；
with favorite_category as (
select *
from domain.examples e
where e.category_id = 105154)
select *
from favorite_category
order by id asc
limit 1000;这将使用category_id = 105154获取所有记录，并按id在内存中进行排序(如果获取的大小小于您的工作内存，请使用show work_mem;查看。默认值为4MB)。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/42123105
复制相关文章
相似问题
腾讯云开发者
