Excessive PostgreSQL tempfile usage

[erikgrinaker]

Grove has excessive tempfile usage on staging, which bogs down IO for the entire cluster (as it's on VMs with a shared IO subsystem). Prod uses 256MB work_mem, so it doesn't trigger, but it's only a matter of time until this happens there as well.

Steps to reproduce locally:

$ postgres --version
postgres (PostgreSQL) 9.2.3

$ grep -E '(work_mem|log_temp_files)' /usr/local/var/postgres/postgresql.conf 
work_mem = 64MB
log_temp_files = 0

$ postgres -D /usr/local/var/postgres/
$ createuser -s grove
$ createdb -O grove grove_development
$ ssh hemnes.park.origo.no pg_dump -F c grove_staging | pg_restore -F c -d grove_development

$ psql grove_development
grove_development=# \timing
Timing is on.
grove_development=# VACUUM FULL;
VACUUM
Time: 16538.177 ms
grove_development=# ANALYZE;
ANALYZE
Time: 713.890 ms
grove_development=# SELECT distinct posts.* FROM "posts" INNER JOIN "locations_posts" ON "locations_posts"."post_id" = "posts"."id" INNER JOIN "locations" ON "locations"."id" = "locations_posts"."location_id" left outer join group_locations on group_locations.location_id = locations.id left outer join group_memberships on group_memberships.group_id = group_locations.group_id and group_memberships.identity_id = 2751066 WHERE "locations"."label_0" = 'apdm' AND "locations"."label_1" = 'oa' AND "locations"."label_2" = 'response' AND "locations"."label_3" = 'imagestream' AND "locations"."label_4" = '667' AND "locations"."label_5" = '442362' AND "locations"."label_6" IS NULL AND (not deleted) AND (klass = 'post.stream_image') AND (not restricted or created_by = 2751066 or group_memberships.identity_id = 2751066) ORDER BY posts.created_at DESC LIMIT 21 OFFSET 0;
Time: 891.326 ms

The PostgreSQL log says:

LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp16524.0", size 123625472
STATEMENT:  SELECT distinct posts.* FROM "posts" INNER JOIN "locations_posts" ON "locations_posts"."post_id" = "posts"."id" INNER JOIN "locations" ON "locations"."id" = "locations_posts"."location_id" left outer join group_locations on group_locations.location_id = locations.id left outer join group_memberships on group_memberships.group_id = group_locations.group_id and group_memberships.identity_id = 2751066 WHERE "locations"."label_0" = 'apdm' AND "locations"."label_1" = 'oa' AND "locations"."label_2" = 'response' AND "locations"."label_3" = 'imagestream' AND "locations"."label_4" = '667' AND "locations"."label_5" = '442362' AND "locations"."label_6" IS NULL AND (not deleted) AND (klass = 'post.stream_image') AND (not restricted or created_by = 2751066 or group_memberships.identity_id = 2751066) ORDER BY posts.created_at DESC LIMIT 21 OFFSET 0;