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
permissions schema
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)
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
Cache size: 3.5 GB
Storage limit: 256 GB
Connection limit: 400
work_mem: 30MB
checkpoint_segments: 40
wal_buffers: 16MB