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), (5,5), (5,5)
8 rows affected

SELECT * FROM test;


col1
col2


1
6

2
3

3
2

4
7

5
8

6
1

5
5

5
5


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

5
5
2

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


3
38


SELECT
  COUNT(*) * 100 / (SELECT COUNT(*) FROM test)
FROM
    test t1
WHERE NOT EXISTS (SELECT 1
                  FROM   test t2
                  WHERE  t2.col1 = t1.col2
                  AND    t2.col2 = t1.col1);


?column?


25


SELECT t1.col1, t1.col2, 1
                  FROM   test t1, test t2
                  WHERE  t2.col1 = t1.col2
                  AND    t2.col2 = t1.col1;


col1
col2
?column?


6
1
1

3
2
1

2
3
1

5
5
1

5
5
1

5
5
1

5
5
1

1
6
1