Postgres 11

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 (col1 INTEGER, col2 INTEGER);
INSERT INTO test VALUES (1, 6), (2,3), (3, 2), (4, 7), (5, 8), (6, 1);
6 rows affected

SELECT * FROM test;


col1
col2


1
6

2
3

3
2

4
7

5
8

6
1


SELECT LEAST(col1, col2) AS mn_c, GREATEST(col1, col2) AS mx_c,
COUNT(*) 
FROM test
GROUP BY 1, 2


mn_c
mx_c
count


2
3
2

4
7
1

1
6
2

5
8
1


SELECT COUNT(cnt1) AS matched_count,
       ROUND(COUNT(cnt1)/(SELECT COUNT(*) FROM test)::FLOAT * 100) AS percentage
FROM 
(
  SELECT LEAST(col1, col2) AS mn_c, GREATEST(col1, col2) AS mx_c,
  COUNT(*) AS cnt1 
  FROM test
  GROUP BY 1, 2
  HAVING COUNT(*) > 1
) AS t


matched_count
percentage


2
33