postgresql - timescaledb postgresql 性能问题
问题描述
我有一个问题需要帮助，我有一个包含 67M 行的表，如下所示
SELECT * FROM db_009a005a_df_downloaded_grand order by "timestamp" desc limit 1000;
| 象征 | 时间戳 | 体积 | 关 | 高的 | 低的 | 打开 |
|---|---|---|---|---|---|---|
| ANT_USDT | 2021-08-31 19:55:00 | 13.198 | 5.111 | 5.123 | 5.11 | 5.123 |
| FET_USDT | 2021-08-31 19:55:00 | 26.443781800000004 | 0.7253 | 0.7255 | 0.7224 | 0.7246 |
| ONC_USDT | 2021-08-31 19:55:00 | 47.89 | 0.363 | 0.3633 | 0.3628 | 0.3633 |
| FSN_USDT | 2021-08-31 19:55:00 | 1044.8977859 | 0.5454 | 0.5509 | 0.5454 | 0.5499 |
| PCX_USDT | 2021-08-31 19:55:00 | 1158.901 | 3.926 | 3.934 | 3.913 | 3.925 |
| PRQ_USDT | 2021-08-31 19:55:00 | 681.83529405 | 0.6791 | 0.6807 | 0.6757 | 0.6805 |
| CREDIT_USDT | 2021-08-31 19:55:00 | 3045.81454624 | 0.10573 | 0.10662 | 0.10567 | 0.10567 |
| JFI_USDT | 2021-08-31 19:55:00 | 0.6434 | 47.08 | 47.1 | 46.91 | 46.91 |
| ENJ_USDT | 2021-08-31 19:55:00 | 2613.32204107 | 2.018 | 2.0315 | 2.018 | 2.0294 |
我有我认为已正确生成的索引
| 表名 | 索引名 | 索引定义 |
|---|---|---|
| db_009a005a_df_downloaded_grand | db_009a005a_df_downloaded_grand_symbol_timestamp_idx | 创建索引 db_009a005a_df_downloaded_grand_symbol_timestamp_idx ON public.db_009a005a_df_downloaded_grand 使用 btree（符号，“时间戳”DESC） |
| db_009a005a_df_downloaded_grand | db_009a005a_df_downloaded_grand_timestamp_idx | 创建索引 db_009a005a_df_downloaded_grand_timestamp_idx ON public.db_009a005a_df_downloaded_grand 使用 btree（“时间戳”DESC） |
| db_009a005a_df_downloaded_grand | idx_symbol | 在 public.db_009a005a_df_downloaded_grand 上使用 btree（符号）创建索引 idx_symbol |
要查询的一个常见问题是检查每个符号的最新时间戳：
SELECT symbol, max("timestamp") FROM db_009a005a_df_downloaded_grand group by symbol;
| 象征 | 最大限度 |
|---|---|
| 100X_USDT | 2021-08-31 19:55:00 |
| 10SET_USDT | 2021-08-31 19:55:00 |
| 1INCH3L_USDT | 2021-08-31 19:20:00 |
| 1INCH3S_USDT | 2021-08-31 19:10:00 |
| 1INCH_USDT | 2021-08-31 19:55:00 |
| 88MPH_USDT | 2021-08-31 19:55:00 |
| A5T_USDT | 2021-08-31 19:55:00 |
| AAVE3L_USDT | 2021-08-31 19:55:00 |
| AAVE3S_USDT | 2021-08-31 19:30:00 |
然而，这需要 26 多秒才能让我回到 1000 行…… 这个速度合理吗？无论如何我可以让它更快吗？我在一个具有 8 个内核和 32GB 内存的专用 AWS 实例上……</p>
这是解释分析结果：
|QUERY PLAN|
|----------|
|Finalize GroupAggregate (cost=1061465.17..1061976.65 rows=1005 width=17) (actual time=14105.139..14221.082 rows=1027 loops=1)|
| Group Key: db_009a005a_df_downloaded_grand.symbol|
| -> Gather Merge (cost=1061465.17..1061946.50 rows=4020 width=17) (actual time=14105.095..14219.754 rows=4841 loops=1)|
| Workers Planned: 4|
| Workers Launched: 4|
| -> Sort (cost=1060465.11..1060467.62 rows=1005 width=17) (actual time=13801.604..13801.931 rows=968 loops=5)|
| Sort Key: db_009a005a_df_downloaded_grand.symbol|
| Sort Method: quicksort Memory: 99kB|
| Worker 0: Sort Method: quicksort Memory: 97kB|
| Worker 1: Sort Method: quicksort Memory: 100kB|
| Worker 2: Sort Method: quicksort Memory: 101kB|
| Worker 3: Sort Method: quicksort Memory: 101kB|
| -> Partial HashAggregate (cost=1060404.94..1060414.99 rows=1005 width=17) (actual time=13798.244..13798.888 rows=968 loops=5)|
| Group Key: db_009a005a_df_downloaded_grand.symbol|
| Batches: 1 Memory Usage: 193kB|
| Worker 0: Batches: 1 Memory Usage: 193kB|
| Worker 1: Batches: 1 Memory Usage: 193kB|
| Worker 2: Batches: 1 Memory Usage: 193kB|
| Worker 3: Batches: 1 Memory Usage: 193kB|
| -> Parallel Custom Scan (ChunkAppend) on db_009a005a_df_downloaded_grand (cost=0.42..969198.01 rows=18241386 width=17) (actual time=0.097..4596.166 rows=10667360 loops=5)|
| Chunks excluded during startup: 1040|
| -> Parallel Index Only Scan using _hyper_67_923_chunk_db_009a005a_df_downloaded_grand_symbol_time on _hyper_67_923_chunk (cost=0.42..4400.92 rows=78719 width=17) (actual time=0.047..92.197 rows=244028 loops=1)|
| Index Cond: ("timestamp" > '2020-08-31'::date)|
| Heap Fetches: 0|
| -> Parallel Index Only Scan using _hyper_67_926_chunk_db_009a005a_df_downloaded_grand_symbol_time on _hyper_67_926_chunk (cost=0.42..4378.26 rows=78434 width=17) (actual time=0.097..74.898 rows=243146 loops=1)|
| Index Cond: ("timestamp" > '2020-08-31'::date)|
| Heap Fetches: 0|
| -> Parallel Index Only Scan using _hyper_67_925_chunk_db_009a005a_df_downloaded_grand_symbol_time on _hyper_67_925_chunk (cost=0.42..4365.58 rows=77821 width=17) (actual time=0.108..66.222 rows=241246 loops=1)|
| Index Cond: ("timestamp" > '2020-08-31'::date)|
| Heap Fetches: 0|
| -> Parallel Index Only Scan using _hyper_67_1034_chunk_db_009a005a_df_downloaded_grand_symbol_tim on _hyper_67_1034_chunk (cost=0.42..4259.76 rows=75697 width=17) (actual time=0.103..74.135 rows=234662 loops=1)|
| Index Cond: ("timestamp" > '2020-08-31'::date)|
| Heap Fetches: 0|
| -> Parallel Index Only Scan using _hyper_67_927_chunk_db_009a005a_df_downloaded_grand_symbol_time on _hyper_67_927_chunk (cost=0.42..4229.76 rows=75755 width=17) (actual time=0.116..89.971 rows=234839 loops=1)|
| Index Cond: ("timestamp" > '2020-08-31'::date)|
| Heap Fetches: 0|
| -> Parallel Index Only Scan using _hyper_67_999_chunk_db_009a005a_df_downloaded_grand_symbol_time on _hyper_67_999_chunk (cost=0.42..4186.99 rows=75196 width=17) (actual time=0.118..106.632 rows=233108 loops=1)|
| Index Cond: ("timestamp" > '2020-08-31'::date)|
| Heap Fetches: 0|
| -> Parallel Index Only Scan using _hyper_67_933_chunk_db_009a005a_df_downloaded_grand_symbol_time on _hyper_67_933_chunk (cost=0.42..4139.58 rows=74167 width=17) (actual time=0.091..64.715 rows=229918 loops=1)|
| Index Cond: ("timestamp" > '2020-08-31'::date)|
| Heap Fetches: 0|
| -> Parallel Index Only Scan using _hyper_67_936_chunk_db_009a005a_df_downloaded_grand_symbol_time on _hyper_67_936_chunk (cost=0.42..4117.81 rows=74108 width=17) (actual time=0.078..73.137 rows=229735 loops=1)|
| Index Cond: ("timestamp" > '2020-08-31'::date)|
| Heap Fetches: 0|
| -> Parallel Index Only Scan using _hyper_67_1032_chunk_db_009a005a_df_downloaded_grand_symbol_tim on _hyper_67_1032_chunk (cost=0.42..4105.84 rows=73847 width=17) (actual time=0.102..74.648 rows=228926 loops=1)|
| Index Cond: ("timestamp" > '2020-08-31'::date)|
| Heap Fetches: 0|
| -> Parallel Index Only Scan using _hyper_67_935_chunk_db_009a005a_df_downloaded_grand_symbol_time on _hyper_67_935_chunk (cost=0.42..4061.01 rows=72466 width=17) (actual time=0.082..67.167 rows=224644 loops=1)|
| Index Cond: ("timestamp" > '2020-08-31'::date)|
| Heap Fetches: 0|
| -> Parallel Index Only Scan using _hyper_67_1031_chunk_db_009a005a_df_downloaded_grand_symbol_tim on _hyper_67_1031_chunk (cost=0.42..4055.36 rows=71998 width=17) (actual time=0.096..68.947 rows=223194 loops=1)|
| Index Cond: ("timestamp" > '2020-08-31'::date)|
| Heap Fetches: 0|
| -> Parallel Index Only Scan using _hyper_67_1033_chunk_db_009a005a_df_downloaded_grand_symbol_tim on _hyper_67_1033_chunk (cost=0.42..4046.55 rows=72792 width=17) (actual time=0.096..70.259 rows=225655 loops=1)|
| Index Cond: ("timestamp" > '2020-08-31'::date)|
| Heap Fetches: 0|
| -> Parallel Index Only Scan using _hyper_67_998_chunk_db_009a005a_df_downloaded_grand_symbol_time on _hyper_67_998_chunk (cost=0.42..4012.91 rows=71317 width=17) (actual time=0.095..68.714 rows=221083 loops=1)|
| Index Cond: ("timestamp" > '2020-08-31'::date)|
| Heap Fetches: 0|
| -> Parallel Index Only Scan using _hyper_67_941_chunk_db_009a005a_df_downloaded_grand_symbol_time on _hyper_67_941_chunk (cost=0.42..4012.40 rows=71566 width=17) (actual time=0.084..67.108 rows=221856 loops=1)|
| Index Cond: ("timestamp" > '2020-08-31'::date)|
| Heap Fetches: 0|
| -> Parallel Index Only Scan using _hyper_67_996_chunk_db_009a005a_df_downloaded_grand_symbol_time on _hyper_67_996_chunk (cost=0.42..3972.09 rows=70949 width=17) (actual time=0.076..67.270 rows=219943 loops=1)|
| Index Cond: ("timestamp" > '2020-08-31'::date)|
| Heap Fetches: 0|
| -> Parallel Index Only Scan using _hyper_67_1064_chunk_db_009a005a_df_downloaded_grand_symbol_tim on _hyper_67_1064_chunk (cost=0.42..3956.07 rows=70501 width=17) (actual time=0.098..68.073 rows=218552 loops=1)|
| Index Cond: ("timestamp" > '2020-08-31'::date)|
| Heap Fetches: 0|
| -> Parallel Index Only Scan using _hyper_67_959_chunk_db_009a005a_df_downloaded_grand_symbol_time on _hyper_67_959_chunk (cost=0.42..3949.26 rows=71388 width=17) (actual time=0.079..66.493 rows=221302 loops=1)|
| Index Cond: ("timestamp" > '2020-08-31'::date)|
| Heap Fetches: 0|
| -> Parallel Index Only Scan using _hyper_67_1030_chunk_db_009a005a_df_downloaded_grand_symbol_tim on _hyper_67_1030_chunk (cost=0.42..3942.66 rows=69899 width=17) (actual time=0.086..71.154 rows=216687 loops=1)|
| Index Cond: ("timestamp" > '2020-08-31'::date)|
| Heap Fetches: 0|
| -> Parallel Index Only Scan using _hyper_67_1028_chunk_db_009a005a_df_downloaded_grand_symbol_tim on _hyper_67_1028_chunk (cost=0.42..3940.07 rows=69193 width=17) (actual time=0.101..68.149 rows=214497 loops=1)|
| Index Cond: ("timestamp" > '2020-08-31'::date)|
| Heap Fetches: 0|
| -> Parallel Index Only Scan using _hyper_67_1065_chunk_db_009a005a_df_downloaded_grand_symbol_tim on _hyper_67_1065_chunk (cost=0.42..3920.26 rows=69490 width=17) (actual time=0.080..63.662 rows=215419 loops=1)|
| Index Cond: ("timestamp" > '2020-08-31'::date)|
| Heap Fetches: 0|
| -> Parallel Index Only Scan using _hyper_67_995_chunk_db_009a005a_df_downloaded_grand_symbol_time on _hyper_67_995_chunk (cost=0.42..3898.46 rows=69794 width=17) (actual time=0.116..66.686 rows=216360 loops=1)|
| Index Cond: ("timestamp" > '2020-08-31'::date)|
| Heap Fetches: 0|
| -> Parallel Index Only Scan using _hyper_67_938_chunk_db_009a005a_df_downloaded_grand_symbol_time on _hyper_67_938_chunk (cost=0.42..3896.60 rows=70234 width=17) (actual time=0.084..70.824 rows=217725 loops=1)|
| Index Cond: ("timestamp" > '2020-08-31'::date)|
| Heap Fetches: 0|
| -> Parallel Index Only Scan using _hyper_67_961_chunk_db_009a005a_df_downloaded_grand_symbol_time on _hyper_67_961_chunk (cost=0.42..3896.00 rows=69521 width=17) (actual time=0.073..62.237 rows=215516 loops=1)|
| Index Cond: ("timestamp" > '2020-08-31'::date)|
| Heap Fetches: 0|
| -> Parallel Index Only Scan using _hyper_67_937_chunk_db_009a005a_df_downloaded_grand_symbol_time on _hyper_67_937_chunk (cost=0.42..3894.92 rows=70316 width=17) (actual time=0.083..68.896 rows=217979 loops=1)|
| Index Cond: ("timestamp" > '2020-08-31'::date)|
| Heap Fetches: 0|
| -> Parallel Index Only Scan using _hyper_67_997_chunk_db_009a005a_df_downloaded_grand_symbol_time on _hyper_67_997_chunk (cost=0.42..3890.90 rows=69633 width=17) (actual time=0.072..67.991 rows=215861 loops=1)|
| Index Cond: ("timestamp" > '2020-08-31'::date)|
| Heap Fetches: 0|
| -> Parallel Index Only Scan using _hyper_67_1066_chunk_db_009a005a_df_downloaded_grand_symbol_tim on _hyper_67_1066_chunk (cost=0.42..3877.77 rows=69436 width=17) (actual time=0.093..62.445 rows=215252 loops=1)|
| Index Cond: ("timestamp" > '2020-08-31'::date)|
| Heap Fetches: 0|
| -> Parallel Index Only Scan using _hyper_67_1046_chunk_db_009a005a_df_downloaded_grand_symbol_tim on _hyper_67_1046_chunk (cost=0.42..3870.32 rows=69543 width=17) (actual time=0.098..67.763 rows=215583 loops=1)|
| Index Cond: ("timestamp" > '2020-08-31'::date)|
| Heap Fetches: 0|
| -> Parallel Index Only Scan using _hyper_67_1044_chunk_db_009a005a_df_downloaded_grand_symbol_tim on _hyper_67_1044_chunk (cost=0.42..3868.14 rows=69841 width=17) (actual time=0.171..71.637 rows=216508 loops=1)|
| Index Cond: ("timestamp" > '2020-08-31'::date)|
| Heap Fetches: 0|
| -> Parallel Index Only Scan using _hyper_67_960_chunk_db_009a005a_df_downloaded_grand_symbol_time on _hyper_67_960_chunk (cost=0.42..3859.53 rows=69880 width=17) (actual time=0.095..60.690 rows=216628 loops=1)|
| Index Cond: ("timestamp" > '2020-08-31'::date)|
| Heap Fetches: 0|
| -> Parallel Index Only Scan using _hyper_67_911_chunk_db_009a005a_df_downloaded_grand_symbol_time on _hyper_67_911_chunk (cost=0.42..3830.51 rows=68313 width=17) (actual time=0.080..66.339 rows=211769 loops=1)|
| Index Cond: ("timestamp" > '2020-08-31'::date)|
| Heap Fetches: 0|
| -> Parallel Index Only Scan using _hyper_67_921_chunk_db_009a005a_df_downloaded_grand_symbol_time on _hyper_67_921_chunk (cost=0.42..3826.92 rows=68800 width=17) (actual time=0.110..68.794 rows=213280 loops=1)|
| Index Cond: ("timestamp" > '2020-08-31'::date)|
| Heap Fetches: 0|
| -> Parallel Index Only Scan using _hyper_67_940_chunk_db_009a005a_df_downloaded_grand_symbol_time on _hyper_67_940_chunk (cost=0.42..3825.87 rows=69000 width=17) (actual time=0.034..89.527 rows=213900 loops=1)|
| Index Cond: ("timestamp" > '2020-08-31'::date)|
| Heap Fetches: 0|
| -> Parallel Index Only Scan using _hyper_67_962_chunk_db_009a005a_df_downloaded_grand_symbol_time on _hyper_67_962_chunk (cost=0.42..3824.56 rows=68828 width=17) (actual time=0.075..61.548 rows=213368 loops=1)|
| Index Cond: ("timestamp" > '2020-08-31'::date)|
| Heap Fetches: 0|
| -> Parallel Index Only Scan using _hyper_67_1063_chunk_db_009a005a_df_downloaded_grand_symbol_tim on _hyper_67_1063_chunk (cost=0.42..3824.14 rows=67922 width=18) (actual time=0.096..61.842 rows=210559 loops=1)|
| Index Cond: ("timestamp" > '2020-08-31'::date)|
| Heap Fetches: 0|
| -> Parallel Index Only Scan using _hyper_67_1043_chunk_db_009a005a_df_downloaded_grand_symbol_tim on _hyper_67_1043_chunk (cost=0.42..3821.81 rows=68018 width=17) (actual time=0.078..72.191 rows=210855 loops=1)|
| Index Cond: ("timestamp" > '2020-08-31'::date)|
| Heap Fetches: 0|
...另外 700 行...
|QUERY PLAN|
|----------|
| Filter: ("timestamp" > '2020-08-31'::date)|
| -> Parallel Seq Scan on _hyper_67_1289_chunk (cost=0.00..212.80 rows=6704 width=17) (actual time=0.021..2.248 rows=11397 loops=1)|
| Filter: ("timestamp" > '2020-08-31'::date)|
| -> Parallel Seq Scan on _hyper_67_1258_chunk (cost=0.00..204.51 rows=6441 width=17) (actual time=0.027..2.199 rows=10949 loops=1)|
| Filter: ("timestamp" > '2020-08-31'::date)|
|Planning Time: 337.300 ms|
|JIT:|
| Functions: 4083|
| Options: Inlining true, Optimization true, Expressions true, Deforming true|
| Timing: Generation 523.809 ms, Inlining 349.583 ms, Optimization 18877.665 ms, Emission 14550.830 ms, Total 34301.887 ms|
|Execution Time: 14334.565 ms|
解决方案
推荐阅读
- java - 如何获取应用了补丁的 openjdk-8-jdk 的源代码？
- node.js - 使用 $and 查询运算符与 mongoose find() 进行挑战
- docker - Lua os.excute() 函数不能正常工作
- python - 如何显示系列中的所有数据框列？
- java - 如何避免使用 JPA 在实体关系中违反外键约束
- php - 如何使用 php 将数组传递给 dompdf？
- html - aria-live（礼貌）在 Safari 中的表格标题上不起作用是否正常？
- c++ - 将用户输入与数组进行比较适用于一个函数，但不适用于另一个函数
- sql - 在间隙重新启动的 SQL 滚动平均值
- c++ - 如何包含“<>”级联标题