"Mason Hale" <masonhale@gmail.com> writes:
>> What's the actual number of groups
>> (target_page_id values)?
> Approximately 40 million (I'll have a more precise number when the query
> finishes running ).
Ouch. The discrepancy between that and the 550K estimate is what's
killing you --- the hash table will be 80 times bigger than the planner
thinks. You sure that increasing the stats target doesn't improve
matters? (I suppose the distribution is one of these "long tail"
affairs...)
The best solution might be to reduce work_mem --- 40MB is fairly high
for a general-purpose setting anyway IMO, since you need to worry about
complex queries eating multiples of work_mem.
regards, tom lane