You're probably right, I'm an amateur at this. My current solution works pretty well, but I should really drill into what may be wrong here.
If you don't mind me asking, what in your estimation would be an ideal query plan for a join of the type I've described? A hash join? Also, I've left statistics to their defaults and run ANALYZE after bulk data uploads (the only time data is written), but I'll try bumping statistics collection up and running VACUUM ANALYZE again. I don't know what server configuration could be messed up to cause something like this; I have my memory settings (shared_buffers, effective_cache_size) set up fine, and cost parameters have been left alone. Other configuration settings I've changed shouldn't be affecting read queries.
If you don't mind me asking, what in your estimation would be an ideal query plan for a join of the type I've described? A hash join? Also, I've left statistics to their defaults and run ANALYZE after bulk data uploads (the only time data is written), but I'll try bumping statistics collection up and running VACUUM ANALYZE again. I don't know what server configuration could be messed up to cause something like this; I have my memory settings (shared_buffers, effective_cache_size) set up fine, and cost parameters have been left alone. Other configuration settings I've changed shouldn't be affecting read queries.