# 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
Sign in with Wallet
Connect another wallet