Postgres 10

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?.

select version();


version


PostgreSQL 10.9 (Debian 10.9-1.pgdg80+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10+deb8u2) 4.9.2, 64-bit


CREATE TABLE tab
(
  t_id    SERIAL NOT NULL PRIMARY KEY,
  t_key   INTEGER NOT NULL,
  t_name  VARCHAR(10) NOT NULL,
  t_value VARCHAR(10) NOT NULL
);
INSERT INTO tab (t_key, t_name, t_value)
VALUES

(75, 'Couleur', 'Bleu'),
(75, 'Taille', 'Grand'),
(75, 'Poids',  '20'),
(75, 'Teint',  'Y'),

(76, 'Couleur', 'Bleu'),
(76, 'Taille', 'Grand'),
(76, 'Poids',  '20'),
(76, 'Teint',  'Y'),

(77, 'Couleur', 'Bleu'),
(77, 'Taille', 'Grand'),
(77, 'Poids',  '20'),
(77, 'Teint',  'N');




;
12 rows affected

SELECT t1.t_id AS "t1id", t1.t_key AS "t1key", t1.t_name AS "t1name", t1.t_value AS "t1value",
       t2.t_id AS "t2id", t2.t_key AS "t2key", t2.t_name AS "t2name", t2.t_value AS "t2value"
FROM tab t1
JOIN tab t2
ON t1.t_key != t2.t_key
ORDER BY t1.t_id, t2.t_id;


t1id
t1key
t1name
t1value
t2id
t2key
t2name
t2value


1
75
Couleur
Bleu
5
76
Couleur
Bleu

1
75
Couleur
Bleu
6
76
Taille
Grand

1
75
Couleur
Bleu
7
76
Poids
20

1
75
Couleur
Bleu
8
76
Teint
Y

1
75
Couleur
Bleu
9
77
Couleur
Bleu

1
75
Couleur
Bleu
10
77
Taille
Grand

1
75
Couleur
Bleu
11
77
Poids
20

1
75
Couleur
Bleu
12
77
Teint
N

2
75
Taille
Grand
5
76
Couleur
Bleu

2
75
Taille
Grand
6
76
Taille
Grand

2
75
Taille
Grand
7
76
Poids
20

2
75
Taille
Grand
8
76
Teint
Y

2
75
Taille
Grand
9
77
Couleur
Bleu

2
75
Taille
Grand
10
77
Taille
Grand

2
75
Taille
Grand
11
77
Poids
20

2
75
Taille
Grand
12
77
Teint
N

3
75
Poids
20
5
76
Couleur
Bleu

3
75
Poids
20
6
76
Taille
Grand

3
75
Poids
20
7
76
Poids
20

3
75
Poids
20
8
76
Teint
Y

3
75
Poids
20
9
77
Couleur
Bleu

3
75
Poids
20
10
77
Taille
Grand

3
75
Poids
20
11
77
Poids
20

3
75
Poids
20
12
77
Teint
N

4
75
Teint
Y
5
76
Couleur
Bleu

4
75
Teint
Y
6
76
Taille
Grand

4
75
Teint
Y
7
76
Poids
20

4
75
Teint
Y
8
76
Teint
Y

4
75
Teint
Y
9
77
Couleur
Bleu

4
75
Teint
Y
10
77
Taille
Grand

4
75
Teint
Y
11
77
Poids
20

4
75
Teint
Y
12
77
Teint
N

5
76
Couleur
Bleu
1
75
Couleur
Bleu

5
76
Couleur
Bleu
2
75
Taille
Grand

5
76
Couleur
Bleu
3
75
Poids
20

5
76
Couleur
Bleu
4
75
Teint
Y

5
76
Couleur
Bleu
9
77
Couleur
Bleu

5
76
Couleur
Bleu
10
77
Taille
Grand

5
76
Couleur
Bleu
11
77
Poids
20

5
76
Couleur
Bleu
12
77
Teint
N

6
76
Taille
Grand
1
75
Couleur
Bleu

6
76
Taille
Grand
2
75
Taille
Grand

6
76
Taille
Grand
3
75
Poids
20

6
76
Taille
Grand
4
75
Teint
Y

