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

CREATE TABLE aleph_beth
(
  the_letter CHAR
);
INSERT INTO aleph_beth
VALUES
('A'),
('B'),
('C'),
('D'),
('E'),
('F'),
('G'),
('H'),
('I'),
('J'),
('K'),
('L'),
('M'),
('N'),
('O'),
('P'),
('Q'),
('R'),
('S'),
('T'),
('V'),
('U'),
('W'),
('X'),
('Y'),
('Z');
26 rows affected

SELECT the_letter FROM aleph_beth WHERE the_letter < 'E';  -- just a test to see that the table has been created!


the_letter


A

B

C

D


SELECT
  a.the_letter AS let_a,
  b.the_letter AS let_b,
  ASCII(a.the_letter) * ASCII(b.the_letter) AS the_product
FROM
  aleph_beth a
JOIN
  aleph_beth b
ON
  a.the_letter < b.the_letter
  AND ASCII(a.the_letter) * ASCII(b.the_letter) < 4500 -- < 4500 is for testing, otherwise 325 records (= (26 * 25/2)
ORDER BY let_a, let_b


let_a
let_b
the_product


A
B
4290

A
C
4355

A
D
4420

A
E
4485

B
C
4422

B
D
4488


SELECT the_product, COUNT(the_product)
FROM
(
SELECT
  a.the_letter AS let_a,
  b.the_letter AS let_b,
  ASCII(a.the_letter) * ASCII(b.the_letter) AS the_product
FROM
  aleph_beth a
JOIN
  aleph_beth b
ON
  a.the_letter < b.the_letter
  ORDER BY let_a, let_b
) x
GROUP BY the_product
HAVING COUNT(the_product) > 1
ORDER BY the_product;           -- So, we have duplicate values of the_product!


the_product
count


5460
2

5544
2

5850
2

6120
2

6160
2

6300
2

6480
2


SELECT
  a.the_letter AS let_a,
  b.the_letter AS let_b,
  ASCII(a.the_letter) * ASCII(b.the_letter) AS the_product
FROM
  aleph_beth a
JOIN
  aleph_beth b
ON
  a.the_letter < b.the_letter
WHERE ASCII(a.the_letter) * ASCII(b.the_letter) = 5544 -- from above, now go here ( https://dbfiddle.uk/?rdbms=postgres_10&fiddle=64857b8bb4f0a56fc95fa7c8d045f2bf )
  ORDER BY let_a, let_b


let_a
let_b
the_product


B
T
5544

H
M
5544