postgresql 最近优化的SQL集合案例、（不写过程了只记录案例，PG优化器问题还是不少）
案例1：
-- 原SQL + 执行计划： explain analyze SELECT G.PID, G.FLOW_ID, G.STATUS, G.ID, AAAAAA.INFO_ID, G.CREATE_UNAME, AAAAAA.FLOW_TIME, G.CREATE_DEPTNAME, G.BT, G.MODULE_NAME, G.MODULE_ID, G.WH, AAAAAA.PRIVATE_SN, G.HANDLE_USERS, G.JJCD, G.JJCD_TEXT, G.MJ, G.MJ_TEXT FROM AAAAAA INNER JOIN BBBBBBB G ON G.ID = AAAAAA.INFO_ID WHERE AAAAAA.FLOW_STATUS = '1' AND AAAAAA.IS_FLOW = '1' AND AAAAAA.USER_ID = '210322182658hbwJJvwlxItCjUQ8PeM' AND G.ROWSTATE >- 1 AND G.MODULE_ID IN('1906261422364oh1kT8fYFKA3iaZcfT') ORDER BY AAAAAA.FLOW_TIME DESC LIMIT 30; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -- Limit (cost=0.99..41702.40 rows=30 width=484) (actual time=31582.593..31582.593 rows=0 loops=1) -> Nested Loop (cost=0.99..5127883.64 rows=3689 width=484) (actual time=31582.592..31582.592 rows=0 loops=1) -> Index Scan Backward using idx_AAAAAA_FLOW_TIME on AAAAAA (cost=0.43..2668871.97 rows=1142813 width=41) (actual time=0.024..17088.849 rows=1132689 loops=1 ) Filter: ((FLOW_STATUS = '1'::NUMERIC) AND (IS_FLOW = '1'::NUMERIC) AND (USER_ID = '210322182658hbwJJvwlxItCjUQ8PeM'::CHARACTER VARYING)) Rows Removed by Filter: 12796824 -> Index Scan using BBBBBBB_PKEY on BBBBBBB G (cost=0.56..2.14 rows=1 width=443) (actual time=0.013..0.013 rows=0 loops=1132689) Index Cond: (ID = AAAAAA.INFO_ID) Filter: ((ROWSTATE > '-1'::NUMERIC) AND (MODULE_ID = '1906261422364oh1kT8fYFKA3iaZcfT'::CHARACTER VARYING)) Rows Removed by Filter: 1 Planning time: 4.969 ms Execution time: 31582.652 ms (11 rows)
-- 索引优化 + 改写 SQL： create index idx_AAAAAA_n1 on AAAAAA(INFO_ID,USER_ID); create index idx_BBBBBBB_n1 on BBBBBBB(MODULE_ID,ROWSTATE); explain analyze SELECT G.PID, G.FLOW_ID, G.STATUS, G.ID, AAAAAA.INFO_ID, G.CREATE_UNAME, AAAAAA.FLOW_TIME, G.CREATE_DEPTNAME, G.BT, G.MODULE_NAME, G.MODULE_ID, G.WH, AAAAAA.PRIVATE_SN, G.HANDLE_USERS, G.JJCD, G.JJCD_TEXT, G.MJ, G.MJ_TEXT FROM AAAAAA INNER JOIN BBBBBBB G ON G.ID = AAAAAA.INFO_ID WHERE AAAAAA.FLOW_STATUS = '1' AND AAAAAA.IS_FLOW = '1' AND AAAAAA.USER_ID = '210322182658hbwJJvwlxItCjUQ8PeM' AND G.ROWSTATE >- 1 AND G.MODULE_ID IN('1906261422364oh1kT8fYFKA3iaZcfT') AND ROWNUM > 0 ORDER BY AAAAAA.FLOW_TIME DESC LIMIT 30; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=601220.27..601220.28 rows=1 width=484) (actual time=150.256..150.256 rows=0 loops=1) -> Sort (cost=601220.27..601220.28 rows=0 width=484) (actual time=150.254..150.254 rows=0 loops=1) Sort Key: AAAAAA.FLOW_TIME DESC Sort Method: quicksort Memory: 25kB -> Count (cost=1.12..601220.26 rows=0 width=484) (actual time=150.249..150.249 rows=0 loops=1) Stop Keys: (ROWNUM > 0) -> Nested Loop (cost=1.12..601183.37 rows=3689 width=484) (actual time=150.248..150.248 rows=0 loops=1) -> Index Scan using BBBBBBB_module_id on BBBBBBB G (cost=0.56..18096.56 rows=15946 width=443) (actual time=0.032..10.329 rows=8285 loops=1) Index Cond: (MODULE_ID = '1906261422364oh1kT8fYFKA3iaZcfT'::CHARACTER VARYING) Filter: (ROWSTATE > '-1'::NUMERIC) Rows Removed by Filter: 1 -> Index Scan using idx_AAAAAA_n1 on AAAAAA (cost=0.56..36.48 rows=9 width=41) (actual time=0.017..0.017 rows=0 loops=8285) Index Cond: ((INFO_ID = G.ID) AND (USER_ID = '210322182658hbwJJvwlxItCjUQ8PeM'::CHARACTER VARYING)) Filter: ((FLOW_STATUS = '1'::NUMERIC) AND (IS_FLOW = '1'::NUMERIC)) Planning time: 0.800 ms Execution time: 150.329 ms (16 rows)
这个案例 SQL开发写得有问题（ G.MODULE_ID IN('1906261422364oh1kT8fYFKA3iaZcfT') + LIMIT 30 ）点到为止不多说，而且 pg 优化器傻乎乎的，不会当成一个整体来执行，就一直往死里推。
案例2：
-- 原SQL + 执行计划 ： SELECT C.NBYJ AS CONTENT, a.swdw_name || '(' || a.CREATE_uname || ')' AS UNAME, TO_CHAR(c.ngrq, 'yyyy-mm-dd hh24:mi:ss') AS PDATE FROM CCCCCCCC a INNER JOIN EEEED d ON d.source_id = a.id AND d.module_id = '190626104707D1FgtbFlPHupVSUtLVK' INNER JOIN EEEED e ON e.source_id = d.id AND e.module_id = '1906281143542BS15J0F45WqfUr9fMV' INNER JOIN CCCCCCCC f ON f.source_id = e.id INNER JOIN EEEED c ON c.source_id = f.id AND c.module_id = '1906281143542BS15J0F45WqfUr9fMV' WHERE a.feedback_status = 1 AND C.ROWSTATE = 1 AND a.REMOTE_ID = '230620100133zXjW7irYYxRuUbIX4dN' ORDER BY C.ngrq; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------ Sort (cost=546497.75..546497.75 rows=1 width=202) (actual time=47022.684..47022.684 rows=0 loops=1) Sort Key: c.NGRQ Sort Method: quicksort Memory: 25kB -> Nested Loop (cost=398361.23..546497.74 rows=1 width=202) (actual time=47022.659..47022.659 rows=0 loops=1) -> Nested Loop (cost=398360.80..539445.12 rows=5777 width=170) (actual time=32142.716..39565.986 rows=49597 loops=1) -> Hash Join (cost=398360.37..511828.98 rows=6700 width=170) (actual time=32090.140..32437.179 rows=49662 loops=1) Hash Cond: (c.SOURCE_ID = f.ID) -> Index Scan using EEEED_MODULE_ID on EEEED c (cost=0.43..107101.65 rows=100105 width=170) (actual time=43.693..273.107 rows=100733 loops=1) Index Cond: (MODULE_ID = '1906281143542BS15J0F45WqfUr9fMV'::CHARACTER VARYING) Filter: (ROWSTATE = '1'::NUMERIC) Rows Removed by Filter: 136 -> Hash (cost=396116.32..396116.32 rows=96530 width=64) (actual time=32046.152..32046.152 rows=49350 loops=1) Buckets: 131072 Batches: 2 Memory Usage: 3343kB -> Hash Join (cost=109182.84..396116.32 rows=96530 width=64) (actual time=17722.308..32017.501 rows=49350 loops=1) Hash Cond: (f.SOURCE_ID = e.ID) -> Seq Scan on CCCCCCCC f (cost=0.00..251072.86 rows=1340086 width=64) (actual time=40.837..13732.995 rows=1340086 loops=1) -> Hash (cost=106850.85..106850.85 rows=100319 width=64) (actual time=17640.822..17640.822 rows=100869 loops=1) Buckets: 131072 Batches: 2 Memory Usage: 5730kB -> Index Scan using EEEED_MODULE_ID on EEEED e (cost=0.43..106850.85 rows=100319 width=64) (actual time=0.040..17575.199 rows=100869 loo ps=1) Index Cond: (MODULE_ID = '1906281143542BS15J0F45WqfUr9fMV'::CHARACTER VARYING) -> Index Scan using EEEED_PKEY on EEEED d (cost=0.43..4.11 rows=1 width=64) (actual time=0.142..0.143 rows=1 loops=49662) Index Cond: (ID = e.SOURCE_ID) Filter: (MODULE_ID = '190626104707D1FgtbFlPHupVSUtLVK'::CHARACTER VARYING) -> Index Scan using CCCCCCCC_PKEY on CCCCCCCC a (cost=0.43..1.21 rows=1 width=97) (actual time=0.150..0.150 rows=0 loops=49597) Index Cond: (ID = d.SOURCE_ID) Filter: ((FEEDBACK_STATUS = '1'::NUMERIC) AND (REMOTE_ID = '230620100133zXjW7irYYxRuUbIX4dN'::CHARACTER VARYING)) Rows Removed by Filter: 1 Planning time: 1044.045 ms Execution time: 47023.095 ms (29 rows)
-- 索引优化： create index idx_EEEED_source_MODULE_ID on EEEED(source_id,module_id); create index idx_CCCCCCCC_REMOTE_ID_FEEDBACK_STATUS on CCCCCCCC(REMOTE_ID,FEEDBACK_STATUS); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Sort (cost=256118.02..256118.02 rows=1 width=202) (actual time=0.079..0.079 rows=0 loops=1) Sort Key: c.NGRQ Sort Method: quicksort Memory: 25kB -> Nested Loop (cost=18.99..256118.01 rows=1 width=202) (actual time=0.073..0.073 rows=0 loops=1) -> Hash Join (cost=18.44..256116.63 rows=1 width=97) (actual time=0.073..0.073 rows=0 loops=1) Hash Cond: (f.SOURCE_ID = e.ID) -> Seq Scan on CCCCCCCC f (cost=0.00..251072.86 rows=1340086 width=64) (actual time=0.011..0.011 rows=1 loops=1) -> Hash (cost=18.42..18.42 rows=1 width=97) (actual time=0.055..0.055 rows=0 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 8kB -> Nested Loop (cost=1.53..18.42 rows=1 width=97) (actual time=0.055..0.055 rows=0 loops=1) -> Nested Loop (cost=0.98..17.03 rows=1 width=97) (actual time=0.055..0.055 rows=0 loops=1) -> Index Scan using idx_CCCCCCCC_REMOTE_ID_FEEDBACK_STATUS on CCCCCCCC a (cost=0.43..8.45 rows=1 width=97) (actual time=0.054..0.054 rows=0 loops=1) Index Cond: ((REMOTE_ID = '230620100133zXjW7irYYxRuUbIX4dN'::CHARACTER VARYING) AND (FEEDBACK_STATUS = '1'::NUMERIC)) -> Index Scan using idx_EEEED_source_MODULE_ID on EEEED d (cost=0.55..8.57 rows=1 width=64) (never executed) Index Cond: ((SOURCE_ID = a.ID) AND (MODULE_ID = '190626104707D1FgtbFlPHupVSUtLVK'::CHARACTER VARYING)) -> Index Scan using idx_EEEED_source_MODULE_ID on EEEED e (cost=0.55..1.38 rows=1 width=64) (never executed) Index Cond: ((SOURCE_ID = d.ID) AND (MODULE_ID = '1906281143542BS15J0F45WqfUr9fMV'::CHARACTER VARYING)) -> Index Scan using idx_EEEED_source_MODULE_ID on EEEED c (cost=0.55..1.36 rows=1 width=170) (never executed) Index Cond: ((SOURCE_ID = f.ID) AND (MODULE_ID = '1906281143542BS15J0F45WqfUr9fMV'::CHARACTER VARYING)) Filter: (ROWSTATE = '1'::NUMERIC) Planning time: 2.936 ms Execution time: 0.464 ms (22 rows)
案例3：
-- 原SQL + 执行计划： SELECT QWQWQW.ID, QWQWQW.PID, QWQWQW.PNID, QWQWQW.WF_ID, QWQWQW.WFNODE_ID, QWQWQW.INFO_ID, QWQWQW.WHOHANDLE, QWQWQW.ACTNAME, QWQWQW.MODULE_NAME, QWQWQW.MODULE_ID, QWQWQW.SEND_DEPTNAME, QWQWQW.BT, QWQWQW.RDATE, QWQWQW.STATUS, QWQWQW.READED_USERS, QWQWQW.BACKREASON, QWQWQW.PRE_OPINION, QWQWQW.DEADLINE_ALERT, QWQWQW.DEADLINE_WARNING, QWQWQW.DEADLINE, QWQWQW.GATHER_STATUS, QWQWQW.URGENT, QWQWQW.URGENT_TEXT, QWQWQW.MJ, QWQWQW.MJ_TEXT, QWQWQW.FUZHU_WH, QWQWQW.WH, QWQWQW.FID, QWQWQW.IS_CB, QWQWQW.IS_CB_TEXT, QWQWQW.RETURN_UNAME, QWQWQW.RETRIEVE_TIME, QWQWQW.DUSER_NAME, QWQWQW.USER_ID, QWQWQW.DUSER_ID, QWQWQW.PRE_ACTNAME, QWQWQW.NGR_ORGNAME, FAV.ID AS FAVORITE_ID, GPGPGP.NODE_TYPE, (CASE WHEN QWQWQW.RETRIEVE_TIME IS NULL THEN QWQWQW.RDATE ELSE QWQWQW.RETRIEVE_TIME END) AS EDATE FROM QWQWQW AS QWQWQW LEFT JOIN GPGPGP AS GPGPGP ON QWQWQW.PNID = GPGPGP.ID LEFT JOIN GFGFGF AS FAV ON FAV.INFO_ID = QWQWQW.INFO_ID AND FAV.USER_ID = '210317113214OEl81T3fQMT6DQ0F8sf' WHERE 1 = 1 AND QWQWQW.MODULE_ID NOT IN ( '1907042057531ABn7slgK0TQOnwpxny' , '1906281143542BS15J0F45WqfUr9fMV' , '190628151722nLv7zPl7NrbAJVisHve' , '190708203559JjBokUI83kSH02Aco8s') AND QWQWQW.IS_HB = - 1 AND QWQWQW.STATUS <> 4 AND QWQWQW.STATUS > - 1 AND (((QWQWQW.MUSER_ID = '210317113214OEl81T3fQMT6DQ0F8sf') OR (QWQWQW.MUSER_ID IN ('180622194528xx5u4YOE67fPJCTD5YG' , '190709174122R6zTDgKJyxfUI390t1r' , '1907311655404iY1WvOrLVCuOaxppjr') AND (QWQWQW.DEPT_ID = '210317113214LlXqda7uwjMfaVwCwl7')) )) AND QWQWQW.GATHER_STATUS IN (0 , 9) ORDER BY QWQWQW.URGENT DESC, QWQWQW.STATUS, EDATE DESC LIMIT 18; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=207246.23..207246.27 rows=18 width=1320) (actual time=367907.141..367907.150 rows=18 loops=1) -> Sort (cost=207246.23..207286.19 rows=15987 width=1320) (actual time=367907.141..367907.145 rows=18 loops=1) Sort Key: QWQWQW.URGENT DESC, QWQWQW.STATUS, (CASE WHEN (QWQWQW.RETRIEVE_TIME IS NULL) THEN QWQWQW.RDATE ELSE QWQWQW.RETRIEVE_TIME END) DESC Sort Method: top-N heapsort Memory: 35kB -> Hash Left Join (cost=753.63..206832.97 rows=15987 width=1320) (actual time=272.135..367820.571 rows=16411 loops=1) Hash Cond: (QWQWQW.INFO_ID = FAV.INFO_ID) -> Nested Loop Left Join (cost=724.02..206543.54 rows=15987 width=1280) (actual time=176.826..367641.032 rows=16411 loops=1) -> Bitmap Heap Scan on QWQWQW (cost=723.45..73747.21 rows=15987 width=1276) (actual time=73.983..58593.100 rows=16411 loops=1) Recheck Cond: ((MUSER_ID = '210317113214OEl81T3fQMT6DQ0F8sf'::CHARACTER VARYING) OR (MUSER_ID = ANY ('{180622194528xx5u4YOE67fPJCTD5YG,190709174122R6zTDgKJyxfUI390t1r,1907311655404iY1WvOrLVCuOaxppjr}'::CHARACT ER VARYING[]))) Filter: ((STATUS <> '4'::NUMERIC) AND (STATUS > '-1'::NUMERIC) AND (GATHER_STATUS = ANY ('{0,9}'::NUMERIC[])) AND (IS_HB = '-1'::NUMERIC) AND (MODULE_ID <> ALL ('{1907042057531ABn7slgK0TQOnwpxny,1906281143542B S15J0F45WqfUr9fMV,190628151722nLv7zPl7NrbAJVisHve,190708203559JjBokUI83kSH02Aco8s}'::CHARACTER VARYING[])) AND ((MUSER_ID = '210317113214OEl81T3fQMT6DQ0F8sf'::CHARACTER VARYING) OR ((MUSER_ID = ANY ('{180622194528xx5u4YOE67fPJCTD5YG,190 709174122R6zTDgKJyxfUI390t1r,1907311655404iY1WvOrLVCuOaxppjr}'::CHARACTER VARYING[])) AND (DEPT_ID = '210317113214LlXqda7uwjMfaVwCwl7'::CHARACTER VARYING)))) Heap Blocks: exact=15711 -> BitmapOr (cost=723.45..723.45 rows=22032 width=0) (actual time=65.729..65.729 rows=0 loops=1) -> Bitmap Index Scan on g_index_MUSER_ID1 (cost=0.00..575.22 rows=17955 width=0) (actual time=65.652..65.652 rows=16411 loops=1) Index Cond: (MUSER_ID = '210317113214OEl81T3fQMT6DQ0F8sf'::CHARACTER VARYING) -> Bitmap Index Scan on g_index_MUSER_ID1 (cost=0.00..140.24 rows=4077 width=0) (actual time=0.074..0.074 rows=0 loops=1) Index Cond: (MUSER_ID = ANY ('{180622194528xx5u4YOE67fPJCTD5YG,190709174122R6zTDgKJyxfUI390t1r,1907311655404iY1WvOrLVCuOaxppjr}'::CHARACTER VARYING[])) -> Index Scan using GPGPGP_PKEY on GPGPGP (cost=0.56..8.30 rows=1 width=36) (actual time=18.821..18.826 rows=1 loops=16411) Index Cond: (QWQWQW.PNID = ID) -> Hash (cost=29.48..29.48 rows=11 width=63) (actual time=95.285..95.285 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Index Scan using GFGFGF_user_id on GFGFGF FAV (cost=0.41..29.48 rows=11 width=63) (actual time=95.272..95.274 rows=1 loops=1) Index Cond: (USER_ID = '210317113214OEl81T3fQMT6DQ0F8sf'::CHARACTER VARYING) Planning time: 1411.150 ms Execution time: 367907.348 ms (24 rows)
-- 改写优化： SELECT QWQWQW.ID, QWQWQW.PID, QWQWQW.PNID, QWQWQW.WF_ID, QWQWQW.WFNODE_ID, QWQWQW.INFO_ID, QWQWQW.WHOHANDLE, QWQWQW.ACTNAME, QWQWQW.MODULE_NAME, QWQWQW.MODULE_ID, QWQWQW.SEND_DEPTNAME, QWQWQW.BT, QWQWQW.RDATE, QWQWQW.STATUS, QWQWQW.READED_USERS, QWQWQW.BACKREASON, QWQWQW.PRE_OPINION, QWQWQW.DEADLINE_ALERT, QWQWQW.DEADLINE_WARNING, QWQWQW.DEADLINE, QWQWQW.GATHER_STATUS, QWQWQW.URGENT, QWQWQW.URGENT_TEXT, QWQWQW.MJ, QWQWQW.MJ_TEXT, QWQWQW.FUZHU_WH, QWQWQW.WH, QWQWQW.FID, QWQWQW.IS_CB, QWQWQW.IS_CB_TEXT, QWQWQW.RETURN_UNAME, QWQWQW.RETRIEVE_TIME, QWQWQW.DUSER_NAME, QWQWQW.USER_ID, QWQWQW.DUSER_ID, QWQWQW.PRE_ACTNAME, QWQWQW.NGR_ORGNAME, FAV.ID AS FAVORITE_ID, GPGPGP.NODE_TYPE, (CASE WHEN QWQWQW.RETRIEVE_TIME IS NULL THEN QWQWQW.RDATE ELSE QWQWQW.RETRIEVE_TIME END) AS EDATE FROM QWQWQW AS QWQWQW LEFT JOIN GPGPGP AS GPGPGP ON QWQWQW.PNID = GPGPGP.ID LEFT JOIN GFGFGF AS FAV ON FAV.INFO_ID = QWQWQW.INFO_ID AND FAV.USER_ID = '210317113214OEl81T3fQMT6DQ0F8sf' WHERE 1 = 1 AND QWQWQW.MODULE_ID NOT IN ( '1907042057531ABn7slgK0TQOnwpxny' , '1906281143542BS15J0F45WqfUr9fMV' , '190628151722nLv7zPl7NrbAJVisHve' , '190708203559JjBokUI83kSH02Aco8s') AND QWQWQW.IS_HB = - 1 AND QWQWQW.STATUS <> 4 AND QWQWQW.STATUS > - 1 AND (( (QWQWQW.MUSER_ID = '210317113214OEl81T3fQMT6DQ0F8sf' ) OR (QWQWQW.MUSER_ID IN ('180622194528xx5u4YOE67fPJCTD5YG', '190709174122R6zTDgKJyxfUI390t1r', '1907311655404iY1WvOrLVCuOaxppjr') AND (QWQWQW.DEPT_ID = '210317113214LlXqda7uwjMfaVwCwl7') ) )) AND QWQWQW.GATHER_STATUS IN (0, 9) ORDER BY QWQWQW.URGENT DESC, QWQWQW.STATUS, --EDATE DESC LIMIT 18; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.98..809.31 rows=18 width=1320) (actual time=0.194..9.936 rows=18 loops=1) -> Nested Loop Left Join (cost=0.98..717932.30 rows=15987 width=1320) (actual time=0.193..9.934 rows=18 loops=1) Join Filter: (FAV.INFO_ID = QWQWQW.INFO_ID) Rows Removed by Join Filter: 18 -> Nested Loop Left Join (cost=0.56..715264.94 rows=15987 width=1280) (actual time=0.166..9.886 rows=18 loops=1) -> Seq Scan on QWQWQW (cost=0.00..582468.61 rows=15987 width=1276) (actual time=0.129..9.388 rows=18 loops=1) Filter: ((STATUS <> '4'::NUMERIC) AND (STATUS > '-1'::NUMERIC) AND (GATHER_STATUS = ANY ('{0,9}'::NUMERIC[])) AND (IS_HB = '-1'::NUMERIC) AND (MODULE_ID <> ALL ('{1907042057531ABn7slgK0TQOnwpxny,1906281143542BS15J0F 45WqfUr9fMV,190628151722nLv7zPl7NrbAJVisHve,190708203559JjBokUI83kSH02Aco8s}'::CHARACTER VARYING[])) AND ((MUSER_ID = '210317113214OEl81T3fQMT6DQ0F8sf'::CHARACTER VARYING) OR ((MUSER_ID = ANY ('{180622194528xx5u4YOE67fPJCTD5YG,190709174 122R6zTDgKJyxfUI390t1r,1907311655404iY1WvOrLVCuOaxppjr}'::CHARACTER VARYING[])) AND (DEPT_ID = '210317113214LlXqda7uwjMfaVwCwl7'::CHARACTER VARYING)))) Rows Removed by Filter: 2903 -> Index Scan using GPGPGP_PKEY on GPGPGP (cost=0.56..8.30 rows=1 width=36) (actual time=0.026..0.026 rows=1 loops=18) Index Cond: (QWQWQW.PNID = ID) -> Materialize (cost=0.41..29.53 rows=11 width=63) (actual time=0.001..0.001 rows=1 loops=18) -> Index Scan using GFGFGF_user_id on GFGFGF FAV (cost=0.41..29.48 rows=11 width=63) (actual time=0.021..0.022 rows=1 loops=1) Index Cond: (USER_ID = '210317113214OEl81T3fQMT6DQ0F8sf'::CHARACTER VARYING) Planning time: 6.222 ms Execution time: 10.051 ms (15 rows)
案例4：
-- 原SQL + 执行计划： SELECT COUNT(1) count FROM QWQWQW AS QWQWQW WHERE 1 = 1 AND QWQWQW.MODULE_ID NOT IN ('1907042057531ABn7slgK0TQOnwpxny' , '1906281143542BS15J0F45WqfUr9fMV' , '190628151722nLv7zPl7NrbAJVisHve' , '190708203559JjBokUI83kSH02Aco8s') AND QWQWQW.IS_HB = - 1 AND QWQWQW.STATUS <> 4 AND QWQWQW.STATUS > - 1 AND (((QWQWQW.MUSER_ID = '210317154535TLbxcuA5czpmtp1Txl6') OR (QWQWQW.MUSER_ID IN ('180622194528xx5u4YOE67fPJCTD5YG' , '180622194639xbqoCeWkSgDpLoDnde8' , '1907311655404iY1WvOrLVCuOaxppjr') AND (QWQWQW.DEPT_ID = '210317154535unv1iMCGYYbl2isvYRJ')))) AND QWQWQW.GATHER_STATUS IN (0 , 9); QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------ Aggregate (cost=430753.32..430753.33 rows=1 width=8) (actual time=74224.814..74224.814 rows=1 loops=1) -> Bitmap Heap Scan on QWQWQW (cost=8427.15..430181.10 rows=228888 width=0) (actual time=151.721..74199.376 rows=37946 loops=1) Recheck Cond: ((MUSER_ID = '210317154535TLbxcuA5czpmtp1Txl6'::CHARACTER VARYING) OR (MUSER_ID = ANY ('{180622194528xx5u4YOE67fPJCTD5YG,180622194639xbqoCeWkSgDpLoDnde8,190731165540 4iY1WvOrLVCuOaxppjr}'::CHARACTER VARYING[]))) Rows Removed by Index Recheck: 372210 Filter: ((STATUS <> '4'::NUMERIC) AND (STATUS > '-1'::NUMERIC) AND (GATHER_STATUS = ANY ('{0,9}'::NUMERIC[])) AND (IS_HB = '-1'::NUMERIC) AND (MODULE_ID <> ALL ('{1907042057531ABn 7slgK0TQOnwpxny,1906281143542BS15J0F45WqfUr9fMV,190628151722nLv7zPl7NrbAJVisHve,190708203559JjBokUI83kSH02Aco8s}'::CHARACTER VARYING[])) AND ((MUSER_ID = '210317154535TLbxcuA5czpmtp1Txl6': :CHARACTER VARYING) OR ((MUSER_ID = ANY ('{180622194528xx5u4YOE67fPJCTD5YG,180622194639xbqoCeWkSgDpLoDnde8,1907311655404iY1WvOrLVCuOaxppjr}'::CHARACTER VARYING[])) AND (DEPT_ID = '21031715 4535unv1iMCGYYbl2isvYRJ'::CHARACTER VARYING)))) Rows Removed by Filter: 215433 Heap Blocks: exact=73741 lossy=67166 -> BitmapOr (cost=8427.15..8427.15 rows=261132 width=0) (actual time=128.894..128.894 rows=0 loops=1) -> Bitmap Index Scan on g_index_MUSER_ID1 (cost=0.00..8172.47 rows=257055 width=0) (actual time=128.823..128.823 rows=253379 loops=1) Index Cond: (MUSER_ID = '210317154535TLbxcuA5czpmtp1Txl6'::CHARACTER VARYING) -> Bitmap Index Scan on g_index_MUSER_ID1 (cost=0.00..140.24 rows=4077 width=0) (actual time=0.070..0.070 rows=0 loops=1) Index Cond: (MUSER_ID = ANY ('{180622194528xx5u4YOE67fPJCTD5YG,180622194639xbqoCeWkSgDpLoDnde8,1907311655404iY1WvOrLVCuOaxppjr}'::CHARACTER VARYING[])) Planning time: 0.965 ms Execution time: 74224.913 ms (14 rows)
-- 改写优化： SELECT COUNT(1) AS count FROM ( SELECT QWQWQW.ctid FROM QWQWQW WHERE QWQWQW.MODULE_ID NOT IN ('1907042057531ABn7slgK0TQOnwpxny', '1906281143542BS15J0F45WqfUr9fMV', '190628151722nLv7zPl7NrbAJVisHve', '190708203559JjBokUI83kSH02Aco8s') AND QWQWQW.IS_HB = - 1 AND QWQWQW.STATUS <> 4 AND QWQWQW.STATUS > - 1 AND QWQWQW.MUSER_ID = '210317154535TLbxcuA5czpmtp1Txl6' AND QWQWQW.GATHER_STATUS IN (0, 9) UNION SELECT QWQWQW.ctid FROM QWQWQW WHERE QWQWQW.MODULE_ID NOT IN ('1907042057531ABn7slgK0TQOnwpxny', '1906281143542BS15J0F45WqfUr9fMV', '190628151722nLv7zPl7NrbAJVisHve', '190708203559JjBokUI83kSH02Aco8s') AND QWQWQW.IS_HB = - 1 AND QWQWQW.STATUS <> 4 AND QWQWQW.STATUS > - 1 AND QWQWQW.MUSER_ID IN ('180622194528xx5u4YOE67fPJCTD5YG', '180622194639xbqoCeWkSgDpLoDnde8', '1907311655404iY1WvOrLVCuOaxppjr') AND QWQWQW.DEPT_ID = '210317154535unv1iMCGYYbl2isvYRJ' AND QWQWQW.GATHER_STATUS IN (0, 9) ) AS x; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------- Aggregate (cost=467680.88..467680.89 rows=1 width=8) (actual time=879.326..879.326 rows=1 loops=1) -> Unique (cost=463674.92..464819.48 rows=228912 width=6) (actual time=865.739..875.807 rows=37946 loops=1) -> Sort (cost=463674.92..464247.20 rows=228912 width=6) (actual time=865.737..869.383 rows=37946 loops=1) Sort Key: QWQWQW.CTID Sort Method: quicksort Memory: 3315kB -> Append (cost=8229.62..443296.67 rows=228912 width=6) (actual time=133.174..858.615 rows=37946 loops=1) -> Bitmap Heap Scan on QWQWQW (cost=8229.62..425632.41 rows=228605 width=6) (actual time=133.173..855.894 rows=37946 loops=1) Recheck Cond: (MUSER_ID = '210317154535TLbxcuA5czpmtp1Txl6'::CHARACTER VARYING) Rows Removed by Index Recheck: 372210 Filter: ((STATUS <> '4'::NUMERIC) AND (STATUS > '-1'::NUMERIC) AND (GATHER_STATUS = ANY ('{0,9}'::NUMERIC[])) AND (IS_HB = '-1'::NUMERIC) AND (MODULE_ID <> ALL ( '{1907042057531ABn7slgK0TQOnwpxny,1906281143542BS15J0F45WqfUr9fMV,190628151722nLv7zPl7NrbAJVisHve,190708203559JjBokUI83kSH02Aco8s}'::CHARACTER VARYING[]))) Rows Removed by Filter: 215433 Heap Blocks: exact=73741 lossy=67166 -> Bitmap Index Scan on g_index_MUSER_ID1 (cost=0.00..8172.47 rows=257055 width=0) (actual time=112.570..112.570 rows=253379 loops=1) Index Cond: (MUSER_ID = '210317154535TLbxcuA5czpmtp1Txl6'::CHARACTER VARYING) -> Bitmap Heap Scan on QWQWQW QWQWQW_1 (cost=140.32..15375.15 rows=307 width=6) (actual time=0.061..0.061 rows=0 loops=1) Recheck Cond: (MUSER_ID = ANY ('{180622194528xx5u4YOE67fPJCTD5YG,180622194639xbqoCeWkSgDpLoDnde8,1907311655404iY1WvOrLVCuOaxppjr}'::CHARACTER VARYING[])) Filter: ((STATUS <> '4'::NUMERIC) AND (STATUS > '-1'::NUMERIC) AND (GATHER_STATUS = ANY ('{0,9}'::NUMERIC[])) AND (IS_HB = '-1'::NUMERIC) AND (DEPT_ID = '2103171 54535unv1iMCGYYbl2isvYRJ'::CHARACTER VARYING) AND (MODULE_ID <> ALL ('{1907042057531ABn7slgK0TQOnwpxny,1906281143542BS15J0F45WqfUr9fMV,190628151722nLv7zPl7NrbAJVisHve,190708203559JjBokUI83 kSH02Aco8s}'::CHARACTER VARYING[]))) -> Bitmap Index Scan on g_index_MUSER_ID1 (cost=0.00..140.24 rows=4077 width=0) (actual time=0.061..0.061 rows=0 loops=1) Index Cond: (MUSER_ID = ANY ('{180622194528xx5u4YOE67fPJCTD5YG,180622194639xbqoCeWkSgDpLoDnde8,1907311655404iY1WvOrLVCuOaxppjr}'::CHARACTER VARYING[])) Planning time: 0.495 ms Execution time: 879.423 ms (21 rows)
PG优化器有时候给我的感觉就像个傻子似的，统计信息，直方图就像摆设，稍微有个弯就转不过去，但是总体算法，性能还是不错，比MySQL真的强太多。
还是ORACLE 的CBO牛逼点。
浙公网安备 33010602011771号