6
76
Taille
Grand
9
77
Couleur
Bleu

6
76
Taille
Grand
10
77
Taille
Grand

6
76
Taille
Grand
11
77
Poids
20

6
76
Taille
Grand
12
77
Teint
N

7
76
Poids
20
1
75
Couleur
Bleu

7
76
Poids
20
2
75
Taille
Grand

7
76
Poids
20
3
75
Poids
20

7
76
Poids
20
4
75
Teint
Y

7
76
Poids
20
9
77
Couleur
Bleu

7
76
Poids
20
10
77
Taille
Grand

7
76
Poids
20
11
77
Poids
20

7
76
Poids
20
12
77
Teint
N

8
76
Teint
Y
1
75
Couleur
Bleu

8
76
Teint
Y
2
75
Taille
Grand

8
76
Teint
Y
3
75
Poids
20

8
76
Teint
Y
4
75
Teint
Y

8
76
Teint
Y
9
77
Couleur
Bleu

8
76
Teint
Y
10
77
Taille
Grand

8
76
Teint
Y
11
77
Poids
20

8
76
Teint
Y
12
77
Teint
N

9
77
Couleur
Bleu
1
75
Couleur
Bleu

9
77
Couleur
Bleu
2
75
Taille
Grand

9
77
Couleur
Bleu
3
75
Poids
20

9
77
Couleur
Bleu
4
75
Teint
Y

9
77
Couleur
Bleu
5
76
Couleur
Bleu

9
77
Couleur
Bleu
6
76
Taille
Grand

9
77
Couleur
Bleu
7
76
Poids
20

9
77
Couleur
Bleu
8
76
Teint
Y

10
77
Taille
Grand
1
75
Couleur
Bleu

10
77
Taille
Grand
2
75
Taille
Grand

10
77
Taille
Grand
3
75
Poids
20

10
77
Taille
Grand
4
75
Teint
Y

10
77
Taille
Grand
5
76
Couleur
Bleu

10
77
Taille
Grand
6
76
Taille
Grand

10
77
Taille
Grand
7
76
Poids
20

10
77
Taille
Grand
8
76
Teint
Y

11
77
Poids
20
1
75
Couleur
Bleu

11
77
Poids
20
2
75
Taille
Grand

11
77
Poids
20
3
75
Poids
20

11
77
Poids
20
4
75
Teint
Y

11
77
Poids
20
5
76
Couleur
Bleu

11
77
Poids
20
6
76
Taille
Grand

11
77
Poids
20
7
76
Poids
20

11
77
Poids
20
8
76
Teint
Y

12
77
Teint
N
1
75
Couleur
Bleu

12
77
Teint
N
2
75
Taille
Grand

12
77
Teint
N
3
75
Poids
20

12
77
Teint
N
4
75
Teint
Y

12
77
Teint
N
5
76
Couleur
Bleu

12
77
Teint
N
6
76
Taille
Grand

12
77
Teint
N
7
76
Poids
20

12
77
Teint
N
8
76
Teint
Y


SELECT t1.t_key AS "t1key", t1.t_name AS "t1name", t1.t_value AS "t1value",
       t2.t_key AS "t2key", t2.t_name AS "t2name", t2.t_value AS "t2value"
FROM tab t1
JOIN tab t2
ON t1.t_key != t2.t_key
  AND t1.t_name = t2.t_name
  AND t1.t_value = t2.t_value
ORDER BY t1.t_id, t2.t_id;


t1key
t1name
t1value
t2key
t2name
t2value


75
Couleur
Bleu
76
Couleur
Bleu

75
Couleur
Bleu
77
Couleur
Bleu

75
Taille
Grand
76
Taille
Grand

75
Taille
Grand
77
Taille
Grand

75
Poids
20
76
Poids
20

75
Poids
20
77
Poids
20

75
Teint
Y
76
Teint
Y

76
Couleur
Bleu
75
Couleur
Bleu

76
Couleur
Bleu
77
Couleur
Bleu

76
Taille
Grand
75
Taille
Grand

76
Taille
Grand
77
Taille
Grand

76
Poids
20
75
Poids
20

76
Poids
20
77
Poids
20

76
Teint
Y
75
Teint
Y

