Skip to content

Instantly share code, notes, and snippets.

@williamstein
Last active February 10, 2017 21:53
Show Gist options
  • Save williamstein/fb31e07d4057232bd3a3e780d6220482 to your computer and use it in GitHub Desktop.
Save williamstein/fb31e07d4057232bd3a3e780d6220482 to your computer and use it in GitHub Desktop.
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)
@mbanck
Copy link

mbanck commented Feb 10, 2017

Interesting. Can you maybe also (i) show the EXPLAIN for the enable_nestloop = off case, and (ii) EXPLAIN (ANALYZE, BUFFERS) for both queries?

@malisper
Copy link

I posted a response here.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment