DBA 脑子要咋长才行啊....

PG 里两条一样的语句,时间跨度不同,结果优化成完全不同的执行方式....

语句语句用于计算：当天注册的人的登陆次数,一张注册表日志,一张登陆日志表 group by 用于合并渠道和游戏 id. 为了避免无法去重没用 distinct

select one_day, count() login_count, ROUND((count() / sum(count(*)) over(PARTITION BY one_day, app_id order by app_id))*100, 2) app_percent, sub_channel, app_id from (select reg.reg_day as one_day, login.app_id as app_id, login.sub_channel as sub_channel, reg.user_id from (select count_time, date_trunc('day', count_time) as reg_day, user_id from t_l_register_log where count_time >= '2019-03-03 00:00:00' and count_time < '2019-04-04 00:00:00' order by reg_day) as reg inner join (select date_trunc('day', count_time) as login_day ,sub_channel, app_id, user_id from t_l_platform_login_log where count_time >= '2019-03-03 00:00:00' and count_time < '2019-04-04 00:00:00' group by login_day, app_id, sub_channel, user_id order by login_day) as login on date_trunc('day', reg.count_time) = login_day and reg.user_id = login.user_id group by one_day, app_id, sub_channel, reg.user_id) as tb group by one_day, app_id, sub_channel order by one_day, app_id, login_count desc, app_percent desc;

这个是能这却执行的 sql,时间跨度 1 年

------------------------------------------------------------------------------------------
 Sort  (cost=97561067.23..97581067.23 rows=8000000 width=56) (actual time=34585.894..34586.519 rows=18517 loops=1)
   Output: reg.reg_day, (count(*)), (round(((((count(*)))::numeric / sum((count(*))) OVER (?)) * '100'::numeric), 2)), login.sub_channel, login.app_id
   Sort Key: reg.reg_day, login.app_id, (count(*)) DESC, (round(((((count(*)))::numeric / sum((count(*))) OVER (?)) * '100'::numeric), 2)) DESC
   Sort Method: quicksort  Memory: 2215kB
   ->  WindowAgg  (cost=83444220.45..96096929.48 rows=8000000 width=56) (actual time=33708.198..34578.031 rows=18517 loops=1)
         Output: reg.reg_day, (count(*)), round(((((count(*)))::numeric / sum((count(*))) OVER (?)) * '100'::numeric), 2), login.sub_channel, login.app_id
         ->  GroupAggregate  (cost=83444220.45..95856929.48 rows=8000000 width=24) (actual time=33708.188..34562.551 rows=18517 loops=1)
               Output: reg.reg_day, login.sub_channel, login.app_id, count(*)
               Group Key: reg.reg_day, login.app_id, login.sub_channel
               ->  Group  (cost=83444220.45..87555123.46 rows=328872241 width=28) (actual time=33708.179..34341.267 rows=3198877 loops=1)
                     Output: reg.reg_day, login.app_id, login.sub_channel, reg.user_id, login.app_id, login.sub_channel
                     Group Key: reg.reg_day, login.app_id, login.sub_channel, reg.user_id
                     ->  Sort  (cost=83444220.45..84266401.05 rows=328872241 width=20) (actual time=33708.175..33958.476 rows=3198877 loops=1)
                           Output: reg.reg_day, reg.user_id, login.app_id, login.sub_channel
                           Sort Key: reg.reg_day, login.app_id, login.sub_channel, reg.user_id
                           Sort Method: external merge  Disk: 93944kB
                           ->  Merge Join  (cost=10070881.07..16687034.80 rows=328872241 width=20) (actual time=28498.899..31904.531 rows=3198877 loops=1)
                                 Output: reg.reg_day, reg.user_id, login.app_id, login.sub_channel
                                 Merge Cond: (((date_trunc('day'::text, reg.count_time)) = login.login_day) AND (reg.user_id = login.user_id))
                                 ->  Sort  (cost=876809.36..883338.44 rows=2611632 width=20) (actual time=2665.030..2929.604 rows=3204477 loops=1)
                                       Output: reg.reg_day, reg.user_id, reg.count_time, (date_trunc('day'::text, reg.count_time))
                                       Sort Key: (date_trunc('day'::text, reg.count_time)), reg.user_id
                                       Sort Method: external merge  Disk: 131744kB
                                       ->  Subquery Scan on reg  (cost=142257.54..491333.83 rows=2611632 width=20) (actual time=376.086..1283.740 rows=3204477 loops=1)
                                             Output: reg.reg_day, reg.user_id, reg.count_time, date_trunc('day'::text, reg.count_time)
                                             ->  Gather Merge  (cost=142257.54..465217.51 rows=2611632 width=20) (actual time=376.081..815.735 rows=3204477 loops=1)
                                                   Output: _hyper_8_974_chunk.count_time, (date_trunc('day'::text, _hyper_8_974_chunk.count_time)), _hyper_8_974_chunk.user_id
                                                   Workers Planned: 8
                                                   Workers Launched: 8
                                                   ->  Sort  (cost=141257.40..142073.54 rows=326454 width=20) (actual time=346.654..379.105 rows=356053 loops=9)
                                                         Output: _hyper_8_974_chunk.count_time, (date_trunc('day'::text, _hyper_8_974_chunk.count_time)), _hyper_8_974_chunk.user_id
                                                         Sort Key: (date_trunc('day'::text, _hyper_8_974_chunk.count_time))
                                                         Sort Method: external merge  Disk: 10176kB

这个是没有正确执行的 sql,时间跨度一个月,不知道要跑多少时间

