10月24日 同事让我优化条SQL语句，卫建委项目，客户说在ORACLE 5s 能出结果，在我们数据库跑出结果要30s。
SQL、执行计划、索引如下：
SELECT ORDER_CREATE_TIME,
MEDICAL_INSTITUTION_NAME,
PRESCRIPTION_NUMBER,
DBMS_RANDOM.value db
FROM TB_CIS_OP_PRESCRIPTION where TO_CHAR1(ORDER_CREATE_TIME, 'YYYYMM') = '202207'
AND PRESCRIPTION_NUMBER IS NOT NULL AND
MEDICAL_INSTITUTION_NAME IN ('广州医科大学附属第一医院', '广州医科大学附属第二医院',
'广州医科大学附属第三医院', '广州医科大学附属第四医院','广州医科大学附属第五医院',
'广州医科大学附属肿瘤医院', '广州医科大学附属口腔医院', '广州市第一人民医院','广州市妇女儿童医疗中心',
'广州医科大学附属中医医院', '广州市红十字会医院', '广州医科大学附属市八医院',
'广州市胸科医院', '广州医科大学附属脑科医院', '广州市第十二人民医院', '广州市皮肤病防治所')
GROUP BY ORDER_CREATE_TIME, MEDICAL_INSTITUTION_NAME, PRESCRIPTION_NUMBER
ORDER BY DBMS_RANDOM.value limit 10;
索引：
"index_ORDER_CREATE_TIME" btree (to_char1(ORDER_CREATE_TIME, 'YYYYMM'::text))
"INDEX_PRESCRIPTION_NUMBER" btree (PRESCRIPTION_NUMBER)
"INX_CFZB" btree (ORDER_CREATE_TIME, MEDICAL_INSTITUTION_NAME, PRESCRIPTION_NUMBER)
"INX_CFZB_CFH" btree (PRESCRIPTION_NUMBER)
"INX_CFZB_RQ" btree (ORDER_CREATE_TIME)
"INX_CFZB_XM" btree (MEDICAL_INSTITUTION_NAME)
"inx_cfzb3" btree (MEDICAL_INSTITUTION_NAME, ORDER_CREATE_TIME, PRESCRIPTION_NUMBER)
访问方法 heap
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
Limit (cost=228834.94..228834.97 rows=10 width=55) (actual time=30088.986..30088.990 rows=10 loops=1)
-> Sort (cost=228834.94..228857.37 rows=8970 width=55) (actual time=30088.985..30088.987 rows=10 loops=1)
Sort Key: (public.dbms_random.value())
Sort Method: top-N heapsort Memory: 27kB
-> Group (cost=228551.25..228641.11 rows=8970 width=55) (actual time=22587.142..29889.206 rows=469003 loops=1)
Group Key: ORDER_CREATE_TIME, MEDICAL_INSTITUTION_NAME, PRESCRIPTION_NUMBER
-> Sort (cost=228551.25..228573.71 rows=8986 width=47) (actual time=22586.990..24016.910 rows=2305236 loops=1)
Sort Key: ORDER_CREATE_TIME, MEDICAL_INSTITUTION_NAME, PRESCRIPTION_NUMBER
Sort Method: external merge Disk: 127696kB
-> Bitmap Heap Scan on TB_CIS_OP_PRESCRIPTION (cost=145362.24..227961.16 rows=8986 width=47) (actual time=4556.222..6904.6
67 rows=2305236 loops=1)
Recheck Cond: ((to_char1(ORDER_CREATE_TIME, 'YYYYMM'::text) = '202207'::text) AND ((MEDICAL_INSTITUTION_NAME)::text =
ANY ('{广州医科大学附属第一医院,广州医科大学附属第二医院,广州医科大学附属第三医院,广州医科大学附属第四医院,广州医科大学附属第五医院,广州医科大学
属肿瘤医院,广州医科大学附属口腔医院,广州市第一人民医院,广州市妇女儿童医疗中心,广州医科大学附属中医医院,广州市红十字会医院,广州医科大学附属市八医
,广州市胸科医院,广州医科大学附属脑科医院,广州市第十二人民医院,广州市皮肤病防治所}'::text[])))
Filter: (PRESCRIPTION_NUMBER IS NOT NULL)
Rows Removed by Filter: 2355256
Heap Blocks: exact=362865
-> BitmapAnd (cost=145362.24..145362.24 rows=23207 width=0) (actual time=4435.632..4435.633 rows=0 loops=1)
-> Bitmap Index Scan on idx_func_ORDER_CREATE_TIME (cost=0.00..2133.70 rows=114818 width=0) (actual time=3400.
155..3400.155 rows=22230320 loops=1)
Index Cond: (to_char1(ORDER_CREATE_TIME, 'YYYYMM'::text) = '202207'::text)
-> Bitmap Index Scan on INX_CFZB_XM (cost=0.00..143223.80 rows=4641442 width=0) (actual time=951.636..951.636
rows=4809588 loops=1)
Index Cond: ((MEDICAL_INSTITUTION_NAME)::text = ANY ('{广州医科大学附属第一医院,广州医科大学附属第二医院,
州医科大学附属第三医院,广州医科大学附属第四医院,广州医科大学附属第五医院,广州医科大学附属肿瘤医院,广州医科大学附属口腔医院,广州市第一人民医院,广
市妇女儿童医疗中心,广州医科大学附属中医医院,广州市红十字会医院,广州医科大学附属市八医院,广州市胸科医院,广州医科大学附属脑科医院,广州市第十二人民
院,广州市皮肤病防治所}'::text[]))
Planning Time: 1.068 ms
Execution Time: 30091.010 ms
(21 行记录)
可以看到
Index Cond: ((MEDICAL_INSTITUTION_NAME)::text = ANY ('{广州医科大学附属第一医院,广州医科大学附属第二医院,
州医科大学附属第三医院,广州医科大学附属第四医院,广州医科大学附属第五医院,广州医科大学附属肿瘤医院,广州医科大学附属口腔医院,广州市第一人民医院,广
市妇女儿童医疗中心,广州医科大学附属中医医院,广州市红十字会医院,广州医科大学附属市八医院,广州市胸科医院,广州医科大学附属脑科医院,广州市第十二人民
院,广州市皮肤病防治所}'::text[]))
这段谓词条件在索引过滤了一遍，然后回表又过滤了一遍
Recheck Cond: ((to_char1(ORDER_CREATE_TIME, 'YYYYMM'::text) = '202207'::text) AND ((MEDICAL_INSTITUTION_NAME)::text =
ANY ('{广州医科大学附属第一医院,广州医科大学附属第二医院,广州医科大学附属第三医院,广州医科大学附属第四医院,广州医科大学附属第五医院,广州医科大学
属肿瘤医院,广州医科大学附属口腔医院,广州市第一人民医院,广州市妇女儿童医疗中心,广州医科大学附属中医医院,广州市红十字会医院,广州医科大学附属市八医
,广州市胸科医院,广州医科大学附属脑科医院,广州市第十二人民医院,广州市皮肤病防治所}'::text[])))
-> Group (cost=228551.25..228641.11 rows=8970 width=55) (actual time=22587.142..29889.206 rows=469003 loops=1)
Group Key: ORDER_CREATE_TIME, MEDICAL_INSTITUTION_NAME, PRESCRIPTION_NUMBER
-> Sort (cost=228551.25..228573.71 rows=8986 width=47) (actual time=22586.990..24016.910 rows=2305236 loops=1)
Sort Key: ORDER_CREATE_TIME, MEDICAL_INSTITUTION_NAME, PRESCRIPTION_NUMBER
Sort Method: external merge Disk: 127696kB
还有排序分组消耗的资源也很多。
加个索引再跑
create index idx_func_ORDER_CREATE_TIME1 on
TB_CIS_OP_PRESCRIPTION(to_char1(ORDER_CREATE_TIME,'YYYYMM'::text),MEDICAL_INSTITUTION_NAME,PRESCRIPTION_NUMBER);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------
Sort (cost=34853.85..34876.27 rows=8970 width=55) (actual time=15287.260..15356.999 rows=469003 loops=1)
Sort Key: (public.dbms_random.value())
Sort Method: quicksort Memory: 74123kB
-> HashAggregate (cost=34175.23..34264.93 rows=8970 width=55) (actual time=10061.590..14945.169 rows=469003 loops=1)
Group Key: ORDER_CREATE_TIME, MEDICAL_INSTITUTION_NAME, PRESCRIPTION_NUMBER
-> Bitmap Heap Scan on TB_CIS_OP_PRESCRIPTION (cost=443.65..34107.83 rows=8986 width=47) (actual time=984.638..1698.971 rows=2305236 l
oops=1)
Recheck Cond: ((to_char1(ORDER_CREATE_TIME, 'YYYYMM'::text) = '202207'::text) AND ((MEDICAL_INSTITUTION_NAME)::text = ANY ('{广州
科大学附属第一医院,广州医科大学附属第二医院,广州医科大学附属第三医院,广州医科大学附属第四医院,广州医科大学附属第五医院,广州医科大学附属肿瘤医院,
州医科大学附属口腔医院,广州市第一人民医院,广州市妇女儿童医疗中心,广州医科大学附属中医医院,广州市红十字会医院,广州医科大学附属市八医院,广州市胸科
院,广州医科大学附属脑科医院,广州市第十二人民医院,广州市皮肤病防治所}'::text[])) AND (PRESCRIPTION_NUMBER IS NOT NULL))
Heap Blocks: exact=203057
-> Bitmap Index Scan on idx_func_ORDER_CREATE_TIME1 (cost=0.00..441.40 rows=8986 width=0) (actual time=931.849..931.849 rows=230
5236 loops=1)
Index Cond: ((to_char1(ORDER_CREATE_TIME, 'YYYYMM'::text) = '202207'::text) AND ((MEDICAL_INSTITUTION_NAME)::text = ANY ('{
州医科大学附属第一医院,广州医科大学附属第二医院,广州医科大学附属第三医院,广州医科大学附属第四医院,广州医科大学附属第五医院,广州医科大学附属肿瘤医
院,广州医科大学附属口腔医院,广州市第一人民医院,广州市妇女儿童医疗中心,广州医科大学附属中医医院,广州市红十字会医院,广州医科大学附属市八医院,广州市
胸科医院,广州医科大学附属脑科医院,广州市第十二人民医院,广州市皮肤病防治所}'::text[])) AND (PRESCRIPTION_NUMBER IS NOT NULL))
Planning Time: 1.545 ms
Execution Time: 15376.727 ms
(12 行记录)
还是有回表，但是可以看到刚才创建的 idx_func_ORDER_CREATE_TIME1 索引已经用上，SQL执行也从30S 降到15S ，分组还是资源消耗大头。
现在的想法就是所有的过滤条件+排序在索引叶子节点完成。
现在又建了个索引再跑SQL语句
create index "idx_func_ORDER_CREATE_TIME2" btree (to_char1(ORDER_CREATE_TIME, 'YYYYMM'::text), MEDICAL_INSTITUTION_NAME,
PRESCRIPTION_NUMBER, ORDER_CREATE_TIME, MEDICAL_INSTITUTION_NAME, PRESCRIPTION_NUMBER);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------
Sort (cost=257746.93..260570.09 rows=1129265 width=55) (actual time=10216.138..10272.409 rows=375661 loops=1)
Sort Key: (public.dbms_random.value())
Sort Method: quicksort Memory: 64106kB
-> HashAggregate (cost=132923.89..144216.54 rows=1129265 width=55) (actual time=6085.459..9953.784 rows=375661
loops=1)
Group Key: ORDER_CREATE_TIME, MEDICAL_INSTITUTION_NAME, PRESCRIPTION_NUMBER
-> Index Only Scan using idx_func_ORDER_CREATE_TIME2 on TB_CIS_OP_PRESCRIPTION (cost=0.69..121648.65 rows
=1503365 width=47) (actual time=0.268..1096.261 rows=1505284 loops=1)
Index Cond: (((to_char1(ORDER_CREATE_TIME, 'YYYYMM'::text)) = '202207'::text) AND (MEDICAL_INSTITUTIO
N_NAME = ANY ('{广州医科大学附属第一医院,广州医科大学附属第二医院,广州医科大学附属第三医院,广州医科大学附属第四医院,
广州医科大学附属第五医院,广州医科大学附属肿瘤医院,"广州医科大学附属口腔
医院",广州市第一人民医院,广州市妇女儿童医疗中心,广州医科大学附属中医医院,广州市红十字会医院,"
广州医科大学附属市八医院",广州市胸科医院,广州医科大学附属脑科医院,广州市第十二人民医院,"广州市
皮肤病防治所"}'::text[])) AND (PRESCRIPTION_NUMBER IS NOT NULL))
Heap Fetches: 0
Planning Time: 1.252 ms
Execution Time: 10289.439 ms
(13 行记录)
可以看到SQL执行时间从15S 降到10S，这条SQL无继续优化空间。
回到最开始的问题，为什么这条语句在ORACLE上执行5s 就能出结果，而金仓数据库需要30s ，
语句里面是有 一堆的谓词过滤条件 加上
GROUP BY ORDER_CREATE_TIME, MEDICAL_INSTITUTION_NAME, PRESCRIPTION_NUMBER
这种分组语句导致。
因为在ORACLE上有 索引快速扫描(index fast full scan) 多块读。
而 postgresql 上任何的索引扫描只能单块读。