cxh116

V2EX 第 13872 号会员，加入于 2011-11-23 09:30:45 +08:00

今日活跃度排名 7624

ciro9527

http://blog.mangege.com GitHub

mangege

时代品牌 http://www.shidaipinpai.com/

cxh116 提问技术话题好玩工作信息交易信息城市相关

根据 cxh116 的设置，主题列表被隐藏

二手交易相关的信息，包括已关闭的交易，不会被隐藏

cxh116 最近回复了

2 天前

回复了 qping 创建的主题 › 耳机 › 百元左右的耳机推荐

就个人体验来看, 漫步者那音质还没有 QCY 好 🐶

3 天前

回复了 fffffhan 创建的主题 › 问与答 › macOS， 200 元以下的无线鼠标都是垃圾(doge

确实,200 以下都是鼠垃圾推荐买 Logitech MX Master 3, 不到 1000 块钱,真便宜.

3 天前

回复了 2ruowqe9r 创建的主题 › 硬件 › 给读高中的表弟买电脑， 3~4 千价位

这价位玩游戏还是得上神船🐶...

7 天前

回复了 vicalloy 创建的主题 › iPhone › 刚把手机从洗衣机里抢救出来

https://www.bilibili.com/video/BV1Fh41187W6

大事故车在维修师傅给力的情况下,可以碰.
进水车万万不能碰,因为不知道什么时候把你扔在路上了.

8 天前

回复了 tonoon 创建的主题 › Google › G Suite 免费版没了！

已经换成 zoho ，5 个免费用户配合 catch all 够用了

10 天前

回复了 deepout 创建的主题 › 宽带症候群 › 新装联通宽带感受， 2400 块的哇为终端就这？

cu 不是中国联通的缩写吗？这锅得找中国联通

13 天前

回复了 uni 创建的主题 › 服务器 › 现在怎么买服务器比较便宜？

kimsufi 洋垃圾独服，桌面主板与内存，服务器 cpu 与机械硬盘，不过 cpu 只有 8 核。没活动时要初装费。

14 天前

回复了 cxh116 创建的主题 › 问与答 › 单机 8 核 32G, 用什么技术才可以做单表 20 亿数据的过滤查询?

@zhoujinjing09 Kimsufi 的洋垃圾独服。

14 天前

回复了 cxh116 创建的主题 › 问与答 › 单机 8 核 32G, 用什么技术才可以做单表 20 亿数据的过滤查询?

@dayeye2006199 @buddyy

感谢感谢,针对网友们的建议,做出以下改变尝试.

1. 改成 ClickHouse ,主键默认使用的是稀疏索引.
2. 使用 Table 自带的 Partition ,取 hash 前 8 位做分区.

14 天前

回复了 cxh116 创建的主题 › 问与答 › 单机 8 核 32G, 用什么技术才可以做单表 20 亿数据的过滤查询?

@outdoorlife

Table "public.transactions"
Column | Type | Collation | Nullable | Default
-----------------------------+--------------------------+-----------+----------+---------
hash | character varying(66) | | |
nonce | bigint | | |
transaction_index | bigint | | |
from_address | character varying(42) | | |
to_address | character varying(42) | | |
value | numeric(38,0) | | |
gas | bigint | | |
gas_price | bigint | | |
input | text | | |
receipt_cumulative_gas_used | bigint | | |
receipt_gas_used | bigint | | |
receipt_contract_address | character varying(42) | | |
receipt_root | character varying(66) | | |
receipt_status | bigint | | |
block_timestamp | timestamp with time zone | | not null |
block_number | bigint | | |
block_hash | character varying(66) | | |
max_fee_per_gas | bigint | | |
max_priority_fee_per_gas | bigint | | |
transaction_type | bigint | | |
receipt_effective_gas_price | bigint | | |
Indexes:
"transactions_block_number" btree (block_number DESC)
"transactions_block_timestamp_idx" btree (block_timestamp DESC)
"transactions_from_address_block_timestamp_index" btree (from_address, block_timestamp DESC)
"transactions_hash" UNIQUE, btree (block_timestamp DESC, hash)
"transactions_to_address_block_timestamp_index" btree (to_address, block_timestamp DESC)
Triggers:
ts_insert_blocker BEFORE INSERT ON transactions FOR EACH ROW EXECUTE FUNCTION _timescaledb_internal.insert_blocker()
Number of child tables: 70 (Use \d+ to list them.)

EXPLAIN select * from transactions where hash='0xd22b255ddcce6af9daa39fb0b2857096314f56b3a92908a9258695b1f4591ad8' limit 1;

-[ RECORD 1 ]----------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Limit (cost=0.28..853977.96 rows=1 width=886)
-[ RECORD 2 ]----------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | -> Append (cost=0.28..59778438.09 rows=70 width=886)
-[ RECORD 3 ]----------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | -> Index Scan using _hyper_2_149_chunk_transactions_hash on _hyper_2_149_chunk (cost=0.28..50.48 rows=1 width=659)
-[ RECORD 4 ]----------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Index Cond: ((hash)::text = '0xd22b255ddcce6af9daa39fb0b2857096314f56b3a92908a9258695b1f4591ad8'::text)
-[ RECORD 5 ]----------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | -> Index Scan using _hyper_2_151_chunk_transactions_hash on _hyper_2_151_chunk (cost=0.42..2496.59 rows=1 width=718)
-[ RECORD 6 ]----------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Index Cond: ((hash)::text = '0xd22b255ddcce6af9daa39fb0b2857096314f56b3a92908a9258695b1f4591ad8'::text)
-[ RECORD 7 ]----------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | -> Index Scan using _hyper_2_153_chunk_transactions_hash on _hyper_2_153_chunk (cost=0.42..28396.85 rows=1 width=774)
-[ RECORD 8 ]----------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Index Cond: ((hash)::text = '0xd22b255ddcce6af9daa39fb0b2857096314f56b3a92908a9258695b1f4591ad8'::text)
-[ RECORD 9 ]----------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | -> Index Scan using _hyper_2_155_chunk_transactions_hash on _hyper_2_155_chunk (cost=0.42..20489.98 rows=1 width=752)
-[ RECORD 10 ]---------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Index Cond: ((hash)::text = '0xd22b255ddcce6af9daa39fb0b2857096314f56b3a92908a9258695b1f4591ad8'::text)
-[ RECORD 11 ]---------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | -> Index Scan using _hyper_2_157_chunk_transactions_hash on _hyper_2_157_chunk (cost=0.42..30703.97 rows=1 width=792)
-[ RECORD 12 ]---------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Index Cond: ((hash)::text = '0xd22b255ddcce6af9daa39fb0b2857096314f56b3a92908a9258695b1f4591ad8'::text)
-[ RECORD 13 ]---------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | -> Index Scan using _hyper_2_159_chunk_transactions_hash on _hyper_2_159_chunk (cost=0.43..34880.92 rows=1 width=800)
-[ RECORD 14 ]---------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Index Cond: ((hash)::text = '0xd22b255ddcce6af9daa39fb0b2857096314f56b3a92908a9258695b1f4591ad8'::text)
-[ RECORD 15 ]---------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | -> Index Scan using _hyper_2_161_chunk_transactions_hash on _hyper_2_161_chunk (cost=0.43..42360.63 rows=1 width=792)
-[ RECORD 16 ]---------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Index Cond: ((hash)::text = '0xd22b255ddcce6af9daa39fb0b2857096314f56b3a92908a9258695b1f4591ad8'::text)
-[ RECORD 17 ]---------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | -> Index Scan using _hyper_2_163_chunk_transactions_hash on _hyper_2_163_chunk (cost=0.43..49846.27 rows=1 width=775)
-[ RECORD 18 ]---------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Index Cond: ((hash)::text = '0xd22b255ddcce6af9daa39fb0b2857096314f56b3a92908a9258695b1f4591ad8'::text)
-[ RECORD 19 ]---------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | -> Index Scan using _hyper_2_165_chunk_transactions_hash on _hyper_2_165_chunk (cost=0.43..41498.32 rows=1 width=815)
-[ RECORD 20 ]---------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Index Cond: ((hash)::text = '0xd22b255ddcce6af9daa39fb0b2857096314f56b3a92908a9258695b1f4591ad8'::text)
-[ RECORD 21 ]---------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | -> Index Scan using _hyper_2_167_chunk_transactions_hash on _hyper_2_167_chunk (cost=0.43..33756.32 rows=1 width=813)
-[ RECORD 22 ]---------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Index Cond: ((hash)::text = '0xd22b255ddcce6af9daa39fb0b2857096314f56b3a92908a9258695b1f4591ad8'::text)

» cxh116 创建的更多回复