Postgres 9.6

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 test(
    id integer,
    json_key json[]
);

INSERT INTO test (id, json_key) VALUES
    (1,array['{"key1":"123"}'::json,'{"key2":"456"}'::json]),
    (2,array['{"key1":"789"}'::json,'{"key2":"456"}'::json]);
2 rows affected

SELECT * FROM test WHERE '{"key2": "456"}'::jsonb <@ ANY(json_key::jsonb[]);


id
json_key


1
{"{"key1":"123"}","{"key2":"456"}"}

2
{"{"key1":"789"}","{"key2":"456"}"}


SELECT * FROM test WHERE '{"key1": "456"}'::jsonb <@ ANY(json_key::jsonb[]);
SELECT * FROM test WHERE '{"key1": "123"}'::jsonb <@ ANY(json_key::jsonb[]);


id
json_key


1
{"{"key1":"123"}","{"key2":"456"}"}


--------------------------------------------------------------------------------
-- ② Graphs
--------------------------------------------------------------------------------
-- "§Chart§" is a magic value to recognize the output as graph data
-- this is a live dbfiddle stats graph but any data can be used
-- dbfiddle stats are only accessible inside the Postgres 9.6 backend
--------------------------------------------------------------------------------
select to_char(date_trunc('day',history_hour_at),'YYYY-MM-DD') "§Chart§"
     , coalesce(sum(distinct_ip) filter (where rdbms_code~'^sqlserver'),0) "SQL Server"
     , coalesce(sum(distinct_ip) filter (where rdbms_code~'^postgres'),0) "Postgres"
     , coalesce(sum(distinct_ip) filter (where rdbms_code~'^oracle'),0) "Oracle"
     , coalesce(sum(distinct_ip) filter (where rdbms_code~'^maria'),0) "MariaDB"
     , coalesce(sum(distinct_ip) filter (where rdbms_code~'^sqlite'),0) "SQLite"
from stats.rdbms
group by date_trunc('day',history_hour_at)
order by 1;


§Chart§
SQL Server
Postgres
Oracle
MariaDB
SQLite


2017-02-27
5
0
0
0
0

2017-02-28
28
4
7
0
0

2017-03-01
12
8
17
0
0

2017-03-02
50
6
10
0
0

2017-03-03
33
0
6
0
0

2017-03-04
8
1
3
0
0

2017-03-05
11
9
4
0
0

2017-03-06
15
14
4
0
0

2017-03-07
24
53
11
0
0

2017-03-08
30
23
13
0
0

2017-03-09
36
33
4
0
0

2017-03-10
33
24
12
0
0

2017-03-11
41
20
1
0
0

2017-03-12
22
17
7
0
0

2017-03-13
15
23
6
0
0

2017-03-14
24
14
9
0
0

2017-03-15
16
10
5
1
0

2017-03-16
37
15
10
0
0

2017-03-17
12
16
4
1
0

2017-03-18
19
13
8
0
0

2017-03-19
55
12
6
3
0

2017-03-20
25
10
20
3
0

2017-03-21
33
7
7
0
0

2017-03-22
26
19
19
1
0

2017-03-23
24
14
13
1
1

2017-03-24
47
23
7
2
0

2017-03-25
59
7
6
0
0

2017-03-26
32
7
5
2
0

2017-03-27
50
10
13
1
0

2017-03-28
35
13
10
2
1

2017-03-29
67
13
7
2
2

2017-03-30
65
27
10
0
0

2017-03-31
45
19
6
1
1

2017-04-01
65
9
3
4
0

2017-04-02
33
9
3
1
0

2017-04-03
71
51
46
0
2

2017-04-04
44
21
26
1
1

2017-04-05
42
9
13
4
0

2017-04-06
34
17
8
2
0

2017-04-07
23
10
16
5
0

2017-04-08
11
6
4
2
0

2017-04-09
20
12
4
3
0

2017-04-10
34
11
6
4
0

2017-04-11
47
23
23
0
0

2017-04-12
43
29
14
5
0

2017-04-13
22
10
7
8
0

2017-04-14
20
7
13
3
0

2017-04-15
44
11
5
5
0

2017-04-16
35
8
5
0
0

2017-04-17
20
17
9
1
0

2017-04-18
38
16
6
2
0

2017-04-19
39
14
9
0
1

2017-04-20
67
20
12
0
3

2017-04-21
37
16
10
0
1

2017-04-22
18
25
4
2
0

2017-04-23
6
7
1
2
0

2017-04-24
22
21
4
3
0

2017-04-25
28
25
2
7
0

2017-04-26
44
19
13
23
1

2017-04-27
7
1
1
4
0

2017-05-01
24
9
5
1
0

