Postgres 9.6

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 (
    CompanyId       INT       NOT NULL,
    Name            TEXT      NOT NULL,
    FoundingDate    DATE      NOT NULL,
    Etcetera        TEXT      NOT NULL,
    CreatedDateTime TIMESTAMP NOT NULL DEFAULT NOW(),
    --
    CONSTRAINT Company_PK PRIMARY KEY (CompanyId),
    CONSTRAINT Company_AK UNIQUE      (Name)
);

CREATE TABLE Person (
    PersonId        INT       NOT NULL, 
    FirstName       TEXT      NOT NULL,
    LastName        TEXT      NOT NULL,
    GenderCode      TEXT      NOT NULL,
    BirthDate       DATE      NOT NULL,
    BirthPlace      TEXT      NOT NULL,  
    Address         TEXT      NOT NULL,   
    PhoneNumber     TEXT      NOT NULL,     
    CreatedDateTime TIMESTAMP NOT NULL DEFAULT NOW(),  
    --
    CONSTRAINT Person_PK PRIMARY KEY (PersonId),
    CONSTRAINT Person_AK UNIQUE      ( 
        FirstName,
        LastName,
        GenderCode,
        BirthDate,
        BirthPlace
    )
);

CREATE TABLE CompanyOwner ( 
    CompanyId          INT       NOT NULL,
    OwnerId            INT       NOT NULL,
    StockShareQuantity INT       NOT NULL,
    Etcetera           TEXT      NOT NULL,  
    CreatedDateTime    TIMESTAMP NOT NULL DEFAULT NOW(),
    --
    CONSTRAINT CompanyOwner_PK          PRIMARY KEY (CompanyId, OwnerId),
    CONSTRAINT CompanyOwnerToCompany_FK FOREIGN KEY (CompanyId)
        REFERENCES Company (CompanyId),
    CONSTRAINT CompanyOwnerToPerson_FK  FOREIGN KEY (OwnerId)
        REFERENCES Person (PersonId),
    CONSTRAINT StockShareQtyIsValid_CK  CHECK       (StockShareQuantity >= 0)
);

CREATE VIEW CompanyAndOwner AS
    SELECT C.CompanyId,
           C.Name AS CompanyName,
           P.PersonId,
           P.FirstName,
           P.LastName,
           P.BirthDate,
           P.BirthPlace,
           P.GenderCode,
           P.Address,
           P.PhoneNumber,
          CO.StockShareQuantity,
           P.CreatedDateTime
        FROM Person P
        JOIN CompanyOwner CO
          ON CO.OwnerId = P.PersonId
        JOIN Company C
          ON C.CompanyId = CO.CompanyId;
INSERT INTO Company 
  (CompanyId, Name, FoundingDate, Etcetera)
VALUES
  (1748, 'Database Modeling Inc.', '1985-06-30', 'Foo'),
  (1750, 'Application Programming Co.', '1987-10-14', 'Bar');
  
  INSERT INTO Person 
  (PersonId, FirstName, LastName, BirthDate, BirthPlace, GenderCode, Address, PhoneNumber)
VALUES
  (1, 'Edgar', 'Codd', '1923-08-19', 'Fortuneswell, UK', 'M', 'IBM Research Laboratory K01/282, 5600 Cottle Road, San Jose, CA, USA', '01-800-17-50-17-50'),
  (2, 'Alan', 'Turing', '1912-06-23', 'Maida Vale, UK','M', 'National Physical Laboratory, Hampton Road, Teddington, TW11 0LW, England', '01-800-17-48-17-48'),
  (3, 'Grace', 'Hopper', '1906-12-09', 'New York City, USA', 'F', 'Navy’s Office of Information Systems Planning, USA.', '01-800-17-50-17-50'),   
  (4, 'Diego', 'Velázquez', '1599-06-06', 'Seville, Spain', 'M', 'Palacio Real, Madrid, Spain', '01-800-17-50-17-50'),
  (5, 'Michelangelo', 'Buonarroti', '1475-03-06', 'Caprese, Italy', 'M', 'Sistine Chapel, Vatican City State', '01-800-17-50-17-50'); 
  
INSERT INTO CompanyOwner 
  (CompanyId, OwnerId, StockShareQuantity, Etcetera)
VALUES
  (1748, 1, 2500, 'V'),
  (1750, 2, 8000, 'W'),
  (1750, 3, 3580, 'X'),
  (1748, 4, 12899, 'Y'),
  (1750, 5, 12899, 'Z'); 
