Postgresql在索引列上查询非常慢
我有50万行的桌子。 一列名为u_sphinx是非常重要的可用值是1,2,3。 现在所有的行都有值3,但是,当我检查新行(u_sphinx = 1)时,查询非常缓慢。 什么可能是错的? 也许索引坏了? 服务器:Debian,8GB 4x Intel(R)Xeon(R)CPU E3-1220 V2 @ 3.10GHz
表结构:
base=> d u_user Table "public.u_user" Column | Type | Modifiers u_ip | character varying | u_agent | text | u_agent_js | text | u_resolution_id | integer | u_os | character varying | u_os_id | smallint | u_platform | character varying | u_language | character varying | u_language_id | smallint | u_language_js | character varying | u_cookie | smallint | u_java | smallint | u_color_depth | integer | u_flash | character varying | u_charset | character varying | u_doctype | character varying | u_compat_mode | character varying | u_sex | character varying | u_age | character varying | u_theme | character varying | u_behave | character varying | u_targeting | character varying | u_resolution | character varying | u_user_hash | bigint | u_tech_hash | character varying | u_last_target_data_time | integer | u_last_target_prof_time | integer | u_id | bigint | not null default nextval('u_user_u_id_seq'::regclass) u_sphinx | smallint | not null default 1::smallint Indexes: "u_user_u_id_pk" PRIMARY KEY, btree (u_id) "u_user_hash_index" btree (u_user_hash) "u_user_u_sphinx_ind" btree (u_sphinx)
慢查询:
base=> explain analyze SELECT u_id FROM u_user WHERE u_sphinx = 1 LIMIT 1; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..0.15 rows=1 width=8) (actual time=485146.252..485146.252 rows=0 loops=1) -> Seq Scan on u_user (cost=0.00..3023707.80 rows=19848860 width=8) (actual time=485146.249..485146.249 rows=0 loops=1) Filter: (u_sphinx = 1) Rows Removed by Filter: 23170476 Total runtime: 485160.241 ms (5 rows)
解决了:
添加部分索引后
base=> explain analyze SELECT u_id FROM u_user WHERE u_sphinx = 1 LIMIT 1; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.27..4.28 rows=1 width=8) (actual time=0.063..0.063 rows=0 loops=1) -> Index Scan using u_user_u_sphinx_index_1 on u_user (cost=0.27..4.28 rows=1 width=8) (actual time=0.061..0.061 rows=0 loops=1) Index Cond: (u_sphinx = 1) Total runtime: 0.106 ms
Thx为@Kouber Saparev
尝试制作部分索引。
CREATE INDEX u_user_u_sphinx_idx ON u_user (u_sphinx) WHERE u_sphinx = 1;
您的查询计划看起来像DB正在处理查询,就好像1在数据库中非常常见,以便挖掘到一个或两个磁盘以更好地识别相关行,而不是增加犁过的开销索引并在随机磁盘页面中查找行。
这可能表示您忘记运行以分析表格,以便规划者具有适当的统计信息:
analyze u_user
链接地址: http://www.djcxy.com/p/86077.html