---------------------------------------------------------------------------
 Sort  (cost=1378360.26..1378362.88 rows=1046 width=56)
   Output: reg.reg_day, (count(*)), (round(((((count(*)))::numeric / sum((count(*))) OVER (?)) * '100'::numeric), 2)), _hyper_4_1229_chunk.sub_channel, _hyper_4_1229_chunk.app_id
   Sort Key: reg.reg_day, _hyper_4_1229_chunk.app_id, (count(*)) DESC, (round(((((count(*)))::numeric / sum((count(*))) OVER (?)) * '100'::numeric), 2)) DESC
   ->  WindowAgg  (cost=1377873.71..1378307.80 rows=1046 width=56)
         Output: reg.reg_day, (count(*)), round(((((count(*)))::numeric / sum((count(*))) OVER (?)) * '100'::numeric), 2), _hyper_4_1229_chunk.sub_channel, _hyper_4_1229_chunk.app_id
         ->  GroupAggregate  (cost=1377873.71..1378276.42 rows=1046 width=24)
               Output: reg.reg_day, _hyper_4_1229_chunk.sub_channel, _hyper_4_1229_chunk.app_id, count(*)
               Group Key: reg.reg_day, _hyper_4_1229_chunk.app_id, _hyper_4_1229_chunk.sub_channel
               ->  Group  (cost=1377873.71..1378004.46 rows=10460 width=28)
                     Output: reg.reg_day, _hyper_4_1229_chunk.app_id, _hyper_4_1229_chunk.sub_channel, reg.user_id, _hyper_4_1229_chunk.app_id, _hyper_4_1229_chunk.sub_channel
                     Group Key: reg.reg_day, _hyper_4_1229_chunk.app_id, _hyper_4_1229_chunk.sub_channel, reg.user_id
                     ->  Sort  (cost=1377873.71..1377899.86 rows=10460 width=20)
                           Output: reg.reg_day, reg.user_id, _hyper_4_1229_chunk.app_id, _hyper_4_1229_chunk.sub_channel
                           Sort Key: reg.reg_day, _hyper_4_1229_chunk.app_id, _hyper_4_1229_chunk.sub_channel, reg.user_id
                           ->  Merge Join  (cost=652242.46..1377175.37 rows=10460 width=20)
                                 Output: reg.reg_day, reg.user_id, _hyper_4_1229_chunk.app_id, _hyper_4_1229_chunk.sub_channel
                                 Merge Cond: ((date_trunc('day'::text, _hyper_4_1229_chunk.count_time)) = (date_trunc('day'::text, reg.count_time)))
                                 Join Filter: (reg.user_id = _hyper_4_1229_chunk.user_id)
                                 ->  Group  (cost=650558.24..1318952.53 rows=979872 width=20)
                                       Output: (date_trunc('day'::text, _hyper_4_1229_chunk.count_time)), _hyper_4_1229_chunk.sub_channel, _hyper_4_1229_chunk.app_id, _hyper_4_1229_chunk.user_id
                                       Group Key: (date_trunc('day'::text, _hyper_4_1229_chunk.count_time)), _hyper_4_1229_chunk.app_id, _hyper_4_1229_chunk.sub_channel, _hyper_4_1229_chunk.user_id
                                       ->  Gather Merge  (cost=650558.24..1267509.25 rows=4899360 width=20)
                                             Output: (date_trunc('day'::text, _hyper_4_1229_chunk.count_time)), _hyper_4_1229_chunk.sub_channel, _hyper_4_1229_chunk.app_id, _hyper_4_1229_chunk.user_id
                                             Workers Planned: 5
                                             ->  Group  (cost=649558.16..676504.67 rows=979872 width=20)
                                                   Output: (date_trunc('day'::text, _hyper_4_1229_chunk.count_time)), _hyper_4_1229_chunk.sub_channel, _hyper_4_1229_chunk.app_id, _hyper_4_1229_chunk.user_id
                                                   Group Key: (date_trunc('day'::text, _hyper_4_1229_chunk.count_time)), _hyper_4_1229_chunk.app_id, _hyper_4_1229_chunk.sub_channel, _hyper_4_1229_chunk.user_id
                                                   ->  Sort  (cost=649558.16..654457.53 rows=1959746 width=20)
                                                         Output: (date_trunc('day'::text, _hyper_4_1229_chunk.count_time)), _hyper_4_1229_chunk.sub_channel, _hyper_4_1229_chunk.app_id, _hyper_4_1229_chunk.user_id
                                                         Sort Key: (date_trunc('day'::text, _hyper_4_1229_chunk.count_time)), _hyper_4_1229_chunk.app_id, _hyper_4_1229_chunk.sub_channel, _hyper_4_1229_chunk.user_id
                                                         ->  Result  (cost=0.00..404552.30 rows=1959746 width=20)
                                                               Output: date_trunc('day'::text, _hyper_4_1229_chunk.count_time), _hyper_4_1229_chunk.sub_channel, _hyper_4_1229_chunk.app_id, _hyper_4_1229_chunk.user_id
                                                               ->  Parallel Append  (cost=0.00..380055.48 rows=1959746 width=20)

正确语句用了日期和 userid 排序, 然后正确的双排序 join 了 不正确的语句没用日期排序,最后导致了 M*N?

有人能帮我解释下是不是这个理啊 orz

DBA 脑子要要怎么长才能了了解数据库的各种坑啊......

这 DBA 要怎么干啊 orz,我光测一条语句就要疯了...