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

/*
  Tables
*/

CREATE TABLE accounts (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  login text NOT NULL,
  password text NOT NULL,
  email text,
  init_index bigint,
  parent_id bigint REFERENCES accounts
);

CREATE TABLE


/*
  Helper functions.
*/

-- random string generator
-- https://www.simononsoftware.com/random-string-in-postgresql/#combined-md5-and-sql
CREATE FUNCTION random_string(length integer)
RETURNS text
LANGUAGE SQL
AS $$ 
  SELECT upper(
    substring(
      (
        SELECT 
          string_agg(
            md5(
              CAST (random() AS text)
            ), 
            ''
          )
        FROM 
          generate_series(
            1,
            CAST (CEIL(length / 32.) AS integer)
          ) 
      ), 
      1, 
      length
    ) 
  );
$$;

--sequence generator
CREATE FUNCTION create_series(amount integer)
RETURNS TABLE (
  index_id bigint
)
LANGUAGE SQL
AS $BODY$
  SELECT
    generate_series AS index_id
  FROM
    generate_series(1, amount)
$BODY$;

CREATE FUNCTION



CREATE FUNCTION


/*
  Random rows
*/

INSERT INTO accounts
  (
    login,
    password
  )
SELECT
  (random_string(10)) AS login,
  (random_string(20)) AS password
FROM
  create_series(7)
RETURNING
  *


id
created_at
login
password
email
init_index
parent_id


1
2022-10-30 18:51:15.711518+00
873431C2BB
EA77031FC64FF7697AF9
null
null
null

2
2022-10-30 18:51:15.711518+00
E90C1EFCC6
51164A31E3FC002E8776
null
null
null

3
2022-10-30 18:51:15.711518+00
F465A8896D
68BA64B12EC3B7B25309
null
null
null

4
2022-10-30 18:51:15.711518+00
1F4BAAF5B9
99E51BE286B9A3FE9275
null
null
null

5
2022-10-30 18:51:15.711518+00
0BB0DC78E8
1C3B0C56A3BE3D5A7987
null
null
null

6
2022-10-30 18:51:15.711518+00
1100FB3136
B7823EDB76216127F89E
null
null
null

7
2022-10-30 18:51:15.711518+00
4EC6EB44E8
491D17ADD0E20A1584B6
null
null
null



INSERT 0 7


/*
  Referential insert.
*/

WITH account_inits AS (
  SELECT
    row_number () OVER () as init_index,
    login,
    password,
    email,
    reference_id,
    parent_reference
  FROM
    json_to_recordset(
      $json$
      [
        {
          "login": "11EB19631A",
          "password": "AE128AADEF97F1E54021",
          "reference_id": 1
        },
        {
          "login": "3ED4ECBBC9",
          "password": "E67EDDB6033D02140BB4",
          "email": "a@b",
          "reference_id": 2,
          "parent_reference": 1
        },
        {
          "login": "C86D7E2CF0",
          "password": "75404617C000A0EB070C",
          "reference_id": 3,
          "parent_reference": 2
        },
        {
          "login": "C51D77BF87",
          "password": "605509993A05EE393081",
          "email": null,
          "reference_id": 4,
          "parent_reference": 2
        },
        {
          "login": "2BAB5AA533",
          "password": "DFCAB818D812B1F8F761",
          "reference_id": 5,
          "parent_reference": 3
        },
        {
          "login": "4229D47E2C",
          "password": "CE4E14ED6AD77CBC71B5",
          "email": "b@c",
          "reference_id": 6,
          "parent_reference": 2
        }
      ]
      $json$
    ) AS input_init(
      login text,
      password text,
      email text,
      reference_id int,
      parent_reference int
    )
),
-- create new accounts
new_accounts AS (
  INSERT INTO accounts
    (
      init_index,
      login,
      password,
      email
    )
  SELECT
    init_index,
    login,
    password,
    email
  FROM
    account_inits
  RETURNING
    *
),
parent_id_pairs AS (
    SELECT
      new_accounts.id,
      account_inits.reference_id
    FROM
      new_accounts
      INNER JOIN
      account_inits
      ON
        account_inits.reference_id IN (
          SELECT DISTINCT
            parent_reference AS reference_id
          FROM
            account_inits
        )
        AND
        new_accounts.init_index = account_inits.init_index
),
account_updates AS (
  SELECT
    new_accounts.id,
    parent_id_pairs.id AS parent_id
  FROM
    new_accounts
    INNER JOIN
    account_inits
    ON
      account_inits.parent_reference IS NOT NULL
      AND
      new_accounts.init_index = account_inits.init_index
    INNER JOIN
    parent_id_pairs
    ON
      account_inits.parent_reference = parent_id_pairs.reference_id
),
updated_accounts AS (
  UPDATE
    accounts
  SET
    parent_id = account_updates.parent_id
  FROM
    account_updates
  WHERE
    account_updates.id = accounts.id
  RETURNING
    *
)
SELECT
  *
FROM
  updated_accounts
;

SELECT
  id,
  init_index,
  parent_id
FROM
  accounts
;


id
created_at
login
password
email
init_index
parent_id
id
parent_id



SELECT 0




id
init_index
parent_id


1
null
null

2
null
null

3
null
null

4
null
null

5
null
null

6
null
null

7
null
null

8
1
null

9
2
null

10
3
null

11
4
null

12
5
null

13
6
null



SELECT 13

id	init_index	parent_id
1	null	null
2	null	null
3	null	null
4	null	null
5	null	null
6	null	null
7	null	null
8	1	null
9	2	null
10	3	null
11	4	null
12	5	null
13	6	null

id	created_at	login	password	email	init_index	parent_id
1	2022-10-30 18:51:15.711518+00	873431C2BB	EA77031FC64FF7697AF9	null	null	null
2	2022-10-30 18:51:15.711518+00	E90C1EFCC6	51164A31E3FC002E8776	null	null	null
3	2022-10-30 18:51:15.711518+00	F465A8896D	68BA64B12EC3B7B25309	null	null	null
4	2022-10-30 18:51:15.711518+00	1F4BAAF5B9	99E51BE286B9A3FE9275	null	null	null
5	2022-10-30 18:51:15.711518+00	0BB0DC78E8	1C3B0C56A3BE3D5A7987	null	null	null
6	2022-10-30 18:51:15.711518+00	1100FB3136	B7823EDB76216127F89E	null	null	null
7	2022-10-30 18:51:15.711518+00	4EC6EB44E8	491D17ADD0E20A1584B6	null	null	null