同事找我优化SQL，同一条SQL语句LIKE过滤条件不同，执行时间差别很多，废话不说安排一下。
LIKE过滤条件执行快的SQL和执行计划：
EXPLAIN ANALYZE
SELECT case_id,
cate_id,
cate_name,
view_url,
proc_ins_id,
create_user_id,
current_user_id,
title,
emergency,
dept_id,
handler_id,
handle_date,
create_date,
end_date,
proc_ins_state,
hint
FROM wfv_pass_list H
WHERE handler_id = '8feae683-f741-45de-b430-7db24b4f7660'
AND title like '%通报%'
ORDER BY handle_date desc nulls last, emergency DESC;
Sort (cost=83260.22..83263.92 rows=1479 width=463) (actual time=203.872..205.642 rows=1750 loops=1)
Sort Key: H.handle_date DESC NULLS LAST, P.emer_level DESC
Sort Method: quicksort Memory: 968kB
-> Gather (cost=12513.91..83182.35 rows=1479 width=463) (actual time=146.637..204.384 rows=1750 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Hash Join (cost=11513.91..69536.90 rows=616 width=452) (actual time=140.984..182.745 rows=583 loops=3)
Hash Cond: (P.cate_id = C.id)
-> Parallel Hash Join (cost=11506.98..69528.32 rows=616 width=408) (actual time=140.808..182.364 rows=583 loops=3)
Hash Cond: (H.proc_ins_id = P.id)
-> Parallel Bitmap Heap Scan on wf_proc_ins_handlers H (cost=443.78..58432.47 rows=12438 width=156) (actual time=6.527..46.555 rows=10375 loops=3)
Recheck Cond: (handler_id = '8feae683-f741-45de-b430-7db24b4f7660'::bpcharbyte)
Filter: ((state = 'A'::bpcharbyte) AND (category = 'H'::bpcharbyte))
Heap Blocks: exact=2799
-> Bitmap Index Scan on PROC_INS_HANDLERS_HANDLER_ID (cost=0.00..436.31 rows=29851 width=0) (actual time=7.405..7.405 rows=31125 loops=1)
Index Cond: (handler_id = '8feae683-f741-45de-b430-7db24b4f7660'::bpcharbyte)
-> Parallel Hash (cost=10935.95..10935.95 rows=10180 width=289) (actual time=132.111..132.112 rows=4266 loops=3)
Buckets: 32768 Batches: 1 Memory Usage: 4480kB
-> Parallel Seq Scan on wf_proc_instances P (cost=0.00..10935.95 rows=10180 width=289) (actual time=0.049..126.563 rows=4266 loops=3)
Filter: ((title)::text ~~ '%通报%'::text)
Rows Removed by Filter: 160050
-> Hash (cost=4.19..4.19 rows=219 width=81) (actual time=0.079..0.080 rows=221 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 33kB
-> Seq Scan on wf_file_categories C (cost=0.00..4.19 rows=219 width=81) (actual time=0.016..0.040 rows=221 loops=3)
SubPlan 1
-> Index Scan using wf_act_instances_PKEY on wf_act_instances (cost=0.43..8.45 rows=1 width=14) (actual time=0.011..0.012 rows=0 loops=1750)
Index Cond: (id = H.act_ins_id)
Planning Time: 1.185 ms
Execution Time: 205.909 ms
LIKE过滤条件执行慢的SQL和执行计划：
EXPLAIN ANALYZE
SELECT case_id,
cate_id,
cate_name,
view_url,
proc_ins_id,
create_user_id,
current_user_id,
title,
emergency,
dept_id,
handler_id,
handle_date,
create_date,
end_date,
proc_ins_state,
hint
FROM wfv_pass_list H
WHERE handler_id = '8feae683-f741-45de-b430-7db24b4f7660'
AND title like '%表扬%'
ORDER BY handle_date desc nulls last, emergency DESC;
Sort (cost=21036.84..21036.85 rows=3 width=463) (actual time=2023.884..2023.948 rows=139 loops=1)
Sort Key: H.handle_date DESC NULLS LAST, P.emer_level DESC
Sort Method: quicksort Memory: 97kB
-> Gather (cost=1449.85..21036.82 rows=3 width=463) (actual time=9.150..2023.615 rows=139 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Nested Loop (cost=449.85..20011.17 rows=1 width=452) (actual time=25.891..2010.559 rows=46 loops=3)
-> Nested Loop (cost=449.71..20010.66 rows=1 width=408) (actual time=25.841..2010.065 rows=46 loops=3)
-> Parallel Seq Scan on wf_proc_instances P (cost=0.00..10935.95 rows=20 width=289) (actual time=0.616..138.214 rows=185 loops=3)
Filter: ((title)::text ~~ '%表扬%'::text)
Rows Removed by Filter: 164131
-> Bitmap Heap Scan on wf_proc_ins_handlers H (cost=449.71..453.73 rows=1 width=156) (actual time=10.097..10.097 rows=0 loops=556)
Recheck Cond: ((proc_ins_id = P.id) AND (handler_id = '8feae683-f741-45de-b430-7db24b4f7660'::bpcharbyte))
Filter: ((state = 'A'::bpcharbyte) AND (category = 'H'::bpcharbyte))
Heap Blocks: exact=29
-> BitmapAnd (cost=449.71..449.71 rows=1 width=0) (actual time=10.093..10.093 rows=0 loops=556)
-> Bitmap Index Scan on PUBLIC_WF_PROC_INS_HANDLERS_INDEX_7 (cost=0.00..5.68 rows=166 width=0) (actual time=0.042..0.042 rows=77 loops=556)
Index Cond: (proc_ins_id = P.id)
-> Bitmap Index Scan on PROC_INS_HANDLERS_HANDLER_ID (cost=0.00..436.31 rows=29851 width=0) (actual time=9.495..9.495 rows=31125 loops=556)
Index Cond: (handler_id = '8feae683-f741-45de-b430-7db24b4f7660'::bpcharbyte)
-> Index Scan using wf_file_categories_PKEY on wf_file_categories C (cost=0.14..0.50 rows=1 width=81) (actual time=0.007..0.007 rows=1 loops=139)
Index Cond: (id = P.cate_id)
SubPlan 1
-> Index Scan using wf_act_instances_PKEY on wf_act_instances (cost=0.43..8.45 rows=1 width=14) (actual time=0.014..0.014 rows=1 loops=139)
Index Cond: (id = H.act_ins_id)
Planning Time: 1.753 ms
Execution Time: 2024.430 ms
wfv_pass_list 表是个视图，定义如下，复制的时候有乱码，问题不大：
è§å¾ "public.WFV_PASS_LIST"
æ ä½ | ç±»å | æ ¡å¯¹è§å | å¯ç©ºç | é¢è®¾ | åå¨ | æè¿°
-----------------+--------------------------------+----------+--------+------+----------+------
case_id | character(36 byte) | | | | extended |
cate_id | character(36 byte) | | | | extended |
CATE_NAME | character varying(100 char) | | | | extended |
VIEW_URL | character varying(1024 char) | | | | extended |
PROC_INS_ID | character(36 byte) | | | | extended |
create_user_id | character(36 byte) | | | | extended |
current_user_id | character(36 byte) | | | | extended |
title | character varying(600 char) | | | | extended |
EMERGENCY | character(1 byte) | | | | extended |
dept_id | character(36 byte) | | | | extended |
handler_id | character(36 byte) | | | | extended |
handle_date | timestamp(3) without time zone | | | | plain |
create_date | timestamp(3) without time zone | | | | plain |
end_date | timestamp(3) without time zone | | | | plain |
PROC_INS_STATE | character(1 byte) | | | | extended |
HINT | character varying(60 byte) | | | | extended |SELECT P.case_id,
P.cate_id,
C.name AS CATE_NAME,
C.url AS VIEW_URL,
P.id AS PROC_INS_ID,
P.create_user_id,
P.current_user_id,
P.title,
P.emer_level AS EMERGENCY,
H.dept_id,
H.handler_id,
H.handle_date,
P.create_date,
P.end_date,
P.state AS PROC_INS_STATE,
( SELECT wf_act_instances.hint
FROM wf_act_instances
WHERE wf_act_instances.id = H.act_ins_id) AS HINT
FROM wf_proc_ins_handlers H,
wf_proc_instances P,
wf_file_categories C
WHERE H.proc_ins_id = P.id AND H.state = 'A'::bpchar::bpcharbyte AND H.category = 'H'::bpchar::bpcharbyte AND C.id = P.cate_id;
可以看到，两条SQL是除了LIKE模糊查询的过滤条件不一样，其他的写法是完全一致，
where title like '%通报%' 执行时间需要205ms，返回数据 1750 行，
where title like '%表扬%' 执行时间需要2024ms，返回数据 139 行，
正常情况下应该是谓词过滤条件为 '%表扬%' 的SQL语句执行速度更快才是，毕竟返回的数据更少，事实上这个过滤条件比前者慢了10倍。
前者执行计划：
后者执行计划：
通过对比发现后者SQL语句的表关联条件 proc_ins_id = P.id 竟然又 Recheck Cond 一次（回表），而前者的表关联条件是没有进行回表的 Hash Cond: (H.proc_ins_id = P.id)，确定找到慢的地方。
增加联合索引优化：
create index idx_wf_proc_ins_handlers_1_2 on wf_proc_ins_handlers(proc_ins_id,handler_id);
优化后执行SQL语句：
EXPLAIN ANALYZE
SELECT case_id,
cate_id,
cate_name,
view_url,
proc_ins_id,
create_user_id,
current_user_id,
title,
emergency,
dept_id,
handler_id,
handle_date,
create_date,
end_date,
proc_ins_state,
hint
FROM wfv_pass_list H
WHERE handler_id = '8feae683-f741-45de-b430-7db24b4f7660'
AND title like '%表扬%'
ORDER BY handle_date desc nulls last, emergency DESC
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=12129.97..12129.97 rows=3 width=464) (actual time=159.627..162.448 rows=139 loops=1)
Sort Key: H.handle_date DESC NULLS LAST, P.emer_level DESC
Sort Method: quicksort Memory: 97kB
-> Gather (cost=1000.58..12129.94 rows=3 width=464) (actual time=1.069..162.167 rows=139 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Nested Loop (cost=0.58..11104.29 rows=1 width=453) (actual time=1.615..148.219 rows=46 loops=3)
-> Nested Loop (cost=0.43..11103.79 rows=1 width=409) (actual time=1.585..148.059 rows=46 loops=3)
-> Parallel Seq Scan on wf_proc_instances P (cost=0.00..10934.44 rows=20 width=290) (actual time=1.197..145.137 rows=185 loops=3)
Filter: ((title)::text ~~ '%表扬%'::text)
Rows Removed by Filter: 164131
-> Index Scan using idx_wf_proc_ins_handlers_1_2 on wf_proc_ins_handlers H (cost=0.43..8.46 rows=1 width=156) (actual time=0.015..0.015 rows=0 loops=556)
Index Cond: ((proc_ins_id = P.id) AND (handler_id = '8feae683-f741-45de-b430-7db24b4f7660'::bpcharbyte))
Filter: ((state = 'A'::bpcharbyte) AND (category = 'H'::bpcharbyte))
-> Index Scan using wf_file_categories_PKEY on wf_file_categories C (cost=0.14..0.50 rows=1 width=81) (actual time=0.003..0.003 rows=1 loops=139)
Index Cond: (id = P.cate_id)
SubPlan 1
-> Index Scan using wf_act_instances_PKEY on wf_act_instances (cost=0.43..8.45 rows=1 width=14) (actual time=0.013..0.013 rows=1 loops=139)
Index Cond: (id = H.act_ins_id)
Planning Time: 1.443 ms
Execution Time: 162.906 ms
(21 行记录)
EXPLAIN ANALYZE
SELECT case_id,
cate_id,
cate_name,
view_url,
proc_ins_id,
create_user_id,
current_user_id,
title,
emergency,
dept_id,
handler_id,
handle_date,
create_date,
end_date,
proc_ins_state,
hint
FROM wfv_pass_list H
WHERE handler_id = '8feae683-f741-45de-b430-7db24b4f7660'
AND title like '%通报%'
ORDER BY handle_date desc nulls last, emergency DESC;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=16733.04..16733.23 rows=73 width=464) (actual time=246.592..252.130 rows=1750 loops=1)
Sort Key: H.handle_date DESC NULLS LAST, P.emer_level DESC
Sort Method: quicksort Memory: 968kB
-> Gather (cost=1000.58..16730.78 rows=73 width=464) (actual time=0.596..250.878 rows=1750 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Nested Loop (cost=0.58..15106.63 rows=30 width=453) (actual time=0.435..228.229 rows=583 loops=3)
-> Nested Loop (cost=0.43..15101.36 rows=30 width=409) (actual time=0.403..224.391 rows=583 loops=3)
-> Parallel Seq Scan on wf_proc_instances P (cost=0.00..10934.44 rows=499 width=290) (actual time=0.062..135.724 rows=4266 loops=3)
Filter: ((title)::text ~~ '%通报%'::text)
Rows Removed by Filter: 160050
-> Index Scan using idx_wf_proc_ins_handlers_1_2 on wf_proc_ins_handlers H (cost=0.43..8.34 rows=1 width=156) (actual time=0.021..0.021 rows=0 loops=12797)
Index Cond: ((proc_ins_id = P.id) AND (handler_id = '8feae683-f741-45de-b430-7db24b4f7660'::bpcharbyte))
Filter: ((state = 'A'::bpcharbyte) AND (category = 'H'::bpcharbyte))
-> Index Scan using wf_file_categories_PKEY on wf_file_categories C (cost=0.14..0.18 rows=1 width=81) (actual time=0.006..0.006 rows=1 loops=1750)
Index Cond: (id = P.cate_id)
SubPlan 1
-> Index Scan using wf_act_instances_PKEY on wf_act_instances (cost=0.43..8.45 rows=1 width=14) (actual time=0.015..0.015 rows=0 loops=1750)
Index Cond: (id = H.act_ins_id)
Planning Time: 1.551 ms
Execution Time: 252.611 ms
(21 行记录)
可以看到，两个谓词过滤条件不同的SQL语句执行时间已经相差无几，在正式环境上创建索引后SQL运行速度有明显提升，本案例已经优化完毕。
附带上位图索引扫描（Bitmap Index Scan）和回表的简单原理介绍：
1、PG的位图扫描基于是B树索引，在读取数据量比较小时，index scan比较合适，在读取数据量比较大的情况下，bitmap index scan会更有优势。
2、有时候创建组合索引也会用到位图索引扫描。
3、Recheck Cond:回表：
3.1、可以看到在对IDX_TEST_OBJECT_NAME206上执行了一个Bitmap Index Scan，由于Bitmap Index Scan记录的是符合条件的记录所在的block，而非记录的指针,通过类似于Oracle位图索引的检索模式进行数据的筛选，
然后对这些位图信息指向的block排序后再进行回表（查询），Bitmap Index Scan之后有一个Recheck Cond是因为解析block的时候需要Recheck 。
3.2、回表多了会有性能瓶颈的，这个时候想办法让SQL走索引查找，或者过滤多点条件，或者走组合索引。
标签：rows,..,time,管理局,案例,cost,SQL,loops,id From： https://www.cnblogs.com/yuzhijian/p/17041509.html