-
Notifications
You must be signed in to change notification settings - Fork 22
/
Copy pathforeign-key-indexes.html
87 lines (79 loc) · 3.5 KB
/
foreign-key-indexes.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
<html>
<meta content="width=device-width, initial-scale=1.0" name="viewport">
<head>
<title>
leontrolski - foreign key indexes
</title>
<style>
body {margin: 5% auto; background: #fff7f7; color: #444444; font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, Helvetica, Arial, sans-serif; font-size: 16px; line-height: 1.8; max-width: 63%;}
@media screen and (max-width: 800px) {body {font-size: 14px; line-height: 1.4; max-width: 90%;}}
pre {width: 100%; border-top: 3px solid gray; border-bottom: 3px solid gray;}
a {border-bottom: 1px solid #444444; color: #444444; text-decoration: none; text-shadow: 0 1px 0 #ffffff; }
a:hover {border-bottom: 0;}
.inline {background: #b3b2b226; padding-left: 0.3em; padding-right: 0.3em; white-space: nowrap;}
blockquote {font-style: italic;color:black;background-color:#f2f2f2;padding:2em;}
details {border-bottom:solid 5px gray;}
</style>
<link href="https://unpkg.com/[email protected]/themes/prism-vs.css" rel="stylesheet">
<script src="https://cdnjs.cloudflare.com/ajax/libs/prism/1.20.0/components/prism-core.min.js">
</script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/prism/1.20.0/plugins/autoloader/prism-autoloader.min.js">
</script>
</head>
<body>
<a href="index.html">
<img src="pic.png" style="height:2em">
⇦
</a>
<p><i>2021-07-15</i></p>
<h1>
Don't get bitten by missing foreign key indexes
</h1>
<p>
Postgres doesn't give you indexes on foreign keys by default (unlike primary keys). This makes some sense when you think about it - there's no necessity for them in order to keep the constraint checking efficient.
</p>
<p>
In reality, you nearly always want an index on any foreign keys - every time you join tables on said key. I lost track of the number of times I've hit performance bugs due to this, so I added a test to check, feel free to steal:
</p>
<pre class="language-python"><code># lovingly copy-pasta-ed from stackoverflow
foreign_key_sql = """
SELECT
tc.table_name,
kcu.column_name,
tc.constraint_name
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
AND tc.table_schema = kcu.table_schema
LEFT OUTER JOIN (
SELECT
t.relname AS table_name,
a.attname AS column_name,
i.relname AS index_name
FROM
pg_class AS t,
pg_class AS i,
pg_index AS ix,
pg_attribute AS a
WHERE
t.oid = ix.indrelid
AND i.oid = ix.indexrelid
AND a.attrelid = t.oid
AND a.attnum = ANY(ix.indkey)
AND t.relkind = 'r'
) as indexes
ON tc.table_name = indexes.table_name AND kcu.column_name = indexes.column_name
WHERE tc.constraint_type = 'FOREIGN KEY'
AND indexes.index_name IS NULL
"""
def assert_all_foreign_keys_have_indexes(engine: Engine) -> None:
rows = list(engine.execute(foreign_key_sql))
if rows:
msg = "The following foreign keys have no index:\n" + "\n".join(
f"{table=} {column=} {foreign_key=}"
for table, column, foreign_key in rows
)
raise RuntimeError(msg)</code>
</pre>
</body>
</html>