bengler/grove — pull request #65 by atombender (2014-08-18 21:17:34+00:00)
Title: Optimize path queries by use subquery on locations_posts instead of expensive query

For large sets, this reduces some queries from ~20 seconds down to a hundred milliseconds or so, and similarly shaves query memory usage.

### Old query

```
grove_production=# explain analyze
grove_production-# SELECT distinct posts.*
grove_production-# FROM "posts"
grove_production-# INNER JOIN "locations_posts" ON "locations_posts"."post_id" = "posts"."id"
grove_production-# INNER JOIN "locations" ON "locations"."id" = "locations_posts"."location_id"
grove_production-# WHERE "locations"."label_0" = 'endeavor'
grove_production-# AND "locations"."label_1" = 'enclosure'
grove_production-# AND "locations"."label_2" = 'bostonglobesandbox'
grove_production-# AND (klass in ('post.listing'))
grove_production-# AND "posts"."restricted" = 'f'
grove_production-# AND "posts"."deleted" = 'f'
grove_production-# AND "posts"."published" = 't'
grove_production-# AND (not deleted)
grove_production-# AND (published)
grove_production-# ORDER BY posts.created_at DESC
grove_production-# LIMIT 101;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=2029.49..2034.54 rows=101 width=1442) (actual time=15104.476..15110.523 rows=101 loops=1)
-> Unique (cost=2029.49..2080.09 rows=1012 width=1442) (actual time=15104.472..15110.452 rows=101 loops=1)
-> Sort (cost=2029.49..2032.02 rows=1012 width=1442) (actual time=15104.466..15104.633 rows=301 loops=1)
Sort Key: posts.created_at, posts.id, posts.document, posts.realm, posts.tags_vector, posts.created_by, posts.deleted, posts.updated_at, posts.external_id, posts.canonical_path, posts.restricted, posts.document_updated_at, posts.external_document_updated_at, posts.external_document, posts.conflicted, posts.published, posts.protected, posts.sensitive
Sort Method: quicksort Memory: 10941kB
-> Nested Loop (cost=120.20..1978.98 rows=1012 width=1442) (actual time=82.725..219.352 rows=7680 loops=1)
-> Hash Join (cost=119.91..1204.17 rows=1357 width=4) (actual time=82.604..114.778 rows=8119 loops=1)
Hash Cond: (locations_posts.location_id = locations.id)
-> Seq Scan on locations_posts (cost=0.00..887.50 rows=48850 width=8) (actual time=0.037..50.129 rows=48217 loops=1)
-> Hash (cost=118.95..118.95 rows=77 width=4) (actual time=0.971..0.971 rows=236 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 6kB
-> Index Scan using index_locations_on_labels on locations (cost=0.28..118.95 rows=77 width=4) (actual time=0.211..0.706 rows=236 loops=1)
Index Cond: ((label_0 = 'endeavor'::text) AND (label_1 = 'enclosure'::text) AND (label_2 = 'bostonglobesandbox'::text))
-> Index Scan using posts_pkey on posts (cost=0.29..0.56 rows=1 width=1442) (actual time=0.007..0.008 rows=1 loops=8119)
Index Cond: (id = locations_posts.post_id)
Filter: ((NOT restricted) AND (NOT deleted) AND published AND (NOT deleted) AND published AND (klass = 'post.listing'::text))
Rows Removed by Filter: 0
Total runtime: 15112.225 ms
(18 rows)
```

### New query

```
grove_production=# explain analyze
grove_production-# SELECT posts.id, posts.created_at
grove_production-# FROM "posts"
grove_production-# where
grove_production-# id in (
grove_production(# select post_id
grove_production(# from locations_posts
grove_production(# JOIN "locations" ON "locations"."id" = "locations_posts"."location_id"
grove_production(# WHERE "locations"."label_0" = 'endeavor'
grove_production(# AND "locations"."label_1" = 'enclosure'
grove_production(# AND "locations"."label_2" = 'bostonglobesandbox'
grove_production(# )
grove_production-# AND (klass in ('post.listing'))
grove_production-# AND "posts"."restricted" = 'f'
grove_production-# AND "posts"."deleted" = 'f'
grove_production-# AND "posts"."published" = 't'
grove_production-# AND (not deleted)
grove_production-# AND (published)
grove_production-# ORDER BY posts.created_at DESC
grove_production-# LIMIT 101;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=2034.69..2034.95 rows=101 width=12) (actual time=152.043..152.288 rows=101 loops=1)
-> Sort (cost=2034.69..2037.22 rows=1012 width=12) (actual time=152.039..152.133 rows=101 loops=1)
Sort Key: posts.created_at
Sort Method: top-N heapsort Memory: 20kB
-> Nested Loop (cost=1207.85..1995.94 rows=1012 width=12) (actual time=114.067..148.571 rows=2560 loops=1)
-> HashAggregate (cost=1207.56..1221.13 rows=1357 width=4) (actual time=114.013..117.177 rows=2797 loops=1)
-> Hash Join (cost=119.91..1204.17 rows=1357 width=4) (actual time=79.292..104.440 rows=8119 loops=1)
Hash Cond: (locations_posts.location_id = locations.id)
-> Seq Scan on locations_posts (cost=0.00..887.50 rows=48850 width=8) (actual time=0.016..45.851 rows=48217 loops=1)
-> Hash (cost=118.95..118.95 rows=77 width=4) (actual time=1.034..1.034 rows=236 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 6kB
-> Index Scan using index_locations_on_labels on locations (cost=0.28..118.95 rows=77 width=4) (actual time=0.213..0.761 rows=236 loops=1)
Index Cond: ((label_0 = 'endeavor'::text) AND (label_1 = 'enclosure'::text) AND (label_2 = 'bostonglobesandbox'::text))
-> Index Scan using posts_pkey on posts (cost=0.29..0.56 rows=1 width=12) (actual time=0.006..0.007 rows=1 loops=2797)
Index Cond: (id = locations_posts.post_id)
Filter: ((NOT restricted) AND (NOT deleted) AND published AND (NOT deleted) AND published AND (klass = 'post.listing'::text))
Rows Removed by Filter: 0
Total runtime: 152.506 ms
(18 rows)
```

---

Note: I don't quite understand `Pebbles::Conditions#labelize`, and my `in` logic may not be accounting for `nil` values in arrays. However, the tests pass.