skytf=> /d tmp_user Table"skytf.tmp_user" Column | Type | Modifiers --------------+--------------------------------+----------------------------------------- user_id | integer | not null account | character varying(32) | not null nickname | character varying(64) | sex | smallint | default1 birthday | character varying(20) | default '1991-01-01'::character varying email | character varying(20) | id_num | character varying(40) | mobile | character varying(20) | country | character varying(32) | default '中国'::character varying province | character varying(20) | city | character varying(16) | signature | character varying(128) | head_icon | integer | default0 state | integer | default0 reg_time | timestamp(0) without time zone | country_code | integer | default86 Indexes: "tmp_user_pkey" PRIMARY KEY, btree (user_id) "tbl_mpc_user_info_username_key" UNIQUE, btree (account) "tmp_user_birthday" btree (birthday) "tmp_user_city_index" btree (city) "tmp_user_nickname_index" btree (nickname) "tmp_user_province_index" btree (province) "tmp_user_reg_time_idx" btree (reg_time) "tmp_user_sex_index" btree (sex, city, birthday, country, id_num) "tmp_user_signature" btree (signature)
备注：在字段 province 上有索引。
表大小
1 2 3 4 5
skytf=> /dt+ tmp_user List of relations Schema | Name | Type | Owner | Size | Description ----------------+---------------------+-------+----------------+---------+------------- skytf | tmp_user | table | skytf | 2628 MB |
优化前的执行计划
1 2 3 4 5 6 7 8 9
skytf=> explain analyze select user_id, account, nickname, sex, city, head_icon, country_code from tmp_user skytf-> where 1=1and user_id <> 226102033andsex=1and province like '%广西%' limit 20; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ Limit (cost=0.00..511445.19rows=1width=61) (actual time=514.228..4489.034rows=3loops=1) -> Seq Scan on tmp_user (cost=0.00..511445.19rows=1width=61) (actual time=514.225..4489.025rows=3loops=1) Filter: ((user_id <> 226102033) AND ((province)::text ~~ '%广西%'::text) AND (sex = 1)) Total runtime: 4489.073 ms (4 rows)
优化后的执行计划
1 2 3 4 5 6 7 8 9 10
skytf=> explain analyze select user_id, account, nickname, sex, city, head_icon, country_code from tmp_user skytf-> where1=1and user_id <> 226102033and sex=1and province like '山东%' limit 20; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..6.36rows=1 width=61) (actual time=0.104..0.312rows=1 loops=1) -> Index Scan using tmp_user_province_index on tmp_user (cost=0.00..6.36rows=1 width=61) (actual time=0.103..0.311rows=1 loops=1) Index Cond: (((province)::text >= '山东'::text) AND ((province)::text < '山丝'::text)) Filter: ((user_id <> 226102033) AND ((province)::text ~~ '山东%'::text) AND (sex = 1)) Total runtime: 0.342 ms
