From 862da3cfed8db462589d0271e144f21f408cf73b Mon Sep 17 00:00:00 2001
From: Yorick Peterse <yorickpeterse@gmail.com>
Date: Thu, 10 Aug 2017 17:53:20 +0200
Subject: Add more database development related docs

---
 doc/development/sql.md | 26 ++++++++++++++++++++++++++
 1 file changed, 26 insertions(+)

(limited to 'doc/development/sql.md')

diff --git a/doc/development/sql.md b/doc/development/sql.md
index 23fd7604957..974b1d99dff 100644
--- a/doc/development/sql.md
+++ b/doc/development/sql.md
@@ -216,4 +216,30 @@ exact same results. This also means there's no need to add an index on
 `created_at` to ensure consistent performance as `id` is already indexed by
 default.
 
+## Use WHERE EXISTS instead of WHERE IN
+
+While `WHERE IN` and `WHERE EXISTS` can be used to produce the same data it is
+recommended to use `WHERE EXISTS` whenever possible. While in many cases
+PostgreSQL can optimise `WHERE IN` quite well there are also many cases where
+`WHERE EXISTS` will perform (much) better.
+
+In Rails you have to use this by creating SQL fragments:
+
+```ruby
+Project.where('EXISTS (?)', User.select(1).where('projects.creator_id = users.id AND users.foo = X'))
+```
+
+This would then produce a query along the lines of the following:
+
+```sql
+SELECT *
+FROM projects
+WHERE EXISTS (
+    SELECT 1
+    FROM users
+    WHERE projects.creator_id = users.id
+    AND users.foo = X
+)
+```
+
 [gin-index]: http://www.postgresql.org/docs/current/static/gin.html
-- 
cgit v1.2.1