-
记录一下 dashboard 性能优化 (10s->1ms) at 2021年05月13日
嗯，只是模拟一下，有的时候不知道预期的返回结果，比如有些节点多，有些节点少的情况
-
记录一下 dashboard 性能优化 (10s->1ms) at 2021年05月13日
整个表我没有去算，而且可能没有 vacuum 了最近，我拿了单条的刚刚看了一下
explain (format json) select * from nb_command_analyses where created_at > '2021-01-01' and device_id = 'F34479F6F935'; QUERY PLAN ------------------------------------------------------------------------------------- [ + { + "Plan": { + "Node Type": "Index Scan", + "Parallel Aware": false, + "Scan Direction": "Forward", + "Index Name": "nba_edcm_idx", + "Relation Name": "nb_command_analyses", + "Alias": "nb_command_analyses", + "Startup Cost": 0.57, + "Total Cost": 1291320.82, + "Plan Rows": 3669, + "Plan Width": 222, + "Index Cond": "((device_id)::text = 'F34479F6F935'::text)", + "Filter": "(created_at > '2021-01-01 00:00:00'::timestamp without time zone)"+ } + } + ] (1 row) explain (format json) select * from nb_command_analyses where created_at > '2021-02-01' and device_id = 'F34479F6F935'; QUERY PLAN ------------------------------------------------------------------------------------- [ + { + "Plan": { + "Node Type": "Index Scan", + "Parallel Aware": false, + "Scan Direction": "Forward", + "Index Name": "nba_edcm_idx", + "Relation Name": "nb_command_analyses", + "Alias": "nb_command_analyses", + "Startup Cost": 0.57, + "Total Cost": 1291320.82, + "Plan Rows": 2938, + "Plan Width": 222, + "Index Cond": "((device_id)::text = 'F34479F6F935'::text)", + "Filter": "(created_at > '2021-02-01 00:00:00'::timestamp without time zone)"+ } + } + ] (1 row) explain (format json) select * from nb_command_analyses where created_at > '2021-03-01' and device_id = 'F34479F6F935'; QUERY PLAN ------------------------------------------------------------------------------------- [ + { + "Plan": { + "Node Type": "Index Scan", + "Parallel Aware": false, + "Scan Direction": "Forward", + "Index Name": "nba_edcm_idx", + "Relation Name": "nb_command_analyses", + "Alias": "nb_command_analyses", + "Startup Cost": 0.57, + "Total Cost": 1291320.82, + "Plan Rows": 2416, + "Plan Width": 222, + "Index Cond": "((device_id)::text = 'F34479F6F935'::text)", + "Filter": "(created_at > '2021-03-01 00:00:00'::timestamp without time zone)"+ } + } + ] (1 row) explain (format json) select * from nb_command_analyses where created_at > '2021-04-01' and device_id = 'F34479F6F935'; QUERY PLAN ------------------------------------------------------------------------------------- [ + { + "Plan": { + "Node Type": "Index Scan", + "Parallel Aware": false, + "Scan Direction": "Forward", + "Index Name": "nba_edcm_idx", + "Relation Name": "nb_command_analyses", + "Alias": "nb_command_analyses", + "Startup Cost": 0.57, + "Total Cost": 1291320.82, + "Plan Rows": 1401, + "Plan Width": 222, + "Index Cond": "((device_id)::text = 'F34479F6F935'::text)", + "Filter": "(created_at > '2021-04-01 00:00:00'::timestamp without time zone)"+ } + } + ] (1 row) explain (format json) select * from nb_command_analyses where created_at > '2021-05-01' and device_id = 'F34479F6F935'; QUERY PLAN ------------------------------------------------------------------------------------- [ + { + "Plan": { + "Node Type": "Index Scan", + "Parallel Aware": false, + "Scan Direction": "Forward", + "Index Name": "nba_edcm_idx", + "Relation Name": "nb_command_analyses", + "Alias": "nb_command_analyses", + "Startup Cost": 0.57, + "Total Cost": 1291320.82, + "Plan Rows": 310, + "Plan Width": 222, + "Index Cond": "((device_id)::text = 'F34479F6F935'::text)", + "Filter": "(created_at > '2021-05-01 00:00:00'::timestamp without time zone)"+ } + } + ] (1 row) 结果是 1.1 -> 3669 2.1 -> 2938 3.1 -> 2416 4.1 -> 1401 5.1 -> 310 实际count 下来 1.1 -> 3 select count(*) from nb_command_analyses where created_at > '2021-01-01' and device_id = 'F34479F6F935'; count ------- 3 (1 row)这个表最后一条
select id from nb_command_analyses order by id desc limit 1; id ---------- 72204914 (1 row) -
记录一下 dashboard 性能优化 (10s->1ms) at 2021年05月13日
QUERY PLAN ----------------------------------------------- [ + { + "Plan": { + "Node Type": "Seq Scan", + "Parallel Aware": false, + "Relation Name": "nb_command_analyses",+ "Alias": "nb_command_analyses", + "Startup Cost": 0.00, + "Total Cost": 2686346.72, + "Plan Rows": 71053872, + "Plan Width": 222 + } + } + ] (1 row)OK 了~ 眼瞎，没有看仔细
带索引的和只有 filter 的条件都很快
-
请问 Ruby 中 range 可以间隔生成数据吗？ at 2021年05月13日
"727562792d6368696e612e6f7267".scan(/../)
-
记录一下 dashboard 性能优化 (10s->1ms) at 2021年05月13日
表大一点，计划会复杂，即使拿里层的 Plan Rows 数据也取不出来
olive=> explain (format json) select count(*) from audits; -[ RECORD 1 ]------------------------------------------------------------- QUERY PLAN | [ + | { + | "Plan": { + | "Node Type": "Aggregate", + | "Strategy": "Plain", + | "Partial Mode": "Finalize", + | "Parallel Aware": false, + | "Startup Cost": 1596821.82, + | "Total Cost": 1596821.83, + | "Plan Rows": 1, + | "Plan Width": 8, + | "Plans": [ + | { + | "Node Type": "Gather", + | "Parent Relationship": "Outer", + | "Parallel Aware": false, + | "Startup Cost": 1596821.60, + | "Total Cost": 1596821.81, + | "Plan Rows": 2, + | "Plan Width": 8, + | "Workers Planned": 2, + | "Single Copy": false, + | "Plans": [ + | { + | "Node Type": "Aggregate", + | "Strategy": "Plain", + | "Partial Mode": "Partial", + | "Parent Relationship": "Outer", + | "Parallel Aware": false, + | "Startup Cost": 1595821.60, + | "Total Cost": 1595821.61, + | "Plan Rows": 1, + | "Plan Width": 8, + | "Plans": [ + | { + | "Node Type": "Index Only Scan", + | "Parent Relationship": "Outer", + | "Parallel Aware": true, + | "Scan Direction": "Forward", + | "Index Name": "index_audits_on_created_at",+ | "Relation Name": "audits", + | "Alias": "audits", + | "Startup Cost": 0.56, + | "Total Cost": 1548601.80, + | "Plan Rows": 18887923, + | "Plan Width": 0 + | } + | ] + | } + | ] + | } + | ] + | } + | } + | ]olive=> select id from audits order by id desc limit 1; -[ RECORD 1 ] id | 45752411不知道是不是版本差异
psql (13.1, server 11.8)
-
Rails 里面使用 jwt_token 来鉴权 at 2019年10月08日
不建议把 username、user_type、mobile 这些私密信息放到 payload 里面，jwt 不 verify 也可先解出来的
{"exp_at"=>"2019-10-06 17:57:54 +0800", "id"=>4, "username"=>"纪", "user_type"=>"super_admin", "mobile"=>"177"}, {"alg"=>"HS256"}
-
一招秒杀 N+1 agg 问题 at 2018年11月11日
嗯，我忘了，我没有 deleted_at 字段，#21 的那里语句写的是按例子来的，实际的是下面这个
SELECT orders.*, aggs.* FROM "orders" LEFT JOIN (SELECT order_id, COUNT(*) AS items_count FROM "order_items" GROUP BY "order_items"."order_id") AS aggs ON aggs.order_id = orders.id ORDER BY orders.id DESC LIMIT 10; -
一招秒杀 N+1 agg 问题 at 2018年11月11日
和索引无关，本地索引是对的
order_items 的数据量为 20W+ orders 为 5W+
使用 LEFT JOIN LATERAL 在子查询里面多加 where 条件后好。感觉还是里面的查询不知道要使用 order_id 的索引来过滤数据，需要强制指定。如果没有这种用法，是不是就只能用子查询了？第一次见到这种写法
又学了一招 -
一招秒杀 N+1 agg 问题 at 2018年11月10日
SELECT orders.*, aggs.* FROM "orders" LEFT JOIN (SELECT order_id, COUNT(*) AS items_count FROM "order_items" WHERE "order_items"."deleted_at" IS NULL GROUP BY "order_items"."order_id") AS aggs ON aggs.order_id = orders.id LIMIT 10这个是正序的情况，是很快，但是 last 是带了 order by orders.id desc 的，好像速度不行~
SELECT orders.*, aggs.* FROM "orders" LEFT JOIN (SELECT order_id, COUNT(*) AS items_count FROM "order_items" WHERE "order_items"."deleted_at" IS NULL GROUP BY "order_items"."order_id") AS aggs ON aggs.order_id = orders.id ORDER BY orders.id DESC LIMIT 10感觉在中间的子查询中还要再嵌套一层过滤 order_id 的，根据外层的条件来
不带 ORDER BY
Limit (cost=0.71..6.73 rows=10 width=359) (actual time=0.072..0.127 rows=10 loops=1) -> Merge Left Join (cost=0.71..40027.18 rows=66537 width=359) (actual time=0.071..0.125 rows=10 loops=1) Merge Cond: (orders.id = order_items.order_id) -> Index Scan using orders_pkey on orders (cost=0.29..9346.63 rows=66537 width=347) (actual time=0.028..0.054 rows=10 loops=1) -> GroupAggregate (cost=0.42..29731.93 rows=34768 width=12) (actual time=0.039..0.057 rows=5 loops=1) Group Key: order_items.order_id -> Index Only Scan using index_order_items_on_order_id on order_items (cost=0.42..28311.68 rows=214514 width=4) (actual time=0.029..0.044 rows=21 loops=1) Heap Fetches: 21 Planning time: 0.640 ms Execution time: 0.244 ms带了 ORDER BY
Limit (cost=12230.82..12232.33 rows=10 width=359) (actual time=254.210..254.258 rows=10 loops=1) -> Merge Left Join (cost=12230.82..22265.03 rows=66537 width=359) (actual time=254.208..254.253 rows=10 loops=1) Merge Cond: (orders.id = order_items.order_id) -> Index Scan Backward using orders_pkey on orders (cost=0.29..9346.63 rows=66537 width=347) (actual time=0.025..0.059 rows=10 loops=1) -> Sort (cost=12230.53..12317.45 rows=34768 width=12) (actual time=254.177..254.179 rows=5 loops=1) Sort Key: order_items.order_id DESC Sort Method: quicksort Memory: 3857kB -> HashAggregate (cost=8912.71..9260.39 rows=34768 width=12) (actual time=202.281..223.324 rows=53143 loops=1) Group Key: order_items.order_id -> Seq Scan on order_items (cost=0.00..7840.14 rows=214514 width=4) (actual time=0.014..87.574 rows=214514 loops=1) Planning time: 0.660 ms Execution time: 255.422 ms如果在中间那个 group by 的子查询中再嵌套一个子查询带外层的条件，又感觉比较丑
-
[广州] 搜床科技招聘 6 名中高级 Ruby 工程师 10-30K at 2017年12月25日
严重支持 -
[深圳] 脚印数据招聘 ROR 工程师 4 名 at 2017年10月28日
看到下午茶进来的
-
[广州][深圳] 晒尔科技招聘 ROR 工程师 2 名 at 2016年08月22日
网约车已经合法，OBD 还是挺热的，看好
-
函数参数疑问 at 2016年08月20日
我的两个理解点 1，ruby 是浅拷备的 2，代码解释由右至左
a.push 的方法，基于浅拷备，不另辟内存空间，所以改变的是原值
add function 里面 a 是一个新变量会有一个新的 object_id，它只是指向 function 外面的 a 的地址，在 a = a 的过程只，只是声明了一个新的 object，并没有开辟内存空间去存值 a = a + b 先计算 a+b，然后指向新的内存地址
估计是为了省内存吧 乱猜的