77
Couleur
Bleu
75
Couleur
Bleu

77
Couleur
Bleu
76
Couleur
Bleu

77
Taille
Grand
75
Taille
Grand

77
Taille
Grand
76
Taille
Grand

77
Poids
20
75
Poids
20

77
Poids
20
76
Poids
20


SELECT 
  DISTINCT LEAST(t1key, t2key) AS "lst", 
        GREATEST(t1key, t2key) AS "gst",
    COUNT(LEAST(t1key, t2key)) AS mn_c,
    COUNT(GREATEST(t1key, t2key)) AS mx_c
FROM
(

  SELECT t1.t_key AS "t1key", t1.t_name AS "t1name", t1.t_value AS "t1value",
         t2.t_key AS "t2key", t2.t_name AS "t2name", t2.t_value AS "t2value"
  FROM tab t1
  JOIN tab t2
  ON t1.t_key != t2.t_key
    AND t1.t_name = t2.t_name
    AND t1.t_value = t2.t_value
  ORDER BY t1.t_id, t2.t_id
)
AS t1
GROUP BY LEAST(t1key, t2key), GREATEST(t1key, t2key)
ORDER BY 1, 2, 3, 4;





lst
gst
mn_c
mx_c


75
76
8
8

75
77
6
6

76
77
6
6


SELECT COUNT(tab.t_key) FROM tab WHERE t_key = 75;


count


4


SELECT 
  DISTINCT LEAST(t1key, t2key) AS "lst", 
        GREATEST(t1key, t2key) AS "gst",
  COUNT(LEAST(t1key, t2key)) AS "mn_c"
  -- COUNT(GREATEST(t1key, t2key)) AS mx_c
FROM
(

  SELECT t1.t_key AS "t1key", t1.t_name AS "t1name", t1.t_value AS "t1value",
         t2.t_key AS "t2key", t2.t_name AS "t2name", t2.t_value AS "t2value"
  FROM tab t1
  JOIN tab t2
  ON t1.t_key != t2.t_key
    AND t1.t_name = t2.t_name
    AND t1.t_value = t2.t_value
  ORDER BY t1.t_id, t2.t_id
)
AS t1
GROUP BY LEAST(t1key, t2key), GREATEST(t1key, t2key)
HAVING COUNT(LEAST(t1key, t2key))/2 = (SELECT COUNT(tab.t_key) FROM tab WHERE t_key = t1key)
ORDER BY 1, 2, 3;

ERROR:  subquery uses ungrouped column "t1.t1key" from outer query
LINE 20: ...)/2 = (SELECT COUNT(tab.t_key) FROM tab WHERE t_key = t1key)
                                                                  ^

