Postgresql 对索引列的查询非常慢

Question

我有一个包含 5000 万行的表格。名为 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_userTable "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::smallintIndexes:    "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

感谢@Kouber Saparev

Answer 1

尝试制作部分索引。

CREATE INDEX u_user_u_sphinx_idx ON u_user (u_sphinx) WHERE u_sphinx = 1;