2017-05-02
25
20
6
5
0

2017-05-03
25
23
11
2
0

2017-05-04
30
17
9
1
0

2017-05-05
11
21
5
2
0

2017-05-06
18
12
7
1
0

2017-05-07
8
11
4
1
0

2017-05-08
27
24
19
4
0

2017-05-09
23
19
15
3
0

2017-05-10
31
29
12
0
0

2017-05-11
29
23
18
15
1

2017-05-12
28
17
7
3
0

2017-05-13
24
15
11
5
0

2017-05-14
5
5
3
0
0

2017-05-15
28
54
10
1
0

2017-05-16
22
24
11
2
0

2017-05-17
54
12
14
1
1

2017-05-18
54
39
17
7
1

2017-05-19
13
43
10
4
0

2017-05-20
27
17
1
6
0

2017-05-21
18
16
2
6
0

2017-05-22
30
15
9
3
0

2017-05-23
54
27
8
8
0

2017-05-24
43
27
6
5
0

2017-05-25
40
24
7
3
0

2017-05-26
41
22
11
0
0

2017-05-27
39
36
9
7
1

2017-05-28
30
32
8
9
3

2017-05-29
26
32
11
4
0

2017-05-30
27
27
15
7
0

2017-05-31
29
30
14
7
0

2017-06-01
33
16
11
3
0

2017-06-02
33
20
11
5
1

2017-06-03
26
10
3
3
0

2017-06-04
24
15
3
3
0

2017-06-05
31
21
5
2
0

2017-06-06
36
12
5
2
0

2017-06-07
35
31
6
3
0

2017-06-08
25
19
4
13
1

2017-06-09
26
16
9
9
1

2017-06-10
15
17
3
5
0

2017-06-11
22
13
2
3
0

2017-06-12
18
24
7
0
0

2017-06-13
19
15
19
8
0

2017-06-14
23
17
7
6
3

2017-06-15
31
15
5
4
1

2017-06-16
32
22
7
5
0

2017-06-17
19
12
4
11
0

2017-06-18
11
4
4
0
0

2017-06-19
28
12
10
26
3

2017-06-20
42
19
10
4
1

2017-06-21
41
9
15
5
0

2017-06-22
53
19
12
6
0

2017-06-23
30
29
7
13
0

2017-06-24
13
33
1
22
1

2017-06-25
12
24
5
10
0

2017-06-26
15
30
7
9
1

2017-06-27
16
29
10
7
0

2017-06-28
32
19
7
7
0

2017-06-29
24
28
14
11
0

2017-06-30
33
32
6
11
0

2017-07-01
24
15
1
14
1

2017-07-02
39
20
8
15
0

2017-07-03
28
33
15
10
0

2017-07-04
35
40
19
11
1

2017-07-05
28
29
25
5
2

2017-07-06
39
20
10
5
2

2017-07-07
28
24
7
9
0

2017-07-08
25
21
6
16
1

2017-07-09
28
23
7
14
3

2017-07-10
18
28
10
13
0

2017-07-11
25
28
12
1
0

2017-07-12
39
26
10
13
3

2017-07-13
38
29
5
9
0

2017-07-14
25
32
6
33
2

2017-07-15
25
38
16
14
0

2017-07-16
26
32
7
10
0

2017-07-17
13
12
0
10
0

id	json_key
1	{"{"key1":"123"}","{"key2":"456"}"}
2	{"{"key1":"789"}","{"key2":"456"}"}

