Postgres 12

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 table1 ("a" INTEGER, "b" INTEGER, "c" INTEGER, "d" INTEGER, "e" INTEGER, "f" INTEGER);

INSERT INTO table1 ("a", "b", "c", "d", "e", "f") VALUES
  ('10', '23', '29', '33', '37', '40'),
  ('9', '13', '21', '25', '32', '42'),
  ('11', '16', '19', '21', '27', '31'),
  ('14', '27', '30', '31', '40', '42'),
  ('16', '24', '29', '40', '41', '42'),
  ('14', '15', '26', '27', '40', '42'),
  ('2', '9', '16', '25', '26', '40'),
  ('8', '19', '25', '34', '37', '39'),
  ('2', '4', '16', '17', '36', '39'),
  ('9', '25', '30', '33', '41', '44'),
  ('1', '7', '36', '37', '41', '42'),
  ('2', '11', '21', '25', '39', '45'),
  ('22', '23', '25', '37', '38', '42'),
  ('2', '6', '12', '31', '33', '40'),
  ('3', '4', '16', '30', '31', '37');

CREATE TABLE table2 ("numbs" INTEGER, "result" INTEGER);

INSERT INTO table2 ("numbs") VALUES
  ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9'), ('10'),
  ('11'), ('12'), ('13'), ('14'), ('15'), ('16'), ('17'), ('18'), ('19'), ('20'),
  ('21'), ('22'), ('23'), ('24'), ('25'), ('26'), ('27'), ('28'), ('29'), ('30'),
  ('31'), ('32'), ('33'), ('34'), ('35'), ('36'), ('37'), ('38'), ('39'), ('40'),
  ('41'), ('42'), ('43'), ('44'), ('45');
15 rows affected

45 rows affected

UPDATE table2 AS t2
SET result = (
  SELECT COUNT(*) 
  FROM table1 AS t1 
  WHERE t2.numbs IN (t1.a, t1.b, t1.c, t1.d, t1.e, t1.f)
);  
45 rows affected

SELECT * FROM table2;


numbs
result


1
1

2
4

3
1

4
2

5
0

6
1

7
1

8
1

9
3

10
1

11
2

12
1

13
1

14
2

15
1

16
5

17
1

18
0

19
2

20
0

21
3

22
1

23
2

24
1

25
6

26
2

27
3

28
0

29
2

30
3

31
4

32
1

33
3

34
1

35
0

36
2

37
5

38
1

39
3

40
6

41
3

42
6

43
0

44
1

45
1

numbs	result
1	1
2	4
3	1
4	2
5	0
6	1
7	1
8	1
9	3
10	1
11	2
12	1
13	1
14	2
15	1
16	5
17	1
18	0
19	2
20	0
21	3
22	1
23	2
24	1
25	6
26	2
27	3
28	0
29	2
30	3
31	4
32	1
33	3
34	1
35	0
36	2
37	5
38	1
39	3
40	6
41	3
42	6
43	0
44	1
45	1

numbs	result
1	1
2	4
3	1
4	2
5	0
6	1
7	1
8	1
9	3
10	1
11	2
12	1
13	1
14	2
15	1
16	5
17	1
18	0
19	2
20	0
21	3
22	1
23	2
24	1
25	6
26	2
27	3
28	0
29	2
30	3
31	4
32	1
33	3
34	1
35	0
36	2
37	5
38	1
39	3
40	6
41	3
42	6
43	0
44	1
45	1

numbs	result
1	1
2	4
3	1
4	2
5	0
6	1
7	1
8	1
9	3
10	1
11	2
12	1
13	1
14	2
15	1
16	5
17	1
18	0
19	2
20	0
21	3
22	1
23	2
24	1
25	6
26	2
27	3
28	0
29	2
30	3
31	4
32	1
33	3
34	1
35	0
36	2
37	5
38	1
39	3
40	6
41	3
42	6
43	0
44	1
45	1