欢迎简历

嗯，只是模拟一下，有的时候不知道预期的返回结果，比如有些节点多，有些节点少的情况

整个表我没有去算，而且可能没有 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)

                  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 的条件都很快

"727562792d6368696e612e6f7267".scan(/../)

表大一点，计划会复杂，即使拿里层的 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)

不建议把 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"}

嗯，我忘了，我没有 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;

和索引无关，本地索引是对的

order_items 的数据量为 20W+ orders 为 5W+

使用 LEFT JOIN LATERAL 在子查询里面多加 where 条件后好。感觉还是里面的查询不知道要使用 order_id 的索引来过滤数据，需要强制指定。如果没有这种用法，是不是就只能用子查询了？第一次见到这种写法 又学了一招

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 的子查询中再嵌套一个子查询带外层的条件，又感觉比较丑

严重支持

看到下午茶进来的

网约车已经合法，OBD 还是挺热的，看好

我的两个理解点 1，ruby 是浅拷备的 2，代码解释由右至左

a.push 的方法，基于浅拷备，不另辟内存空间，所以改变的是原值

add function 里面 a 是一个新变量会有一个新的 object_id，它只是指向 function 外面的 a 的地址，在 a = a 的过程只，只是声明了一个新的 object，并没有开辟内存空间去存值 a = a + b 先计算 a+b，然后指向新的内存地址

估计是为了省内存吧 乱猜的