§Chart§	SQL Server	Postgres	Oracle	MariaDB	SQLite
2017-02-27	5	0	0	0	0
2017-02-28	28	4	7	0	0
2017-03-01	12	8	17	0	0
2017-03-02	50	6	10	0	0
2017-03-03	33	0	6	0	0
2017-03-04	8	1	3	0	0
2017-03-05	11	9	4	0	0
2017-03-06	15	14	4	0	0
2017-03-07	24	53	11	0	0
2017-03-08	30	23	13	0	0
2017-03-09	36	33	4	0	0
2017-03-10	33	24	12	0	0
2017-03-11	41	20	1	0	0
2017-03-12	22	17	7	0	0
2017-03-13	15	23	6	0	0
2017-03-14	24	14	9	0	0
2017-03-15	16	10	5	1	0
2017-03-16	37	15	10	0	0
2017-03-17	12	16	4	1	0
2017-03-18	19	13	8	0	0
2017-03-19	55	12	6	3	0
2017-03-20	25	10	20	3	0
2017-03-21	33	7	7	0	0
2017-03-22	26	19	19	1	0
2017-03-23	24	14	13	1	1
2017-03-24	47	23	7	2	0
2017-03-25	59	7	6	0	0
2017-03-26	32	7	5	2	0
2017-03-27	50	10	13	1	0
2017-03-28	35	13	10	2	1
2017-03-29	67	13	7	2	2
2017-03-30	65	27	10	0	0
2017-03-31	45	19	6	1	1
2017-04-01	65	9	3	4	0
2017-04-02	33	9	3	1	0
2017-04-03	71	51	46	0	2
2017-04-04	44	21	26	1	1
2017-04-05	42	9	13	4	0
2017-04-06	34	17	8	2	0
2017-04-07	23	10	16	5	0
2017-04-08	11	6	4	2	0
2017-04-09	20	12	4	3	0
2017-04-10	34	11	6	4	0
2017-04-11	47	23	23	0	0
2017-04-12	43	29	14	5	0
2017-04-13	22	10	7	8	0
2017-04-14	20	7	13	3	0
2017-04-15	44	11	5	5	0
2017-04-16	35	8	5	0	0
2017-04-17	20	17	9	1	0
2017-04-18	38	16	6	2	0
2017-04-19	39	14	9	0	1
2017-04-20	67	20	12	0	3
2017-04-21	37	16	10	0	1
2017-04-22	18	25	4	2	0
2017-04-23	6	7	1	2	0
2017-04-24	22	21	4	3	0
2017-04-25	28	25	2	7	0
2017-04-26	44	19	13	23	1
2017-04-27	7	1	1	4	0
2017-05-01	24	9	5	1	0
2017-05-02	25	20	6	5	0
2017-05-03	25	23	11	2	0
2017-05-04	30	17	9	1	0
2017-05-05	11	21	5	2	0
2017-05-06	18	12	7	1	0
2017-05-07	8	11	4	1	0
2017-05-08	27	24	19	4	0
2017-05-09	23	19	15	3	0
2017-05-10	31	29	12	0	0
2017-05-11	29	23	18	15	1
2017-05-12	28	17	7	3	0
2017-05-13	24	15	11	5	0
2017-05-14	5	5	3	0	0
2017-05-15	28	54	10	1	0
2017-05-16	22	24	11	2	0
2017-05-17	54	12	14	1	1
2017-05-18	54	39	17	7	1
2017-05-19	13	43	10	4	0
2017-05-20	27	17	1	6	0
2017-05-21	18	16	2	6	0
2017-05-22	30	15	9	3	0
2017-05-23	54	27	8	8	0
2017-05-24	43	27	6	5	0
2017-05-25	40	24	7	3	0
2017-05-26	41	22	11	0	0
2017-05-27	39	36	9	7	1
2017-05-28	30	32	8	9	3
2017-05-29	26	32	11	4	0
2017-05-30	27	27	15	7	0
2017-05-31	29	30	14	7	0
2017-06-01	33	16	11	3	0
2017-06-02	33	20	11	5	1
2017-06-03	26	10	3	3	0
2017-06-04	24	15	3	3	0
2017-06-05	31	21	5	2	0
2017-06-06	36	12	5	2	0
2017-06-07	35	31	6	3	0
2017-06-08	25	19	4	13	1
2017-06-09	26	16	9	9	1
2017-06-10	15	17	3	5	0
2017-06-11	22	13	2	3	0
2017-06-12	18	24	7	0	0
2017-06-13	19	15	19	8	0
2017-06-14	23	17	7	6	3
2017-06-15	31	15	5	4	1
2017-06-16	32	22	7	5	0
2017-06-17	19	12	4	11	0
2017-06-18	11	4	4	0	0
2017-06-19	28	12	10	26	3
2017-06-20	42	19	10	4	1
2017-06-21	41	9	15	5	0
2017-06-22	53	19	12	6	0
2017-06-23	30	29	7	13	0
2017-06-24	13	33	1	22	1
2017-06-25	12	24	5	10	0
2017-06-26	15	30	7	9	1
2017-06-27	16	29	10	7	0
2017-06-28	32	19	7	7	0
2017-06-29	24	28	14	11	0
2017-06-30	33	32	6	11	0
2017-07-01	24	15	1	14	1
2017-07-02	39	20	8	15	0
2017-07-03	28	33	15	10	0
2017-07-04	35	40	19	11	1
2017-07-05	28	29	25	5	2
2017-07-06	39	20	10	5	2
2017-07-07	28	24	7	9	0
2017-07-08	25	21	6	16	1
2017-07-09	28	23	7	14	3
2017-07-10	18	28	10	13	0
2017-07-11	25	28	12	1	0
2017-07-12	39	26	10	13	3
2017-07-13	38	29	5	9	0
2017-07-14	25	32	6	33	2
2017-07-15	25	38	16	14	0
2017-07-16	26	32	7	10	0
2017-07-17	13	12	0	10	0