同事最近做個金融適配專案，找我看條SQL，告知ORACLE跑1分鐘，PG要跑30分鐘(其實並沒有這麼誇張)?， 廢話不說，貼慢SQL。
慢SQL（關鍵資訊已經加密）：
explain analyze SELECT c_qxxxxaode, '2023-03-22 00:00:00' AS d_cdate, SUM(CASE WHEN l.f_qdqdqdq <= add_months('2023-03-22 00:00:00', -60) THEN 1 ELSE 0 END) AS bt5ycusts, SUM(CASE WHEN l.f_qdqdqdq > add_months('2023-03-22 00:00:00', -60) AND l.f_qdqdqdq <= add_months('2023-03-22 00:00:00', -36) THEN 1 ELSE 0 END) AS bt3to5ycusts, SUM(CASE WHEN l.f_qdqdqdq <= add_months('2023-03-22 00:00:00', -36) THEN 1 ELSE 0 END) AS bt3ycusts, SUM(CASE WHEN l.f_qdqdqdq > add_months('2023-03-22 00:00:00', -36) AND l.f_qdqdqdq <= add_months('2023-03-22 00:00:00', -12) THEN 1 ELSE 0 END) AS bt1yto3ycusts, SUM(CASE WHEN l.f_qdqdqdq > add_months('2023-03-22 00:00:00', -12) AND l.f_qdqdqdq <= add_months('2023-03-22 00:00:00', -6) THEN 1 ELSE 0 END) AS bt6to12mcusts, SUM(CASE WHEN l.f_qdqdqdq > add_months('2023-03-22 00:00:00', -6) AND l.f_qdqdqdq <= add_months('2023-03-22 00:00:00', -3) THEN 1 ELSE 0 END) AS bt3to6mcusts, SUM(CASE WHEN l.f_qdqdqdq > add_months('2023-03-22 00:00:00', -3) THEN 1 ELSE 0 END) AS btlose3mcusts, SUM(CASE WHEN l.f_qdqdqdq <= add_months('2023-03-22 00:00:00', -60) THEN f_qwwvddvvzz ELSE 0 END) AS bt5yshares, SUM(CASE WHEN l.f_qdqdqdq > add_months('2023-03-22 00:00:00', -60) AND l.f_qdqdqdq <= add_months('2023-03-22 00:00:00', -36) THEN f_qwwvddvvzz ELSE 0 END) AS bt3to5yshares, SUM(CASE WHEN l.f_qdqdqdq <= add_months('2023-03-22 00:00:00', -36) THEN f_qwwvddvvzz ELSE 0 END) AS bt3yshares, SUM(CASE WHEN l.f_qdqdqdq > add_months('2023-03-22 00:00:00', -36) AND l.f_qdqdqdq <= add_months('2023-03-22 00:00:00', -12) THEN f_qwwvddvvzz ELSE 0 END) AS bt1to3yshares, SUM(CASE WHEN l.f_qdqdqdq > add_months('2023-03-22 00:00:00', -12) AND l.f_qdqdqdq <= add_months('2023-03-22 00:00:00', -6) THEN f_qwwvddvvzz ELSE 0 END) AS bt6to12mshares, SUM(CASE WHEN l.f_qdqdqdq > add_months('2023-03-22 00:00:00', -6) AND l.f_qdqdqdq <= add_months('2023-03-22 00:00:00', -3) THEN f_qwwvddvvzz ELSE 0 END) AS bt3to6mshares, SUM(CASE WHEN l.f_qdqdqdq > add_months('2023-03-22 00:00:00', -3) THEN f_qwwvddvvzz ELSE 0 END) AS btlose3mshares, round(AVG(months_between('2023-03-22 00:00:00', l.f_qdqdqdq)), 2) AS avgmonth, round(AVG(nvl(f_qwwvddvvzz, 0)), 2) AS avgshares, COUNT(a_daccoxxz) AS custsum, SUM(f_qwwvddvvzz) AS sharessum FROM stcdlbxxxxx l WHERE nvl(l.f_qwwvddvvzz, 0) > 0 AND l.f_qdqdqdq <= '2023-03-22 00:00:00' AND l.a_daccoxxz <> '996000000000' AND c_qxxxxaode IN (SELECT c_qxxxxaode FROM xdsfffs WHERE c_raisetype = '1') GROUP BY c_qxxxxaode;
執行計劃：
HashAggregate (cost=1043326.49..1043332.91 rows=151 width=424) (actual time=381246.429..381246.640 rows=150 loops=1) Group Key: l.c_qxxxxaode -> Hash Semi Join (cost=8.78..936347.95 rows=301348 width=38) (actual time=0.057..30237.230 rows=30056793 loops=1) Hash Cond: (l.c_qxxxxaode = tfundinfo.c_qxxxxaode) -> Seq Scan on stcdlbxxxxx l (cost=0.00..906618.70 rows=10044941 width=38) (actual time=0.008..25908.814 rows=30157190 loops=1) " Filter: ((NVL(f_qwwvddvvzz, '0'::numeric) > '0'::numeric) AND (f_qdqdqdq <= '2023-03-22 00:00:00'::timestamp without time zone) AND (a_daccoxxz <> '996000000000'::text))" Rows Removed by Filter: 4842810 -> Hash (cost=6.91..6.91 rows=150 width=8) (actual time=0.046..0.047 rows=150 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 14kB -> Index Only Scan using idx_tfundinfo_fundcode on xdsfffs (cost=0.28..6.91 rows=150 width=8) (actual time=0.021..0.037 rows=150 loops=1) Index Cond: (c_raisetype = '1'::text) Heap Fetches: 0 Planning Time: 0.512 ms Execution Time: 381246.699 ms
select count(1) from stcdlbxxxxx; count ---------- 35000000 (1 row)
stcdlbxxxxx 表資料量 3500W，資料量不算大，這條SQL主要含義應該是對 fact 表做統計，一堆聚合函式，fact 表是數倉概念，如果不明白可以去百度。
這條SQL主要慢在HashAggregate這個節點上，Hash Semi Join 花了 30237.230毫秒（30秒），然後到上面 Group Key: l.c_qxxxxaode 分個組以後，HashAggregate 直接飆到 381246.640毫秒（6.3分鐘）。
其實這條SQL最理想的狀態是走 HashAggregate + parallel 的計劃，但是最佳化器並沒有這樣做，我懷疑可能是SQL寫法導致最佳化器沒有走並行，沒多想，直接改寫了一版的SQL讓同事去試試。
改寫版本SQL：(PG獨佔的語法，聚合函式新增 FILTER 屬性，代替 case when 寫法)
explain analyze SELECT c_qxxxxaode, '2023-03-22 00:00:00' AS d_cdate, COUNT(*) FILTER (WHERE l.f_qdqdqdq <= add_months('2023-03-22 00:00:00', -60)) AS bt5ycusts, COUNT(*) FILTER (WHERE l.f_qdqdqdq > add_months('2023-03-22 00:00:00', -60) AND l.f_qdqdqdq <= add_months('2023-03-22 00:00:00', -36)) AS bt3to5ycusts, COUNT(*) FILTER (WHERE l.f_qdqdqdq <= add_months('2023-03-22 00:00:00', -36)) AS bt3ycusts, COUNT(*) FILTER (WHERE l.f_qdqdqdq > add_months('2023-03-22 00:00:00', -36) AND l.f_qdqdqdq <= add_months('2023-03-22 00:00:00', -12)) AS bt1yto3ycusts, COUNT(*) FILTER (WHERE l.f_qdqdqdq > add_months('2023-03-22 00:00:00', -12) AND l.f_qdqdqdq <= add_months('2023-03-22 00:00:00', -6)) AS bt6to12mcusts, COUNT(*) FILTER (WHERE l.f_qdqdqdq > add_months('2023-03-22 00:00:00', -6) AND l.f_qdqdqdq <= add_months('2023-03-22 00:00:00', -3)) AS bt3to6mcusts, COUNT(*) FILTER (WHERE l.f_qdqdqdq > add_months('2023-03-22 00:00:00', -3)) AS btlose3mcusts, SUM(f_qwwvddvvzz) FILTER (WHERE l.f_qdqdqdq <= add_months('2023-03-22 00:00:00', -60)) AS bt5yshares, SUM(f_qwwvddvvzz) FILTER (WHERE l.f_qdqdqdq > add_months('2023-03-22 00:00:00', -60) AND l.f_qdqdqdq <= add_months('2023-03-22 00:00:00', -36)) AS bt3to5yshares, SUM(f_qwwvddvvzz) FILTER (WHERE l.f_qdqdqdq <= add_months('2023-03-22 00:00:00', -36)) AS bt3yshares, SUM(f_qwwvddvvzz) FILTER (WHERE l.f_qdqdqdq > add_months('2023-03-22 00:00:00', -36) AND l.f_qdqdqdq <= add_months('2023-03-22 00:00:00', -12)) AS bt1to3yshares, SUM(f_qwwvddvvzz) FILTER (WHERE l.f_qdqdqdq > add_months('2023-03-22 00:00:00', -12) AND l.f_qdqdqdq <= add_months('2023-03-22 00:00:00', -6)) AS bt6to12mshares, SUM(f_qwwvddvvzz) FILTER (WHERE l.f_qdqdqdq > add_months('2023-03-22 00:00:00', -6) AND l.f_qdqdqdq <= add_months('2023-03-22 00:00:00', -3)) AS bt3to6mshares, SUM(f_qwwvddvvzz) FILTER (WHERE l.f_qdqdqdq > add_months('2023-03-22 00:00:00', -3)) AS btlose3mshares, ROUND(AVG(months_between('2023-03-22 00:00:00', l.f_qdqdqdq)), 2) AS avgmonth, ROUND(AVG(NVL(f_qwwvddvvzz, 0)), 2) AS avgshares, COUNT(a_daccoxxz) AS custsum, SUM(f_qwwvddvvzz) AS sharessum FROM stcdlbxxxxx l WHERE NVL(l.f_qwwvddvvzz, 0) > 0 AND l.f_qdqdqdq <= '2023-03-22 00:00:00' AND l.a_daccoxxz <> '996000000000' AND c_qxxxxaode IN (SELECT c_qxxxxaode FROM xdsfffs WHERE c_raisetype = '1') GROUP BY c_qxxxxaode;
改寫後執行計劃：
HashAggregate (cost=1043326.49..1043332.91 rows=151 width=424) (actual time=380246.621..380246.849 rows=150 loops=1) Group Key: l.c_qxxxxaode -> Hash Semi Join (cost=8.78..936347.95 rows=301348 width=38) (actual time=0.055..29983.463 rows=30056793 loops=1) Hash Cond: (l.c_qxxxxaode = tfundinfo.c_qxxxxaode) -> Seq Scan on stcdlbxxxxx l (cost=0.00..906618.70 rows=10044941 width=38) (actual time=0.008..25415.490 rows=30157190 loops=1) " Filter: ((NVL(f_qwwvddvvzz, '0'::numeric) > '0'::numeric) AND (f_qdqdqdq <= '2023-03-22 00:00:00'::timestamp without time zone) AND (a_daccoxxz <> '996000000000'::text))" Rows Removed by Filter: 4842810 -> Hash (cost=6.91..6.91 rows=150 width=8) (actual time=0.043..0.044 rows=150 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 14kB -> Index Only Scan using idx_tfundinfo_fundcode on xdsfffs (cost=0.28..6.91 rows=150 width=8) (actual time=0.018..0.035 rows=150 loops=1) Index Cond: (c_raisetype = '1'::text) Heap Fetches: 0 Planning Time: 0.533 ms Execution Time: 380246.909 ms
當時我看到這個改寫後執行計劃就無語了?，和原SQL的計劃一毛一樣，直接把我整懵逼，懷疑是否現場環境沒開並行引數，但是被同事告知是已經設定了並行引數。
qtbg=> show max_parallel_workers_per_gather qtbg-> ; max_parallel_workers_per_gather --------------------------------- 16 (1 row)
瞭解到情況以後開始研究SQL，發現使用了兩個自定義函式，add_months，months_between（我們研發寫的函式、為了相容ORACLE），然後看了下這兩個函式的資訊。
qtbg=> \df+ add_months List of functions Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description --------+------------+------------------+-----------------------------------+------+------------+----------+--------+----------+-------------------+----------+----------------------------------------------------------------------------------+------------- sys | add_months | pg_catalog.date | pg_catalog.date, boolean | func | immutable | safe | system | invoker | | c | add_months_bool | sys | add_months | pg_catalog.date | pg_catalog.date, integer | func | immutable | safe | system | invoker | | c | add_months | sys | add_months | date | timestamp with time zone, integer | func | immutable | safe | system | invoker | | sql | select (add_months($1::pg_catalog.date, $2) + $1::pg_catalog.time)::sys.date; | (3 rows) qtbg=> \df+ months_between List of functions Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Descri ption --------+----------------+------------------+----------------------------------------------------+------+------------+----------+--------+----------+-------------------+----------+-------------------------------------------------------------------------------------------------------------------------------------+------- ------ sys | months_between | double precision | date, date | func | volatile | unsafe | system | invoker | | plsql | +| | | | | | | | | | | | begin +| | | | | | | | | | | | case when +| | | | | | | | | | | | (last_day($1) = $1 and last_day($2) = $2) +| | | | | | | | | | | | or +| | | | | | | | | | | | (extract(day from $1) = extract(day from $2)) +| | | | | | | | | | | | then +| | | | | | | | | | | | return (select (extract(years from $1)::int * 12 - extract(years from $2)::int * 12)::float + +| | | | | | | | | | | | (extract(month from $1)::int - extract(month from $2)::int)::float); +| | | | | | | | | | | | else +| | | | | | | | | | | | return (select (extract(years from $1)::int * 12 - extract(years from $2)::int * 12)::float + +| | | | | | | | | | | | (extract(month from $1)::int - extract(month from $2)::int)::float + +| | | | | | | | | | | | (extract(day from $1)::int - extract(day from $2)::int)/31::float + +| | | | | | | | | | | | (extract(hour from $1)::int * 3600 + extract(minutes from $1)::int * 60 + extract(seconds from $1)::int)/(3600*24*31)::float -+| | | | | | | | | | | | (extract(hour from $2)::int * 3600 + extract(minutes from $2)::int * 60 + extract(seconds from $2)::int)/(3600*24*31)::float);+| | | | | | | | | | | | end case; +| | | | | | | | | | | | end; +| | | | | | | | | | | | | sys | months_between | double precision | timestamp with time zone, timestamp with time zone | func | volatile | unsafe | system | invoker | | plsql | +| | | | | | | | | | | | begin +| | | | | | | | | | | | case when +| | | | | | | | | | | | (last_day($1) = $1 and last_day($2) = $2) +| | | | | | | | | | | | or +| | | | | | | | | | | | (extract(day from $1) = extract(day from $2)) +| | | | | | | | | | | | then +| | | | | | | | | | | | return (select (extract(years from $1)::int * 12 - extract(years from $2)::int * 12)::float + +| | | | | | | | | | | | (extract(month from $1)::int - extract(month from $2)::int)::float); +| | | | | | | | | | | | else +| | | | | | | | | | | | return (select (extract(years from $1)::int * 12 - extract(years from $2)::int * 12)::float + +| | | | | | | | | | | | (extract(month from $1)::int - extract(month from $2)::int)::float + +| | | | | | | | | | | | (extract(day from $1)::int - extract(day from $2)::int)/31::float + +| | | | | | | | | | | | (extract(hour from $1)::int * 3600 + extract(minutes from $1)::int * 60 + extract(seconds from $1)::int)/(3600*24*31)::float -+| | | | | | | | | | | | (extract(hour from $2)::int * 3600 + extract(minutes from $2)::int * 60 + extract(seconds from $2)::int)/(3600*24*31)::float);+| | | | | | | | | | | | end case; +| | | | | | | | | | | | end; +| | | | | | | | | | | | | (2 rows)
當時就給我整崩潰了，研發真的太坑啦???。
months_between 函式居然還是 volatile（不穩定）狀態，我一直以為研發寫好的ORACLE 相容函式都是 immutable 或者 是 stable 狀態，所以剛開始也沒往這方面想。
PG的自定義函式有三種狀態，volatile、immutable 、stable 。
volatile 是不可以走並行的，這三種狀態具體代表的是什麼含義，如果不清楚的同學可以下去百度，這裡就不廢話了，我也怕解釋不清楚。?
和客戶溝通以後，我寫了一個 months_between1 函式代替原來的 months_between函式，返回結果小數點的精度可能和ORACLE有點區別，但是客戶表示可以接受。
months_between1 函式：
CREATE OR REPLACE FUNCTION months_between1(date1 DATE, date2 DATE) RETURNS FLOAT AS $$ DECLARE years_diff INT; months_diff INT; days_diff FLOAT; BEGIN years_diff := EXTRACT(YEAR FROM date1) - EXTRACT(YEAR FROM date2); months_diff := EXTRACT(MONTH FROM date1) - EXTRACT(MONTH FROM date2); days_diff := EXTRACT(DAY FROM date1) - EXTRACT(DAY FROM date2); days_diff := days_diff / 30.0; RETURN (years_diff * 12) + months_diff + days_diff; END; $$ LANGUAGE plpgsql IMMUTABLE;
將 months_between1 設定可以並行：
ALTER FUNCTION months_between1(date, date) PARALLEL SAFE;
months_between1 函式資訊：
\df+ months_between1 List of functions Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description --------+-----------------+------------------+------------------------+------+------------+----------+-------+----------+-------------------+----------+---------------------------------------------------------------------------+------------- scott | months_between1 | double precision | date1 date, date2 date | func | immutable | safe | qtbg | invoker | | plpgsql | +| | | | | | | | | | | | DECLARE +| | | | | | | | | | | | years_diff INT; +| | | | | | | | | | | | months_diff INT; +| | | | | | | | | | | | days_diff FLOAT; +| | | | | | | | | | | | BEGIN +| | | | | | | | | | | | years_diff := EXTRACT(YEAR FROM date1) - EXTRACT(YEAR FROM date2); +| | | | | | | | | | | | months_diff := EXTRACT(MONTH FROM date1) - EXTRACT(MONTH FROM date2);+| | | | | | | | | | | | days_diff := EXTRACT(DAY FROM date1) - EXTRACT(DAY FROM date2); +| | | | | | | | | | | | days_diff := days_diff / 30.0; +| | | | | | | | | | | | RETURN (years_diff * 12) + months_diff + days_diff; +| | | | | | | | | | | | END; +| | | | | | | | | | | | | (1 row)
最後執行SQL測試效率：
1 explain analyze 2 SELECT c_qxxxxaode, 3 '2023-03-22 00:00:00' AS d_cdate, 4 COUNT(*) FILTER (WHERE l.f_qdqdqdq <= add_months('2023-03-22 00:00:00', -60)) AS bt5ycusts, 5 COUNT(*) FILTER (WHERE l.f_qdqdqdq > add_months('2023-03-22 00:00:00', -60) AND l.f_qdqdqdq <= add_months('2023-03-22 00:00:00', -36)) AS bt3to5ycusts, 6 COUNT(*) FILTER (WHERE l.f_qdqdqdq <= add_months('2023-03-22 00:00:00', -36)) AS bt3ycusts, 7 COUNT(*) FILTER (WHERE l.f_qdqdqdq > add_months('2023-03-22 00:00:00', -36) AND l.f_qdqdqdq <= add_months('2023-03-22 00:00:00', -12)) AS bt1yto3ycusts, 8 COUNT(*) FILTER (WHERE l.f_qdqdqdq > add_months('2023-03-22 00:00:00', -12) AND l.f_qdqdqdq <= add_months('2023-03-22 00:00:00', -6)) AS bt6to12mcusts, 9 COUNT(*) FILTER (WHERE l.f_qdqdqdq > add_months('2023-03-22 00:00:00', -6) AND l.f_qdqdqdq <= add_months('2023-03-22 00:00:00', -3)) AS bt3to6mcusts, 10 COUNT(*) FILTER (WHERE l.f_qdqdqdq > add_months('2023-03-22 00:00:00', -3)) AS btlose3mcusts, 11 SUM(f_qwwvddvvzz) FILTER (WHERE l.f_qdqdqdq <= add_months('2023-03-22 00:00:00', -60)) AS bt5yshares, 12 SUM(f_qwwvddvvzz) FILTER (WHERE l.f_qdqdqdq > add_months('2023-03-22 00:00:00', -60) AND l.f_qdqdqdq <= add_months('2023-03-22 00:00:00', -36)) AS bt3to5yshares, 13 SUM(f_qwwvddvvzz) FILTER (WHERE l.f_qdqdqdq <= add_months('2023-03-22 00:00:00', -36)) AS bt3yshares, 14 SUM(f_qwwvddvvzz) FILTER (WHERE l.f_qdqdqdq > add_months('2023-03-22 00:00:00', -36) AND l.f_qdqdqdq <= add_months('2023-03-22 00:00:00', -12)) AS bt1to3yshares, 15 SUM(f_qwwvddvvzz) FILTER (WHERE l.f_qdqdqdq > add_months('2023-03-22 00:00:00', -12) AND l.f_qdqdqdq <= add_months('2023-03-22 00:00:00', -6)) AS bt6to12mshares, 16 SUM(f_qwwvddvvzz) FILTER (WHERE l.f_qdqdqdq > add_months('2023-03-22 00:00:00', -6) AND l.f_qdqdqdq <= add_months('2023-03-22 00:00:00', -3)) AS bt3to6mshares, 17 SUM(f_qwwvddvvzz) FILTER (WHERE l.f_qdqdqdq > add_months('2023-03-22 00:00:00', -3)) AS btlose3mshares, 18 ROUND(AVG(months_between1('2023-03-22 00:00:00', l.f_qdqdqdq)), 2) AS avgmonth, 19 ROUND(AVG(NVL(f_qwwvddvvzz, 0)), 2) AS avgshares, 20 COUNT(a_daccoxxz) AS custsum, 21 SUM(f_qwwvddvvzz) AS sharessum 22 FROM stcdlbxxxxx l 23 WHERE NVL(l.f_qwwvddvvzz, 0) > 0 24 AND l.f_qdqdqdq <= '2023-03-22 00:00:00' 25 AND l.a_daccoxxz <> '996000000000' 26 AND c_qxxxxaode IN (SELECT c_qxxxxaode FROM xdsfffs WHERE c_raisetype = '1') 27 GROUP BY c_qxxxxaode;
執行計劃：
Finalize GroupAggregate (cost=406085.34..424225.64 rows=151 width=424) (actual time=9533.754..16818.165 rows=150 loops=1) Group Key: l.c_qxxxxaode -> Gather Merge (cost=406085.34..424155.80 rows=906 width=392) (actual time=9484.778..16812.958 rows=1050 loops=1) Workers Planned: 6 Workers Launched: 6 -> Partial GroupAggregate (cost=405085.25..423045.59 rows=151 width=392) (actual time=9383.774..16482.619 rows=150 loops=7) Group Key: l.c_qxxxxaode -> Sort (cost=405085.25..405210.81 rows=50225 width=38) (actual time=9358.407..10328.135 rows=4293828 loops=7) Sort Key: l.c_qxxxxaode Sort Method: quicksort Memory: 461463kB Worker 0: Sort Method: quicksort Memory: 452754kB Worker 1: Sort Method: quicksort Memory: 452822kB Worker 2: Sort Method: quicksort Memory: 451891kB Worker 3: Sort Method: quicksort Memory: 448327kB Worker 4: Sort Method: quicksort Memory: 454387kB Worker 5: Sort Method: quicksort Memory: 453623kB -> Hash Semi Join (cost=8.78..401163.65 rows=50225 width=38) (actual time=0.219..4369.485 rows=4293828 loops=7) Hash Cond: (l.c_qxxxxaode = tfundinfo.c_qxxxxaode) -> Parallel Seq Scan on stcdlbxxxxx l (cost=0.00..396201.45 rows=1674157 width=38) (actual time=0.017..3948.943 rows=4308170 loops=7) " Filter: ((NVL(f_qwwvddvvzz, '0'::numeric) > '0'::numeric) AND (f_qdqdqdq <= '2023-03-22 00:00:00'::timestamp without time zone) AND (a_daccoxxz <> '996000000000'::text))" Rows Removed by Filter: 691830 -> Hash (cost=6.91..6.91 rows=150 width=8) (actual time=0.164..0.173 rows=150 loops=7) Buckets: 1024 Batches: 1 Memory Usage: 14kB -> Index Only Scan using idx_tfundinfo_fundcode on xdsfffs (cost=0.28..6.91 rows=150 width=8) (actual time=0.072..0.151 rows=150 loops=7) Index Cond: (c_raisetype = '1'::text) Heap Fetches: 0 Planning Time: 0.588 ms Execution Time: 16851.196 ms
最後可以看到原來 6分鐘的SQL，現在已經 16.8秒就能跑出結果，研發真的太坑了。????
其實這條SQL完全不用改成 （聚合函式新增 FILTER 屬性）這種寫法，原來的SQL只要把 months_between 函式替換成 months_between1，一樣也可以走並行。
但是為了更好看，還是把這種新的寫法提交給了客戶。?????