Postgres 13

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

CREATE TABLE

CREATE TABLE

WITH input_sequence AS (
  SELECT
    generate_series AS index_id
  FROM
    generate_series(1, 5)
),
new_accounts AS (
  INSERT INTO accounts 
  SELECT 
  FROM 
    input_sequence
  RETURNING
    *
),
input_accounts AS (
  SELECT
    input_sequence.index_id,
    new_accounts.id
  FROM
    input_sequence CROSS JOIN new_accounts
),
new_profiles AS (
  INSERT INTO profiles 
  SELECT
  FROM 
    input_sequence
  RETURNING
    *
),
input_profiles AS (
  SELECT
    input_sequence.index_id,
    new_profiles.id
  FROM
    input_sequence CROSS JOIN new_profiles 
),
new_account_profile_pairs AS (
  INSERT INTO account_profiles
    ( 
      account_id, 
      profile_id 
    )
  SELECT
    input_accounts.id AS account_id,
    input_profiles.id AS profile_id
  FROM
    input_accounts
    JOIN
    input_profiles
    ON
    input_accounts.index_id = input_profiles.index_id
)
SELECT
  (
    SELECT
      count(*)
    FROM
      input_accounts
  ) AS input_accounts,
  (
    SELECT
      count(*)
    FROM
      input_profiles
  ) AS input_profiles
;

SELECT
  (
    SELECT
      count(*)
    FROM
      accounts
  ) AS accounts,
  (
    SELECT
      count(*)
    FROM
      profiles
  ) AS profiles,
  (
    SELECT
      count(*)
    FROM
      account_profiles
  ) AS acc_profiles
;

input_accounts	input_profiles
25	25

SELECT 1

accounts	profiles	acc_profiles
5	5	125

SELECT 1