norman/friendly_id — pull request #497 by mhodgson (2013-12-09 22:33:07+00:00)
Title: Performance improvement for finding if a slug is already used or not

This commit drastically improves the performance of this lookup on non-trivial sized tables. The original query too the following for (for a table named 'titles' with a slugged field called 'permalink'):

```SQL
SELECT 1 AS one FROM "titles"
INNER JOIN "friendly_id_slugs"
ON "friendly_id_slugs"."sluggable_id" = "titles"."id"
AND "friendly_id_slugs"."sluggable_type" = 'Title'
WHERE (
"titles"."permalink" = 'biology' OR ("friendly_id_slugs"."sluggable_type" = 'Title' AND "friendly_id_slugs"."slug" = 'biology')
) LIMIT 1;
```
When explained, you can see that this query is doing TWO sequential scans, one on the 'titles' table and one on the 'friendly_id_slugs' table:

```SQL
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=39606.33..67567.51 rows=1 width=0)
-> Hash Join (cost=39606.33..123489.87 rows=3 width=0)
Hash Cond: (friendly_id_slugs.sluggable_id = titles.id)
Join Filter: ((titles.permalink = 'biology'::text) OR (((friendly_id_slugs.sluggable_type)::text = 'Title'::text) AND (friendly_id_slugs.slug = 'biology'::text)))
-> Seq Scan on friendly_id_slugs (cost=0.00..31217.93 rows=859256 width=76)
Filter: ((sluggable_type)::text = 'Title'::text)
-> Hash (cost=27845.48..27845.48 rows=449348 width=87)
-> Seq Scan on titles (cost=0.00..27845.48 rows=449348 width=87)
```

This is really bad. For example, our friendly_id_slugs table has 900k records and our titles table has 500k records. This query takes over 4 SECONDS.

This query *should* be able to take advantage of the indexes on both the friendly_id_slugs table and the titles table (which has an index on permalink). Unfortunately the Postgres query optimizer is not taking advantage of the indexes on either table.

The attached commit simply splits this method into two simpler queries that can take advantage of the indexes. On the example above this amounts to a performance improvement of over 1000x (3ms vs 4028ms).

It is possible that this refactor will actually decrease performance in apps with very small amounts of data, but anyone with a significant amount of data should see serious performance improvements.