---
# System prepended metadata

title: Postgresql 經驗談

---

# Postgresql 經驗談

資料維度 每天1-10萬筆資料
```sql
CREATE TABLE public.goods_plan_user_mapping_1029 (
	id bigserial NOT NULL,
	daily date NOT NULL,
	user_id int8 NOT NULL,
	goods_plan_daily_id int8 NOT NULL,
	created_at int8 NULL,
	account text NULL,
	"operator" text NULL,
	superior_account text NULL,
	CONSTRAINT goods_plan_user_mapping_1029_pkey PRIMARY KEY (daily, goods_plan_daily_id, user_id)
)
PARTITION BY LIST (daily);
CREATE INDEX goods_plan_user_mapping_1029_daily_idx ON ONLY public.goods_plan_user_mapping_1029 USING btree (daily);
CREATE INDEX goods_plan_user_mapping_1029_idx ON ONLY public.goods_plan_user_mapping_1029 USING btree (goods_plan_daily_id);
```


資料維度每天 5筆資料
```sql
CREATE TABLE public.goods_plan_daily (
	id bigserial NOT NULL,
	site_id int8 NOT NULL,
	daily date NOT NULL,
	"name" text NULL,
	members int8 NOT NULL DEFAULT 0,
	normal_max_balance_ratio numeric(19, 2) NULL DEFAULT 0,
	normal_min_balance_ratio numeric(19, 2) NULL DEFAULT 0,
	exceed_probability numeric(19, 2) NULL DEFAULT 0,
	exceed_max_balance_ratio numeric(19, 2) NULL DEFAULT 0,
	exceed_min_balance_ratio numeric(19, 2) NULL DEFAULT 0,
	plan jsonb NULL,
	task_limit jsonb NULL,
	"operator" text NULL,
	created_at int8 NULL,
	task_reward_ratio numeric(19, 4) NULL DEFAULT 0,
	updated_at int8 NULL,
	contract_id int8 NULL,
	CONSTRAINT goods_plan_daily_pk PRIMARY KEY (id, daily)
)
PARTITION BY LIST (daily);
CREATE INDEX goods_plan_daily_idx ON ONLY public.goods_plan_daily USING btree (site_id, daily);
```

```sql
SELECT user_id FROM goods_plan_user_mapping_1029 WHERE daily = (SELECT daily FROM goods_plan_daily WHERE id = 2572 AND site_id = 1029)
```

事實上這是cross join，表會做cross join後再filter。
1. 空資料表也會影響效能 (175天份=> 7天) (160s => 1s)
2. 思考表需不需要做partition


