Postgres 12

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 info(
   table_catalog VARCHAR(5) NOT NULL
  ,table_schema  VARCHAR(13) NOT NULL
  ,Table_name    VARCHAR(15) NOT NULL
);
INSERT INTO info(table_catalog,table_schema,Table_name) VALUES ('dev','BigBangTheory','SomeTable1');
INSERT INTO info(table_catalog,table_schema,Table_name) VALUES ('dev','BigBangTheory','SomeTable2');
INSERT INTO info(table_catalog,table_schema,Table_name) VALUES ('dev','BigBangTheory','SomeTable3');
INSERT INTO info(table_catalog,table_schema,Table_name) VALUES ('dev','Walle','AnotherTable100');
INSERT INTO info(table_catalog,table_schema,Table_name) VALUES ('dev','Walle','AnotherTable200');
INSERT INTO info(table_catalog,table_schema,Table_name) VALUES ('dev','StarWars','SpaceTablexxx');
INSERT INTO info(table_catalog,table_schema,Table_name) VALUES ('dev','StarWars','SpaceTableyyy');
INSERT INTO info(table_catalog,table_schema,Table_name) VALUES ('stage','BigBangTheory','SomeTable1');
INSERT INTO info(table_catalog,table_schema,Table_name) VALUES ('stage','BigBangTheory','SomeTable2');
INSERT INTO info(table_catalog,table_schema,Table_name) VALUES ('stage','BigBangTheory','SomeTable3');
INSERT INTO info(table_catalog,table_schema,Table_name) VALUES ('stage','Walle','AnotherTable100');
INSERT INTO info(table_catalog,table_schema,Table_name) VALUES ('stage','Walle','AnotherTable200');
INSERT INTO info(table_catalog,table_schema,Table_name) VALUES ('stage','StarWars','SpaceTablexxx');
INSERT INTO info(table_catalog,table_schema,Table_name) VALUES ('stage','StarWars','SpaceTableyyy');
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

CREATE TABLE IF NOT EXISTS resources
(
"id" SERIAL NOT NULL PRIMARY KEY,
"type" varchar(100) NOT NULL,             
"name" varchar(100) NOT NULL,    
"parent" int,
FOREIGN KEY (parent) REFERENCES resources (id)
);
with 
	dbs as (
		insert into resources (type, name)
		select distinct 'database', table_catalog 
		from info
		returning id, name
	),
	schemas as (
		insert into resources(type, name, parent)
		select distinct 'schema', i.table_schema, d.id
		from info i
		inner join dbs d on d.name = i.table_catalog
		returning id, name, parent
	)
insert into resources(type, name, parent)
select 'table', table_name, s.id
from info i
inner join schemas s on s.name = i.table_schema
inner join dbs d on d.id = s.parent and d.name = i.table_catalog
14 rows affected

select * from resources order by type, id


id
type
name
parent


1
database
dev
null

2
database
stage
null

3
schema
StarWars
1

4
schema
Walle
1

5
schema
BigBangTheory
2

6
schema
Walle
2

7
schema
StarWars
2

8
schema
BigBangTheory
1

9
table
SomeTable1
8

10
table
SomeTable2
8

11
table
SomeTable3
8

12
table
SomeTable1
5

13
table
SomeTable2
5

14
table
SomeTable3
5

15
table
SpaceTableyyy
3

16
table
SpaceTablexxx
3

17
table
SpaceTablexxx
7

18
table
SpaceTableyyy
7

19
table
AnotherTable200
4

20
table
AnotherTable100
4

21
table
AnotherTable100
6

22
table
AnotherTable200
6

id	type	name	parent
1	database	dev	null
2	database	stage	null
3	schema	StarWars	1
4	schema	Walle	1
5	schema	BigBangTheory	2
6	schema	Walle	2
7	schema	StarWars	2
8	schema	BigBangTheory	1
9	table	SomeTable1	8
10	table	SomeTable2	8
11	table	SomeTable3	8
12	table	SomeTable1	5
13	table	SomeTable2	5
14	table	SomeTable3	5
15	table	SpaceTableyyy	3
16	table	SpaceTablexxx	3
17	table	SpaceTablexxx	7
18	table	SpaceTableyyy	7
19	table	AnotherTable200	4
20	table	AnotherTable100	4
21	table	AnotherTable100	6
22	table	AnotherTable200	6