add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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?.
--remove all instances of `limit 1e5`
drop table if exists assets;
create table assets (
id bigserial,
name text,
owner text);

drop table if exists users;
create table users (
id bigserial primary key,
username text generated always as ('user_'||id::text) stored,
ref1 text,
ref2 text);
--a chain of 1 million users, each connected only to the next
--text prepended to the ref to prevent cheating by direct casts
with batch as (
insert into users (ref1, ref2)
select 'ref_'||case when n%2=1 then n::text else (n+1)::text end,
'ref_'||case when n%2=0 then n::text else (n+1)::text end
from generate_series(1,1e6,1) g(n)
limit 1e5
returning *
)
select * from batch limit 8;

--another 1 million users, made up of 10x100k chains
with batch as (
insert into users (ref1, ref2)
select 'ref_'||case when n%2=1 then n::text else (n+1)::text end,
'ref_'||case when n%2=0 then n::text else (n+1)::text end
from generate_series(2e6,3e6,1) g(n)
where n%1e5 not in (1,0)
limit 1e5
returning *
)
DROP TABLE

CREATE TABLE

DROP TABLE

CREATE TABLE
id username ref1 ref2
1 user_1 ref_1 ref_2
2 user_2 ref_3 ref_2
3 user_3 ref_3 ref_4
4 user_4 ref_5 ref_4
5 user_5 ref_5 ref_6
6 user_6 ref_7 ref_6
7 user_7 ref_7 ref_8
8 user_8 ref_9 ref_8 
SELECT 8
id username ref1 ref2
100001 user_100001 ref_2000003 ref_2000002
100002 user_100002 ref_2000003 ref_2000004
100003 user_100003 ref_2000005 ref_2000004
100004 user_100004 ref_2000005 ref_2000006
100005 user_100005 ref_2000007 ref_2000006
100006 user_100006 ref_2000007 ref_2000008
100007 user_100007 ref_2000009 ref_2000008
100008 user_100008 ref_2000009 ref_2000010 
SELECT 8
id username ref1 ref2
200001 user_200001 ref_4000000 ref_5000000
200002 user_200002 ref_4000001 ref_5000001
200003 user_200003 ref_4000002 ref_5000002
200004 user_200004 ref_4000003 ref_5000003
200005 user_200005 ref_4000004 ref_5000004
200006 user_200006 ref_4000005 ref_5000005
200007 user_200007 ref_4000006 ref_5000006
200008 user_200008 ref_4000007 ref_5000007 
SELECT 8
id username ref1 ref2
300001 user_300001 ref_6000000 ref_6000001
300002 user_300002 ref_6000000 ref_6000001
300003 user_300003 ref_6000000 ref_6000001
300004 user_300004 ref_6000000 ref_6000001
300005 user_300005 ref_6000000 ref_6000001
300006 user_300006 ref_6000000 ref_6000001
300007 user_300007 ref_6000000 ref_6000001
300008 user_300008 ref_6000000 ref_6000001 
SELECT 8
id username ref1 ref2
400001 user_400001 ref_7000003 ref_7000002
400002 user_400002 ref_7000003 ref_7000004
400003 user_400003 ref_7000005 ref_7000004
400004 user_400004 ref_7000005 ref_7000006
400005 user_400005 ref_7000007 ref_7000006
400006 user_400006 ref_7000007 ref_7000008
400007 user_400007 ref_7000009 ref_7000008
400008 user_400008 ref_7000009 ref_7000010 
SELECT 8
id name owner
1 asset_1 ref_1
2 asset_2 ref_10
3 asset_3 ref_100
4 asset_4 ref_1000
5 asset_5 ref_10000
6 asset_6 ref_100000
7 asset_7 ref_100001
8 asset_8 ref_10001 
SELECT 8
id name owner
500107 asset_5000001 null
500108 asset_5000002 null
500109 asset_5000003 null
500110 asset_5000004 null
500111 asset_5000005 null
500112 asset_5000006 null
500113 asset_5000007 null
500114 asset_5000008 null 
SELECT 8
id username ref1 ref2
1 user_1 ref_1 ref_2
2 user_2 ref_3 ref_2
3 user_3 ref_3 ref_4
4 user_4 ref_5 ref_4
5 user_5 ref_5 ref_6
6 user_6 ref_7 ref_6
7 user_7 ref_7 ref_8
8 user_8 ref_9 ref_8
9 user_9 ref_9 ref_10
10 user_10 ref_11 ref_10 
SELECT 10
id username ref1 ref2
109 user_109 ref_109 ref_110
236 user_236 ref_237 ref_236
493 user_493 ref_493 ref_494
646 user_646 ref_647 ref_646
756 user_756 ref_757 ref_756
872 user_872 ref_873 ref_872
972 user_972 ref_973 ref_972
1057 user_1057 ref_1057 ref_1058
1063 user_1063 ref_1063 ref_1064
1137 user_1137 ref_1137 ref_1138
1252 user_1252 ref_1253 ref_1252
1290 user_1290 ref_1291 ref_1290
1322 user_1322 ref_1323 ref_1322
1326 user_1326 ref_1327 ref_1326
1449 user_1449 ref_1449 ref_1450
1641 user_1641 ref_1641 ref_1642
1704 user_1704 ref_1705 ref_1704
1769 user_1769 ref_1769 ref_1770
1836 user_1836 ref_1837 ref_1836
1917 user_1917 ref_1917 ref_1918 
SELECT 20