``` sql
Aggregate  (cost=30549.26..30549.27 rows=1 width=8) (actual time=74.388..74.404 rows=1 loops=1)
  ->  Hash Right Join  (cost=30500.50..30519.59 rows=11866 width=0) (actual time=74.339..74.398 rows=28 loops=1)
        Hash Cond: (td.user_id = ub.user_id)
        ->  Seq Scan on time_deposit_deposit_cache_log_1029 td  (cost=0.00..15.60 rows=560 width=8) (actual time=0.002..0.002 rows=0 loops=1)
        ->  Hash  (cost=30352.18..30352.18 rows=11866 width=8) (actual time=74.327..74.342 rows=28 loops=1)
              Buckets: 16384  Batches: 1  Memory Usage: 130kB
              ->  Hash Right Join  (cost=30334.71..30352.18 rows=11866 width=8) (actual time=74.276..74.335 rows=28 loops=1)
                    Hash Cond: (pa.user_id = ub.user_id)
                    ->  Seq Scan on preferential_interest_deposit_cache_log_1029 pa  (cost=0.00..14.60 rows=460 width=8) (actual time=0.007..0.007 rows=0 loops=1)
                    ->  Hash  (cost=30186.39..30186.39 rows=11866 width=8) (actual time=74.253..74.267 rows=28 loops=1)
                          Buckets: 16384  Batches: 1  Memory Usage: 130kB
                          ->  Hash Join  (cost=29142.05..30186.39 rows=11866 width=8) (actual time=67.734..74.256 rows=28 loops=1)
                                Hash Cond: (ub.user_id = u.id)
                                ->  Seq Scan on users_balance ub  (cost=0.00..918.88 rows=47788 width=8) (actual time=0.007..4.516 rows=47790 loops=1)
                                ->  Hash  (cost=28990.86..28990.86 rows=12095 width=8) (actual time=66.322..66.335 rows=28 loops=1)
                                      Buckets: 16384  Batches: 1  Memory Usage: 130kB
                                      ->  Seq Scan on users u  (cost=13193.51..28990.86 rows=12095 width=8) (actual time=22.980..66.322 rows=28 loops=1)
                                            Filter: (((agent_type)::text = ANY ('{agt1}'::text[])) AND (NOT (hashed SubPlan 1)) AND (NOT (hashed SubPlan 3)) AND (site_id = 1029))
                                            Rows Removed by Filter: 47762
                                            SubPlan 1
                                              ->  Seq Scan on goods_contract_user_1029  (cost=0.00..6.58 rows=70 width=8) (actual time=0.005..0.030 rows=98 loops=1)
                                                    Filter: ((status = 0) OR ((status = 1) AND (expired_at > '1665642134175'::bigint)))
                                            SubPlan 3
                                              ->  Append  (cost=48.57..12200.41 rows=394541 width=8) (actual time=0.007..9.543 rows=47380 loops=1)
                                                    InitPlan 2 (returns $1)
                                                      ->  Append  (cost=0.00..48.57 rows=21 width=4) (actual time=0.069..0.098 rows=1 loops=1)
                                                            ->  Seq Scan on goods_plan_daily_2022_10_01  (cost=0.00..1.03 rows=1 width=4) (actual time=0.005..0.006 rows=0 loops=1)
                                                                  Filter: ((id = 2572) AND (site_id = 1029))
                                                                  Rows Removed by Filter: 2
                                                            ->  Seq Scan on goods_plan_daily_2022_10_02  (cost=0.00..2.17 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=1)
                                                                  Filter: ((id = 2572) AND (site_id = 1029))
                                                                  Rows Removed by Filter: 11
                                                            ->  Index Scan using goods_plan_daily_2022_10_03_pkey on goods_plan_daily_2022_10_03  (cost=0.14..2.37 rows=1 width=4) (actual time=0.007..0.008 rows=0 loops=1)
                                                                  Index Cond: (id = 2572)
                                                                  Filter: (site_id = 1029)
                                                            ->  Index Scan using goods_plan_daily_2022_10_04_pkey on goods_plan_daily_2022_10_04  (cost=0.27..2.49 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=1)
                                                                  Index Cond: (id = 2572)
                                                                  Filter: (site_id = 1029)
                                                            ->  Index Scan using goods_plan_daily_2022_10_05_pkey on goods_plan_daily_2022_10_05  (cost=0.28..2.50 rows=1 width=4) (actual time=0.006..0.007 rows=0 loops=1)
                                                                  Index Cond: (id = 2572)
                                                                  Filter: (site_id = 1029)
                                                            ->  Index Scan using goods_plan_daily_2022_10_06_pkey on goods_plan_daily_2022_10_06  (cost=0.28..2.50 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=1)
                                                                  Index Cond: (id = 2572)
                                                                  Filter: (site_id = 1029)
                                                            ->  Index Scan using goods_plan_daily_2022_10_07_pkey on goods_plan_daily_2022_10_07  (cost=0.14..2.36 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1)
                                                                  Index Cond: (id = 2572)
                                                                  Filter: (site_id = 1029)
                                                            ->  Index Scan using goods_plan_daily_2022_10_08_pkey on goods_plan_daily_2022_10_08  (cost=0.14..2.36 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1)
                                                                  Index Cond: (id = 2572)
                                                                  Filter: (site_id = 1029)
                                                            ->  Bitmap Heap Scan on goods_plan_daily_2022_10_09  (cost=1.24..2.36 rows=1 width=4) (actual time=0.005..0.005 rows=0 loops=1)
                                                                  Recheck Cond: (id = 2572)
                                                                  Filter: (site_id = 1029)
                                                                  ->  Bitmap Index Scan on goods_plan_daily_2022_10_09_pkey  (cost=0.00..1.24 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=1)
                                                                        Index Cond: (id = 2572)
                                                            ->  Index Scan using goods_plan_daily_2022_10_10_pkey on goods_plan_daily_2022_10_10  (cost=0.14..2.36 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1)
                                                                  Index Cond: (id = 2572)
                                                                  Filter: (site_id = 1029)
                                                            ->  Index Scan using goods_plan_daily_2022_10_11_pkey on goods_plan_daily_2022_10_11  (cost=0.14..2.36 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1)
                                                                  Index Cond: (id = 2572)
                                                                  Filter: (site_id = 1029)
                                                            ->  Index Scan using goods_plan_daily_2022_10_12_pkey on goods_plan_daily_2022_10_12  (cost=0.13..2.35 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1)
                                                                  Index Cond: (id = 2572)
                                                                  Filter: (site_id = 1029)
                                                            ->  Index Scan using goods_plan_daily_2022_10_13_site_id_daily_idx on goods_plan_daily_2022_10_13  (cost=0.14..2.37 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=1)
                                                                  Index Cond: (site_id = 1029)
                                                                  Filter: (id = 2572)
                                                                  Rows Removed by Filter: 2
                                                            ->  Index Scan using goods_plan_daily_2022_10_14_site_id_daily_idx on goods_plan_daily_2022_10_14  (cost=0.14..2.37 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1)
                                                                  Index Cond: (site_id = 1029)
                                                                  Filter: (id = 2572)
                                                                  Rows Removed by Filter: 2
                                                            ->  Index Scan using goods_plan_daily_2022_10_15_site_id_daily_idx on goods_plan_daily_2022_10_15  (cost=0.14..2.37 rows=1 width=4) (actual time=0.003..0.004 rows=0 loops=1)
                                                                  Index Cond: (site_id = 1029)
                                                                  Filter: (id = 2572)
                                                            ->  Index Scan using goods_plan_daily_2022_10_16_site_id_daily_idx on goods_plan_daily_2022_10_16  (cost=0.14..2.37 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=1)
                                                                  Index Cond: (site_id = 1029)
                                                                  Filter: (id = 2572)
                                                            ->  Index Scan using goods_plan_daily_2022_10_17_site_id_daily_idx on goods_plan_daily_2022_10_17  (cost=0.14..2.37 rows=1 width=4) (actual time=0.002..0.003 rows=0 loops=1)
                                                                  Index Cond: (site_id = 1029)
                                                                  Filter: (id = 2572)
                                                            ->  Index Scan using goods_plan_daily_2022_10_18_site_id_daily_idx on goods_plan_daily_2022_10_18  (cost=0.14..2.37 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1)
                                                                  Index Cond: (site_id = 1029)
                                                                  Filter: (id = 2572)
                                                            ->  Index Scan using goods_plan_daily_2022_10_19_site_id_daily_idx on goods_plan_daily_2022_10_19  (cost=0.14..2.37 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1)
                                                                  Index Cond: (site_id = 1029)
                                                                  Filter: (id = 2572)
                                                            ->  Index Scan using goods_plan_daily_2022_10_20_site_id_daily_idx on goods_plan_daily_2022_10_20  (cost=0.14..2.37 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1)
                                                                  Index Cond: (site_id = 1029)
                                                                  Filter: (id = 2572)
                                                            ->  Index Scan using goods_plan_daily_default_pkey on goods_plan_daily_default  (cost=0.14..2.37 rows=1 width=4) (actual time=0.003..0.003 rows=0 loops=1)
                                                                  Index Cond: (id = 2572)
                                                                  Filter: (site_id = 1029)
                                                    ->  Seq Scan on goods_plan_user_mapping_1029_2022_10_01  (cost=0.00..317.44 rows=12595 width=8) (never executed)
                                                          Filter: (daily = $1)
                                                    ->  Seq Scan on goods_plan_user_mapping_1029_2022_10_02  (cost=0.00..332.91 rows=13193 width=8) (never executed)
                                                          Filter: (daily = $1)
                                                    ->  Seq Scan on goods_plan_user_mapping_1029_2022_10_03  (cost=0.00..350.73 rows=13898 width=8) (never executed)
                                                          Filter: (daily = $1)
                                                    ->  Seq Scan on goods_plan_user_mapping_1029_2022_10_04  (cost=0.00..840.56 rows=33645 width=8) (never executed)
                                                          Filter: (daily = $1)
                                                    ->  Seq Scan on goods_plan_user_mapping_1029_2022_10_05  (cost=0.00..16.86 rows=629 width=8) (never executed)
                                                          Filter: (daily = $1)
                                                    ->  Seq Scan on goods_plan_user_mapping_1029_2022_10_06  (cost=0.00..716.91 rows=28473 width=8) (never executed)
                                                          Filter: (daily = $1)
                                                    ->  Seq Scan on goods_plan_user_mapping_1029_2022_10_07  (cost=0.00..465.83 rows=18466 width=8) (never executed)
                                                          Filter: (daily = $1)
                                                    ->  Seq Scan on goods_plan_user_mapping_1029_2022_10_08  (cost=0.00..485.35 rows=19228 width=8) (never executed)
                                                          Filter: (daily = $1)
                                                    ->  Seq Scan on goods_plan_user_mapping_1029_2022_10_09  (cost=0.00..1199.76 rows=47581 width=8) (never executed)
                                                          Filter: (daily = $1)
                                                    ->  Seq Scan on goods_plan_user_mapping_1029_2022_10_10  (cost=0.00..1155.44 rows=44035 width=8) (never executed)
                                                          Filter: (daily = $1)
                                                    ->  Seq Scan on goods_plan_user_mapping_1029_2022_10_11  (cost=0.00..868.11 rows=29289 width=8) (never executed)
                                                          Filter: (daily = $1)
                                                    ->  Seq Scan on goods_plan_user_mapping_1029_2022_10_12  (cost=0.00..783.69 rows=31095 width=8) (never executed)
                                                          Filter: (daily = $1)
                                                    ->  Seq Scan on goods_plan_user_mapping_1029_2022_10_13  (cost=0.00..1198.16 rows=47533 width=8) (never executed)
                                                          Filter: (daily = $1)
                                                    ->  Seq Scan on goods_plan_user_mapping_1029_2022_10_14  (cost=0.00..1172.26 rows=47381 width=8) (actual time=0.005..6.724 rows=47380 loops=1)
                                                          Filter: (daily = $1)
                                                    ->  Bitmap Heap Scan on goods_plan_user_mapping_1029_2022_10_15  (cost=1.27..4.44 rows=3 width=8) (never executed)
                                                          Recheck Cond: (daily = $1)
                                                          ->  Bitmap Index Scan on goods_plan_user_mapping_1029_2022_10_15_daily_idx  (cost=0.00..1.27 rows=3 width=0) (never executed)
                                                                Index Cond: (daily = $1)
                                                    ->  Bitmap Heap Scan on goods_plan_user_mapping_1029_2022_10_16  (cost=1.27..4.44 rows=3 width=8) (never executed)
                                                          Recheck Cond: (daily = $1)
                                                          ->  Bitmap Index Scan on goods_plan_user_mapping_1029_2022_10_16_daily_idx  (cost=0.00..1.27 rows=3 width=0) (never executed)
                                                                Index Cond: (daily = $1)
                                                    ->  Bitmap Heap Scan on goods_plan_user_mapping_1029_2022_10_17  (cost=1.27..4.44 rows=3 width=8) (never executed)
                                                          Recheck Cond: (daily = $1)
                                                          ->  Bitmap Index Scan on goods_plan_user_mapping_1029_2022_10_17_daily_idx  (cost=0.00..1.27 rows=3 width=0) (never executed)
                                                                Index Cond: (daily = $1)
                                                    ->  Bitmap Heap Scan on goods_plan_user_mapping_1029_2022_10_18  (cost=1.27..4.44 rows=3 width=8) (never executed)
                                                          Recheck Cond: (daily = $1)
                                                          ->  Bitmap Index Scan on goods_plan_user_mapping_1029_2022_10_18_daily_idx  (cost=0.00..1.27 rows=3 width=0) (never executed)
                                                                Index Cond: (daily = $1)
                                                    ->  Bitmap Heap Scan on goods_plan_user_mapping_1029_2022_10_19  (cost=1.27..4.44 rows=3 width=8) (never executed)
                                                          Recheck Cond: (daily = $1)
                                                          ->  Bitmap Index Scan on goods_plan_user_mapping_1029_2022_10_19_daily_idx  (cost=0.00..1.27 rows=3 width=0) (never executed)
                                                                Index Cond: (daily = $1)
                                                    ->  Bitmap Heap Scan on goods_plan_user_mapping_1029_2022_10_20  (cost=1.27..4.44 rows=3 width=8) (never executed)
                                                          Recheck Cond: (daily = $1)
                                                          ->  Bitmap Index Scan on goods_plan_user_mapping_1029_2022_10_20_daily_idx  (cost=0.00..1.27 rows=3 width=0) (never executed)
                                                                Index Cond: (daily = $1)
                                                    ->  Index Scan using goods_plan_user_mapping_1029_default_daily_idx on goods_plan_user_mapping_1029_default  (cost=0.42..248.46 rows=7482 width=8) (never executed)
                                                          Index Cond: (daily = $1)
Planning Time: 1.519 ms
Execution Time: 74.583 ms
```



