Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE user_table (
user_name text , --[使用者名稱]
team_id  text, --[隊伍id]
position text); --[職位('LEADER'/'MEMBER')]

INSERT INTO user_table (user_name,team_id,position)
VALUES 
(1,'A','LEADER'),
(2,'A','MEMBER'),
(3,'D','MEMBER'),
(4,'C','LEADER'),
(5,'X','MEMBER'),
(6,'A','MEMBER'),
(7,'D','LEADER'),
(8,'C','MEMBER'),
(9,'X','MEMBER'),
(10,'D',NULL),
(11,'C','MEMBER'),	
(12,'C','MEMBER'),
(13,'D','');

SELECT *
FROM user_table
ORDER BY team_id,position;
13 rows affected



user_name
team_id
position


1
A
LEADER

6
A
MEMBER

2
A
MEMBER

4
C
LEADER

12
C
MEMBER

8
C
MEMBER

11
C
MEMBER

13
D


7
D
LEADER

3
D
MEMBER

10
D
null

9
X
MEMBER

5
X
MEMBER


-- 隊伍= X ,職位=LEADER
SELECT COUNT(position) AS COUNTER
FROM user_table
WHERE position='LEADER' AND team_id='X';


counter


0


-- 隊伍= D ,職位=LEADER
SELECT COUNT(position) AS COUNTER
FROM user_table
WHERE position='LEADER' AND team_id='D';


counter


1


-- 隊伍=A ,user_name='1' ,position= LEADER
-- 變更為隊伍=A ,user_name='6' ,position= LEADER
BEGIN;
UPDATE user_table
SET position='MEMBER'
WHERE team_id='A' AND NOT (position IS NULL OR position='');

UPDATE user_table
SET position='LEADER'
WHERE team_id='A' AND user_name ='6';
COMMIT;
3 rows affected

1 rows affected

SELECT *
FROM user_table
ORDER BY team_id,position;


user_name
team_id
position


6
A
LEADER

2
A
MEMBER

1
A
MEMBER

4
C
LEADER

12
C
MEMBER

8
C
MEMBER

11
C
MEMBER

13
D


7
D
LEADER

3
D
MEMBER

10
D
null

9
X
MEMBER

5
X
MEMBER

user_name	team_id	position
1	A	LEADER
6	A	MEMBER
2	A	MEMBER
4	C	LEADER
12	C	MEMBER
8	C	MEMBER
11	C	MEMBER
13	D
7	D	LEADER
3	D	MEMBER
10	D	null
9	X	MEMBER
5	X	MEMBER