slow-query.sql

https://news.ycombinator.com/reply?id=13613909&goto=item%3Fid%3D13610146%2313613909

---

smc=# explain SELECT * FROM file_use WHERE project_id = any(select project_id from projects where users ? '25e2cae4-05c7-4c28-ae22-1e6d3d2e8bb3') ORDER
 BY last_edited DESC limit 100;
                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.85..8198.07 rows=100 width=242)
   ->  Nested Loop Semi Join  (cost=0.85..1736663.69 rows=21186 width=242)
         ->  Index Scan Backward using file_use_last_edited_idx on file_use  (cost=0.43..462869.27 rows=2214686 width=242)
         ->  Index Scan using projects_pkey on projects  (cost=0.42..0.58 rows=1 width=16)
               Index Cond: (project_id = file_use.project_id)
               Filter: (users ? '25e2cae4-05c7-4c28-ae22-1e6d3d2e8bb3'::text)
(6 rows)

smc=# \timing
Timing is on.
smc=# SELECT * FROM file_use WHERE project_id = any(select project_id from projects where users ? '25e2cae4-05c7-4c28-ae22-1e6d3d2e8bb4') ORDER BY last
_edited DESC limit 100;
 id | project_id | path | users | last_edited
----+------------+------+-------+-------------
(0 rows)
 
Time: 11695.095 ms

smc=# BEGIN;
BEGIN
Time: 0.124 ms
smc=# SET LOCAL enable_nestloop = off;
SET
Time: 0.172 ms
smc=# SELECT * FROM file_use WHERE project_id = any(select project_id from projects where users ? '25e2cae4-05c7-4c28-ae22-1e6d3d2e8bb4') ORDER BY last
_edited DESC limit 100;
 id | project_id | path | users | last_edited
----+------------+------+-------+-------------
(0 rows)
 
Time: 1.479 ms
smc=# commit;
COMMIT
Time: 0.166 ms

smc=# \d file_use
                Table "public.file_use"
   Column    |            Type             | Modifiers
-------------+-----------------------------+-----------
 id          | character(40)               | not null
 project_id  | uuid                        |
 path        | text                        |
 users       | jsonb                       |
 last_edited | timestamp without time zone |
Indexes:
    "file_use_pkey" PRIMARY KEY, btree (id)
    "file_use_last_edited_idx" btree (last_edited)
    "file_use_project_id_idx" btree (project_id)
Triggers:
    change_12294c1fc89b482b AFTER INSERT OR DELETE OR UPDATE OF project_id, path, users, last_edited, id ON file_use FOR EACH ROW EXECUTE PROCEDURE change_12294c1fc89b482b()
 
smc=# \d projects
                  Table "public.projects"
     Column      |            Type             | Modifiers
-----------------+-----------------------------+-----------
 project_id      | uuid                        | not null
 title           | text                        |
 description     | text                        |
 users           | jsonb                       |
 invite          | jsonb                       |
 invite_requests | jsonb                       |
 deleted         | boolean                     |
 host            | jsonb                       |
 settings        | jsonb                       |
 status          | jsonb                       |
 state           | jsonb                       |
 last_edited     | timestamp without time zone |
 last_active     | jsonb                       |
 created         | timestamp without time zone |
 action_request  | jsonb                       |
 storage         | jsonb                       |
 last_backup     | timestamp without time zone |
 storage_request | jsonb                       |
 course          | jsonb                       |
 run             | boolean                     |
 storage_server  | integer                     |
 storage_ready   | boolean                     |
 disk_size       | integer                     |
 resources       | jsonb                       |
 preemptible     | boolean                     |
 idle_timeout    | integer                     |
Indexes:
    "projects_pkey" PRIMARY KEY, btree (project_id)

I posted a response here.