# Postgresql Debug

## DB Lock
* 沒開獎
* 後台很卡
* Query要很久

1. 用Query平均時間、最久時間去分析有沒有Deadlock
```sql
select * from pg_stat_statements order by mean_time desc; --看平均值行時間
select * from pg_stat_statements order by max_time desc; -- 看最久時間
```
2. 看當下Lock的情況(查看多次，看有無Lock)，或是等待什麼Lock
```sql
 SELECT
  COALESCE(blockingl.relation::regclass::text,blockingl.locktype) as locked_item,
  blockeda.pid AS blocked_pid, blockeda.query as blocked_query,
  blockedl.mode as blocked_mode, blockinga.pid AS blocking_pid,
  blockinga.query as blocking_query, blockingl.mode as blocking_mode
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(
  ( (blockingl.transactionid=blockedl.transactionid) OR
    (blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype)
  ) AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
WHERE NOT blockedl.granted;
```

3. 看執行時間大於5分鐘的Connection，或是等待什麼Lock
```sql
SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state FROM pg_stat_activity WHERE now() - pg_stat_activity.query_start > interval '5 minutes';
```
4. 用以上的userid，去pg_user找是什麼使用者。
```sql
    select * from pg_user;
```
5. 未來可以效能校調的部分
```sql
select * from pg_stat_statements pss where temp_blks_read > 0 order by mean_time desc;
```

## 補開獎、有效投注跟不上
1. 計劃未結算(當天)，記得處理pyrite_bet_log
2. 計劃未結算非當天，要處理所有資料bet_log、lottery_bet_log、pyrite_bet_log、
    statistic(總投注)、statistic_total(總投注)
    
## 資金明細錯誤或是入款方式錯
1. finance_log、deposit_withdraw_report、statistic_date_report、statistic