Skip to content

Instantly share code, notes, and snippets.

@onderkalaci
Last active May 12, 2023 11:33
Show Gist options
  • Save onderkalaci/115f4f790f93463f51d39d3e128f4085 to your computer and use it in GitHub Desktop.
Save onderkalaci/115f4f790f93463f51d39d3e128f4085 to your computer and use it in GitHub Desktop.
Hackhaton
WITH joins_only AS (SELECT left_tablename, p1.attname, right_tablename, p2.attname, call_count  FROM public.equi_filters, pg_attribute p1, pg_attribute p2 WHERE right_tablename != 'NULL' AND equi_filters.left_tablename::text::regclass = p1.attrelid AND equi_filters.right_tablename::text::regclass = p2.attrelid AND p1.attnum = left_attrno AND p2.attnum = right_attrname) SELECT * FROM joins_only;
WITH only_filters AS (SELECT left_tablename, attname,call_count FROM pg_attribute, equi_filters WHERE right_tablename = 'NULL' AND  equi_filters.left_tablename::text::regclass = attrelid AND attnum=left_attrno) SELECT * FROM only_filters;
CREATE VIEW foreign_keys AS
WITH filtered_tables AS (
SELECT
schemaname AS schema_name,
tablename AS table_name
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
AND NOT EXISTS (
SELECT 1
FROM pg_depend
WHERE deptype = 'e' AND objid = schemaname::text::regnamespace
)
),
foreign_keys AS (
SELECT
tc.table_schema AS schema_name,
tc.table_name AS table_name,
kcu.column_name AS column_name,
ccu.table_schema AS referenced_schema,
ccu.table_name AS referenced_table,
ccu.column_name AS referenced_column,
rc.update_rule AS update_rule,
rc.delete_rule AS delete_rule
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.referential_constraints AS rc ON tc.constraint_name = rc.constraint_name
JOIN information_schema.constraint_column_usage AS ccu ON rc.unique_constraint_name = ccu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
),
filtered_foreign_keys AS (
SELECT *
FROM foreign_keys
WHERE schema_name IN (SELECT schema_name FROM filtered_tables) AND
table_name IN (SELECT table_name FROM filtered_tables)
)
SELECT
json_build_object(
'schema_name', schema_name,
'table_name', table_name,
'column_name', column_name,
'referenced_schema', referenced_schema,
'referenced_table', referenced_table,
'referenced_column', referenced_column,
'update_rule', update_rule,
'delete_rule', delete_rule
) AS foreign_key_info
FROM filtered_foreign_keys;
CREATE VIEW table_accesses AS
WITH filtered_tables AS (
SELECT
schemaname AS schema_name,
tablename AS table_name
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
AND NOT EXISTS (
SELECT 1
FROM pg_depend
WHERE deptype = 'e' AND objid = schemaname::text::regnamespace
)
)
SELECT
json_build_object(
'schema_name', ft.schema_name,
'table_name', ft.table_name,
'total_reads', COALESCE(st.seq_scan, 0) + COALESCE(st.idx_scan, 0),
'total_writes', COALESCE(st.n_tup_ins, 0) + COALESCE(st.n_tup_upd, 0) + COALESCE(st.n_tup_del, 0)
) AS table_stats
FROM filtered_tables AS ft
JOIN pg_stat_user_tables AS st ON ft.schema_name = st.schemaname AND ft.table_name = st.relname
ORDER BY ft.schema_name, ft.table_name;
CREATE VIEW table_list AS
WITH table_sizes AS (
SELECT
schemaname AS schema_name,
tablename AS table_name,
(pg_relation_size(schemaname || '.' || tablename)) AS size
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema', 'columnar_internal')
),
table_columns AS (
SELECT
table_schema AS schema_name,
table_name,
json_agg(json_build_object('column_name', column_name, 'data_type', data_type, 'is_nullable', is_nullable)) AS columns
FROM information_schema.columns
WHERE table_schema NOT IN ('pg_catalog', 'information_schema', 'columnar_internal')
GROUP BY table_schema, table_name
),
filtered_tables AS (
SELECT
table_sizes.schema_name,
table_sizes.table_name,
table_sizes.size,
table_columns.columns
FROM table_sizes
JOIN table_columns ON table_sizes.schema_name = table_columns.schema_name AND table_sizes.table_name = table_columns.table_name
WHERE NOT EXISTS (
SELECT 1 FROM pg_depend where deptype = 'e' and objid =table_columns.schema_name::text::regnamespace
)
)
SELECT
json_build_object(
'schema_name', schema_name,
'table_name', table_name,
'size', size,
'columns', columns
) AS table_info
FROM filtered_tables;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment