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 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 |
