Please help with a slow query: there are millions of records, what can we do?

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