Postgres 16

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 company (
        id INT PRIMARY KEY,
        company_name VARCHAR(255),
        address VARCHAR(255),
        telephone VARCHAR(20),
        fax VARCHAR(20)
    );
CREATE TABLE employee (
        id INT PRIMARY KEY,
        name VARCHAR(255),
        company_id INT,
        address VARCHAR(255),
        telephone VARCHAR(20),
        FOREIGN KEY (company_id) REFERENCES company(id)
    );

CREATE TABLE



CREATE TABLE


select setseed(.1);
insert into company 
  select n,
         'name_of_company_'||n,
         'address_of_company_'||n,
         lpad((random()*1e10)::bigint::text,10,'5'),
         lpad((random()*1e10)::bigint::text,10,'5')
  from generate_series(1,20_000,1)_(n);
insert into employee 
  select n,
         'name_of_employee_'||n,
         --least(2*(random()*10)^4+random()*100+1,20e3),
         floor(1+random()*20e3),
         'address_of_employee_'||n,
         (random()*1e10)::bigint::text
  from generate_series(1,65_000,1)_(n);


setseed






SELECT 1



INSERT 0 20000



INSERT 0 65000


SELECT c.id, c.company_name, COUNT(e.id) AS total_employees
FROM company c
INNER JOIN employee e ON c.id= e.company_id
GROUP BY c.id, c.company_name
HAVING COUNT(e.id) = 10000
LIMIT 1000;


id
company_name
total_employees



SELECT 0