Postgres 12

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 12.14 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-16), 64-bit

SELECT 1

CREATE TABLE 
sequence
(
    id SERIAL PRIMARY KEY,
    dna VARCHAR(50) NOT NULL
);

INSERT INTO sequence (dna) VALUES ('agctagctagct');
INSERT INTO sequence (dna) VALUES ('agctagctagct');
INSERT INTO sequence (dna) VALUES ('agctagctagct');

INSERT INTO sequence (dna) VALUES ('tagctagctagc');
INSERT INTO sequence (dna) VALUES ('tagctagctagc');
INSERT INTO sequence (dna) VALUES ('tagctagctagc');
INSERT INTO sequence (dna) VALUES ('tagctagctagc');

INSERT INTO sequence (dna) VALUES ('ggcggcggcggc');
INSERT INTO sequence (dna) VALUES ('ggcggcggcggc');

CREATE TABLE

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

INSERT 0 1

-- SELECT * FROM sequence ORDER BY id;
SELECT s.dna, COUNT(dna)
FROM sequence s
GROUP BY dna
HAVING COUNT(dna) > 1
ORDER BY s.dna;

dna
count

agctagctagct
3

ggcggcggcggc
2

tagctagctagc
4

SELECT 3

DELETE FROM sequence s1 USING sequence s2
WHERE
  s1.id < s2.id AND
  s1.dna = s2.dna;

DELETE 6

SELECT s.dna, COUNT(dna)
FROM sequence s
GROUP BY dna
ORDER BY s.dna;

dna
count

agctagctagct
1

ggcggcggcggc
1

tagctagctagc
1

SELECT 3