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
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 | 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",
| id | created_at | login | password | 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