TimescaleDB 不支持并行查询? - V2EX
V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
lolizeppelin
V2EX    PostgreSQL

TimescaleDB 不支持并行查询?

  •  
  •   lolizeppelin 2019-07-05 00:14:42 +08:00 4257 次点击
    这是一个创建于 2294 天前的主题,其中的信息可能已经有所发展或是发生改变。

    pg11

    select count 没可以并行

    sdktest=> explain(analyze,verbose,costs) select count(*) from t_l_game_login_log; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=949099.50..949099.51 rows=1 width=8) (actual time=1926.216..1926.217 rows=1 loops=1) Output: count(*) -> Gather (cost=949099.46..949099.47 rows=12 width=8) (actual time=1923.655..1967.013 rows=13 loops=1) Output: (PARTIAL count(*)) Workers Planned: 12 Workers Launched: 12 -> Partial Aggregate (cost=948099.46..948099.47 rows=1 width=8) (actual time=1856.612..1856.613 rows=1 loops=13) Output: PARTIAL count(*) Worker 0: actual time=1849.929..1849.929 rows=1 loops=1 Worker 1: actual time=1855.286..1855.287 rows=1 loops=1 Worker 2: actual time=1848.387..1848.387 rows=1 loops=1 Worker 3: actual time=1849.642..1849.642 rows=1 loops=1 Worker 4: actual time=1849.865..1849.865 rows=1 loops=1 Worker 5: actual time=1849.815..1849.816 rows=1 loops=1 Worker 6: actual time=1859.493..1859.493 rows=1 loops=1 Worker 7: actual time=1855.420..1855.420 rows=1 loops=1 Worker 8: actual time=1849.899..1849.900 rows=1 loops=1 Worker 9: actual time=1849.641..1849.641 rows=1 loops=1 Worker 10: actual time=1849.872..1849.872 rows=1 loops=1 Worker 11: actual time=1849.886..1849.887 rows=1 loops=1 

    不是 count 就不行了...

    sdktest=> explain(analyze,verbose,costs) select time_bucket('1 days', count_time) as day1, COUNT(id) from t_l_game_login_log where count_time > now() - interval '60 day' group by day1, app_id; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=807049.04..807172.99 rows=9916 width=20) (actual time=12512.462..12513.110 rows=2755 loops=1) Output: (time_bucket('1 day'::interval, t_l_game_login_log.count_time)), count(t_l_game_login_log.id), t_l_game_login_log.app_id Group Key: time_bucket('1 day'::interval, t_l_game_login_log.count_time), t_l_game_login_log.app_id -> Custom Scan (ConstraintAwareAppend) (cost=0.00..712676.29 rows=12583034 width=16) (actual time=0.065..10334.102 rows=12650070 loops=1) Output: time_bucket('1 day'::interval, t_l_game_login_log.count_time), t_l_game_login_log.app_id, t_l_game_login_log.id Hypertable: t_l_game_login_log Chunks left after exclusion: 36 -> Append (cost=0.00..681218.70 rows=12583034 width=16) (actual time=0.063..8630.928 rows=12650070 loops=1) -> Index Scan using _hyper_32_1429_chunk_t_l_game_login_log_count_time_idx on _timescaledb_internal._hyper_32_1429_chunk (cost=0.43..14150.17 rows=259580 width=16) (actual time=0.062..154.435 rows=277763 loops=1) Output: _hyper_32_1429_chunk.count_time, _hyper_32_1429_chunk.id, _hyper_32_1429_chunk.app_id Index Cond: (_hyper_32_1429_chunk.count_time > (now() - '60 days'::interval)) -> Index Scan using _hyper_32_1430_chunk_t_l_game_login_log_count_time_idx on _timescaledb_internal._hyper_32_1430_chunk (cost=0.43..5594.26 rows=106791 width=16) (actual time=0.058..56.189 rows=106399 loops=1) Output: _hyper_32_1430_chunk.count_time, _hyper_32_1430_chunk.id, _hyper_32_1430_chunk.app_id Index Cond: (_hyper_32_1430_chunk.count_time > (now() - '60 days'::interval)) -> Index Scan using _hyper_32_1431_chunk_t_l_game_login_log_count_time_idx on _timescaledb_internal._hyper_32_1431_chunk (cost=0.43..11419.14 rows=213147 width=16) (actual time=0.067..141.589 rows=257787 loops=1) Output: _hyper_32_1431_chunk.count_time, _hyper_32_1431_chunk.id, _hyper_32_1431_chunk.app_id Index Cond: (_hyper_32_1431_chunk.count_time > (now() - '60 days'::interval)) -> Index Scan using _hyper_32_1432_chunk_t_l_game_login_log_count_time_idx on _timescaledb_internal._hyper_32_1432_chunk (cost=0.42..2065.82 rows=39474 width=16) (actual time=0.064..26.715 rows=44729 loops=1) Output: _hyper_32_1432_chunk.count_time, _hyper_32_1432_chunk.id, _hyper_32_1432_chunk.app_id Index Cond: (_hyper_32_1432_chunk.count_time > (now() - '60 days'::interval)) -> Seq Scan on _timescaledb_internal._hyper_32_1445_chunk (cost=0.00..27784.83 rows=572733 width=16) (actual time=0.015..331.596 rows=572733 loops=1) Output: _hyper_32_1445_chunk.count_time, _hyper_32_1445_chunk.id, _hyper_32_1445_chunk.app_id Filter: (_hyper_32_1445_chunk.count_time > (now() - '60 days'::interval)) -> Seq Scan on _timescaledb_internal._hyper_32_1446_chunk (cost=0.00..31036.78 rows=627016 width=16) (actual time=0.016..364.134 rows=626923 loops=1) Output: _hyper_32_1446_chunk.count_time, _hyper_32_1446_chunk.id, _hyper_32_1446_chunk.app_id Filter: (_hyper_32_1446_chunk.count_time > (now() - '60 days'::interval)) -> Seq Scan on _timescaledb_internal._hyper_32_1447_chunk (cost=0.00..12388.78 rows=262216 width=16) (actual time=0.016..149.345 rows=262216 loops=1) Output: _hyper_32_1447_chunk.count_time, _hyper_32_1447_chunk.id, _hyper_32_1447_chunk.app_id 

    强制并行查询也不行

    是我的问题还是 TimescaleDB 不支持并行?

    google 也查不到 orz

    6 条回复    2019-07-06 20:14:20 +08:00
    lolizeppelin
        1
    lolizeppelin  
    OP
       2019-07-05 00:47:22 +08:00
    是因为 Custom Scan (ConstraintAwareAppend) (cost=0.00..712676.29 rows=12583034 width=16) (actual time=0.065..10334.102 rows=12650070 loops=1)

    时间太长导致的?
    lolizeppelin
        2
    lolizeppelin  
    OP
       2019-07-05 01:01:45 +08:00
    感觉好像是 扫描 chunk 时间比较短没必要并行?
    时间都花费在刚开始排除不需要的 chunk 上了?
    gtlions
        3
    gtlions  
       2019-07-05 08:43:51 +08:00 via iPhone
    没记错的话,是的不支持,当初在做技术选型的时候验证了这个,看介绍和一些自己的测试确实挺好的,然后在加载入库验证的是否发现怎么性能这么差,才发现居然会锁表,然后,没有然后了……
    lolizeppelin
        4
    lolizeppelin  
    OP
       2019-07-05 10:46:11 +08:00
    @gtlions

    不会吧
    https://medium.com/@aiven_io/timescaledb-101-the-why-what-and-how-9c0eb08a7c0b

    3.2. The second most optimal query
    看这里... 原来好像是支持的

    因为新版加了事务导致?还是说现在商业版才支持了 0 0 ?
    lolizeppelin
        5
    lolizeppelin  
    OP
       2019-07-05 12:50:45 +08:00
    我联系了官方的客服.... 对面也说是支持的...
    卧槽 我做错了啥
    lolizeppelin
        6
    lolizeppelin  
    OP
       2019-07-06 20:14:20 +08:00
    @gtlions

    ...我似乎找到原因了
    where 条件里不带秒 '2019-01-01 02:00:00' 可以并行

    where 雕件里带秒'2019-05-07 19:59:37'

    就没法并行了.........?卧槽这什么鬼
    关于     帮助文档     自助推广系统     博客     API     FAQ     Solana     3167 人在线   最高记录 6679       Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 23ms UTC 10:56 PVG 18:56 LAX 03:56 JFK 06:56
    Do have faith in what you're doing.
    ubao snddm index pchome yahoo rakuten mypaper meadowduck bidyahoo youbao zxmzxm asda bnvcg cvbfg dfscv mmhjk xxddc yybgb zznbn ccubao uaitu acv GXCV ET GDG YH FG BCVB FJFH CBRE CBC GDG ET54 WRWR RWER WREW WRWER RWER SDG EW SF DSFSF fbbs ubao fhd dfg ewr dg df ewwr ewwr et ruyut utut dfg fgd gdfgt etg dfgt dfgd ert4 gd fgg wr 235 wer3 we vsdf sdf gdf ert xcv sdf rwer hfd dfg cvb rwf afb dfh jgh bmn lgh rty gfds cxv xcv xcs vdas fdf fgd cv sdf tert sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf sdf shasha9178 shasha9178 shasha9178 shasha9178 shasha9178 liflif2 liflif2 liflif2 liflif2 liflif2 liblib3 liblib3 liblib3 liblib3 liblib3 zhazha444 zhazha444 zhazha444 zhazha444 zhazha444 dende5 dende denden denden2 denden21 fenfen9 fenf619 fen619 fenfe9 fe619 sdf sdf sdf sdf sdf zhazh90 zhazh0 zhaa50 zha90 zh590 zho zhoz zhozh zhozho zhozho2 lislis lls95 lili95 lils5 liss9 sdf0ty987 sdft876 sdft9876 sdf09876 sd0t9876 sdf0ty98 sdf0976 sdf0ty986 sdf0ty96 sdf0t76 sdf0876 df0ty98 sf0t876 sd0ty76 sdy76 sdf76 sdf0t76 sdf0ty9 sdf0ty98 sdf0ty987 sdf0ty98 sdf6676 sdf876 sd876 sd876 sdf6 sdf6 sdf9876 sdf0t sdf06 sdf0ty9776 sdf0ty9776 sdf0ty76 sdf8876 sdf0t sd6 sdf06 s688876 sd688 sdf86