2 rows affected

5 rows affected

5 rows affected

SELECT * 
  FROM CompanyAndOwner 
 WHERE CompanyId = 1750;


companyid
companyname
personid
firstname
lastname
birthdate
birthplace
gendercode
address
phonenumber
stocksharequantity
createddatetime


1750
Application Programming Co.
2
Alan
Turing
1912-06-23
Maida Vale, UK
M
National Physical Laboratory, Hampton Road, Teddington, TW11 0LW, England
01-800-17-48-17-48
8000
2022-05-07 03:40:37.03482

1750
Application Programming Co.
3
Grace
Hopper
1906-12-09
New York City, USA
F
Navy’s Office of Information Systems Planning, USA.
01-800-17-50-17-50
3580
2022-05-07 03:40:37.03482

1750
Application Programming Co.
5
Michelangelo
Buonarroti
1475-03-06
Caprese, Italy
M
Sistine Chapel, Vatican City State
01-800-17-50-17-50
12899
2022-05-07 03:40:37.03482


SELECT CompanyName,
       FirstName AS OwnerFirstName,
       LastName AS OwnerLastName,
       StockShareQuantity
  FROM CompanyAndOwner 
 WHERE CompanyId = 1750; 


companyname
ownerfirstname
ownerlastname
stocksharequantity


Application Programming Co.
Alan
Turing
8000

Application Programming Co.
Grace
Hopper
3580

Application Programming Co.
Michelangelo
Buonarroti
12899


SELECT * 
  FROM CompanyAndOwner 
 WHERE CompanyId = 1748; 


companyid
companyname
personid
firstname
lastname
birthdate
birthplace
gendercode
address
phonenumber
stocksharequantity
createddatetime


1748
Database Modeling Inc.
1
Edgar
Codd
1923-08-19
Fortuneswell, UK
M
IBM Research Laboratory K01/282, 5600 Cottle Road, San Jose, CA, USA
01-800-17-50-17-50
2500
2022-05-07 03:40:37.03482

1748
Database Modeling Inc.
4
Diego
Velázquez
1599-06-06
Seville, Spain
M
Palacio Real, Madrid, Spain
01-800-17-50-17-50
12899
2022-05-07 03:40:37.03482


SELECT CompanyName,
       FirstName AS OwnerFirstName,
       LastName AS OwnerLastName,
       StockShareQuantity
  FROM CompanyAndOwner 
 WHERE CompanyId = 1748; 


companyname
ownerfirstname
ownerlastname
stocksharequantity


Database Modeling Inc.
Edgar
Codd
2500

Database Modeling Inc.
Diego
Velázquez
12899


SELECT COUNT(OwnerId) AS OwnedCompaniesQuantity
   FROM CompanyOwner
  WHERE OwnerId = 1;


ownedcompaniesquantity


1

companyid	companyname	personid	firstname	lastname	birthdate	birthplace	gendercode	address	phonenumber	stocksharequantity	createddatetime
1750	Application Programming Co.	2	Alan	Turing	1912-06-23	Maida Vale, UK	M	National Physical Laboratory, Hampton Road, Teddington, TW11 0LW, England	01-800-17-48-17-48	8000	2022-05-07 03:40:37.03482
1750	Application Programming Co.	3	Grace	Hopper	1906-12-09	New York City, USA	F	Navy’s Office of Information Systems Planning, USA.	01-800-17-50-17-50	3580	2022-05-07 03:40:37.03482
1750	Application Programming Co.	5	Michelangelo	Buonarroti	1475-03-06	Caprese, Italy	M	Sistine Chapel, Vatican City State	01-800-17-50-17-50	12899	2022-05-07 03:40:37.03482

companyid	companyname	personid	firstname	lastname	birthdate	birthplace	gendercode	address	phonenumber	stocksharequantity	createddatetime
1748	Database Modeling Inc.	1	Edgar	Codd	1923-08-19	Fortuneswell, UK	M	IBM Research Laboratory K01/282, 5600 Cottle Road, San Jose, CA, USA	01-800-17-50-17-50	2500	2022-05-07 03:40:37.03482
1748	Database Modeling Inc.	4	Diego	Velázquez	1599-06-06	Seville, Spain	M	Palacio Real, Madrid, Spain	01-800-17-50-17-50	12899	2022-05-07 03:40:37.03482