t1id	t1key	t1name	t1value	t2id	t2key	t2name	t2value
1	75	Couleur	Bleu	5	76	Couleur	Bleu
1	75	Couleur	Bleu	6	76	Taille	Grand
1	75	Couleur	Bleu	7	76	Poids	20
1	75	Couleur	Bleu	8	76	Teint	Y
1	75	Couleur	Bleu	9	77	Couleur	Bleu
1	75	Couleur	Bleu	10	77	Taille	Grand
1	75	Couleur	Bleu	11	77	Poids	20
1	75	Couleur	Bleu	12	77	Teint	N
2	75	Taille	Grand	5	76	Couleur	Bleu
2	75	Taille	Grand	6	76	Taille	Grand
2	75	Taille	Grand	7	76	Poids	20
2	75	Taille	Grand	8	76	Teint	Y
2	75	Taille	Grand	9	77	Couleur	Bleu
2	75	Taille	Grand	10	77	Taille	Grand
2	75	Taille	Grand	11	77	Poids	20
2	75	Taille	Grand	12	77	Teint	N
3	75	Poids	20	5	76	Couleur	Bleu
3	75	Poids	20	6	76	Taille	Grand
3	75	Poids	20	7	76	Poids	20
3	75	Poids	20	8	76	Teint	Y
3	75	Poids	20	9	77	Couleur	Bleu
3	75	Poids	20	10	77	Taille	Grand
3	75	Poids	20	11	77	Poids	20
3	75	Poids	20	12	77	Teint	N
4	75	Teint	Y	5	76	Couleur	Bleu
4	75	Teint	Y	6	76	Taille	Grand
4	75	Teint	Y	7	76	Poids	20
4	75	Teint	Y	8	76	Teint	Y
4	75	Teint	Y	9	77	Couleur	Bleu
4	75	Teint	Y	10	77	Taille	Grand
4	75	Teint	Y	11	77	Poids	20
4	75	Teint	Y	12	77	Teint	N
5	76	Couleur	Bleu	1	75	Couleur	Bleu
5	76	Couleur	Bleu	2	75	Taille	Grand
5	76	Couleur	Bleu	3	75	Poids	20
5	76	Couleur	Bleu	4	75	Teint	Y
5	76	Couleur	Bleu	9	77	Couleur	Bleu
5	76	Couleur	Bleu	10	77	Taille	Grand
5	76	Couleur	Bleu	11	77	Poids	20
5	76	Couleur	Bleu	12	77	Teint	N
6	76	Taille	Grand	1	75	Couleur	Bleu
6	76	Taille	Grand	2	75	Taille	Grand
6	76	Taille	Grand	3	75	Poids	20
6	76	Taille	Grand	4	75	Teint	Y
6	76	Taille	Grand	9	77	Couleur	Bleu
6	76	Taille	Grand	10	77	Taille	Grand
6	76	Taille	Grand	11	77	Poids	20
6	76	Taille	Grand	12	77	Teint	N
7	76	Poids	20	1	75	Couleur	Bleu
7	76	Poids	20	2	75	Taille	Grand
7	76	Poids	20	3	75	Poids	20
7	76	Poids	20	4	75	Teint	Y
7	76	Poids	20	9	77	Couleur	Bleu
7	76	Poids	20	10	77	Taille	Grand
7	76	Poids	20	11	77	Poids	20
7	76	Poids	20	12	77	Teint	N
8	76	Teint	Y	1	75	Couleur	Bleu
8	76	Teint	Y	2	75	Taille	Grand
8	76	Teint	Y	3	75	Poids	20
8	76	Teint	Y	4	75	Teint	Y
8	76	Teint	Y	9	77	Couleur	Bleu
8	76	Teint	Y	10	77	Taille	Grand
8	76	Teint	Y	11	77	Poids	20
8	76	Teint	Y	12	77	Teint	N
9	77	Couleur	Bleu	1	75	Couleur	Bleu
9	77	Couleur	Bleu	2	75	Taille	Grand
9	77	Couleur	Bleu	3	75	Poids	20
9	77	Couleur	Bleu	4	75	Teint	Y
9	77	Couleur	Bleu	5	76	Couleur	Bleu
9	77	Couleur	Bleu	6	76	Taille	Grand
9	77	Couleur	Bleu	7	76	Poids	20
9	77	Couleur	Bleu	8	76	Teint	Y
10	77	Taille	Grand	1	75	Couleur	Bleu
10	77	Taille	Grand	2	75	Taille	Grand
10	77	Taille	Grand	3	75	Poids	20
10	77	Taille	Grand	4	75	Teint	Y
10	77	Taille	Grand	5	76	Couleur	Bleu
10	77	Taille	Grand	6	76	Taille	Grand
10	77	Taille	Grand	7	76	Poids	20
10	77	Taille	Grand	8	76	Teint	Y
11	77	Poids	20	1	75	Couleur	Bleu
11	77	Poids	20	2	75	Taille	Grand
11	77	Poids	20	3	75	Poids	20
11	77	Poids	20	4	75	Teint	Y
11	77	Poids	20	5	76	Couleur	Bleu
11	77	Poids	20	6	76	Taille	Grand
11	77	Poids	20	7	76	Poids	20
11	77	Poids	20	8	76	Teint	Y
12	77	Teint	N	1	75	Couleur	Bleu
12	77	Teint	N	2	75	Taille	Grand
12	77	Teint	N	3	75	Poids	20
12	77	Teint	N	4	75	Teint	Y
12	77	Teint	N	5	76	Couleur	Bleu
12	77	Teint	N	6	76	Taille	Grand
12	77	Teint	N	7	76	Poids	20
12	77	Teint	N	8	76	Teint	Y