Hi there, I’ve been asked to help with a project dealing with slow queries. I’m brand new to the project, so I have very little context. I’ve gathered as much information as I can.
I’ve put the schema, query, and explain info in gists to maintain their formatting. We are stumped with this slow query right now. I could really use some help looking for ways to speed it up. If you need any more information, please let me know. Thanks, Pat Full Table and Index Schema tasks schema <https://gist.github.com/patmaddox/c599dc26daa99a12c1923c4994e402df#file-1_tasks-txt> permissions schema <https://gist.github.com/patmaddox/c599dc26daa99a12c1923c4994e402df#file-2_permissions-txt> Table Metadata tasks count: 8.8 million tasks count where assigned_to_user_id is null: 2.7 million tasks table has lots of new records added, individual existing records updated (e.g. to mark them complete) permissions count: 4.4 million EXPLAIN (ANALYZE, BUFFERS) query <https://gist.github.com/patmaddox/c599dc26daa99a12c1923c4994e402df#file-3_query-sql> explain using Heroku default work_mem=30MB: <https://gist.github.com/patmaddox/c599dc26daa99a12c1923c4994e402df#file-4_explain-txt> explain using work_mem=192MB <https://gist.github.com/patmaddox/c599dc26daa99a12c1923c4994e402df#file-5_explain_mem-txt> Postgres version PostgreSQL 9.4.9 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit History Slow query has gotten steadily worse over the past few months. Hardware / Maintenance Setup / WAL Configuration / GUC Settings Heroku Premium 2 plan <https://devcenter.heroku.com/articles/heroku-postgres-plans#premium-tier> Cache size: 3.5 GB Storage limit: 256 GB Connection limit: 400 work_mem: 30MB checkpoint_segments: 40 wal_buffers: 16MB
