Skip to content

Instantly share code, notes, and snippets.

@patmaddox

patmaddox/1_tasks.txt Secret

Last active March 8, 2017 02:20
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save patmaddox/c599dc26daa99a12c1923c4994e402df to your computer and use it in GitHub Desktop.
Save patmaddox/c599dc26daa99a12c1923c4994e402df to your computer and use it in GitHub Desktop.
Download ZIP
Raw
1_tasks.txt
Table "public.tasks"
Column | Type | Modifiers
------------------------------+-----------------------------+----------------------------------------------------
id | integer | not null default nextval('tasks_id_seq'::regclass)
task_id | integer |
completed_by_user_id | integer |
assigned_to_user_id | integer |
complete | boolean | default false
completed_at | timestamp without time zone |
created_at | timestamp without time zone | not null
updated_at | timestamp without time zone | not null
due_on | date |
note | text |
externally_visible | boolean |
taskable_id | integer |
taskable_type | character varying(255) |
recurring | boolean | default false
recurring_day_of_week | integer |
recurring_every_so_many_days | integer |
archived | boolean |
original_task_id | integer |
name | character varying(255) |
recurring_frequency | character varying(255) |
account_id | integer |
position | integer |
reminder | boolean | default false
instructions | text |
taskable_name | character varying(255) |
high_priority | boolean | default false
expiration_type | integer | default 0
expiration_date | date |
expiration_rule | jsonb | default '{}'::jsonb
contingent | boolean | default false
contingency_rule | jsonb | default '{}'::jsonb
number_of_occurrences | integer |
Indexes:
"tasks_pkey" PRIMARY KEY, btree (id)
"index_tasks_on_account_id" btree (account_id)
"index_tasks_on_account_id_and_assigned_to_user_id" btree (account_id, assigned_to_user_id)
"index_tasks_on_assigned_to_user_id" btree (assigned_to_user_id)
"index_tasks_on_completed_at" btree (completed_at)
"index_tasks_on_completed_by_user_id" btree (completed_by_user_id)
"index_tasks_on_due_on" btree (due_on)
"index_tasks_on_position" btree ("position")
"index_tasks_on_taskable_id_and_taskable_type" btree (taskable_id, taskable_type)
"index_tasks_on_taskable_name" btree (taskable_name)
"index_tasks_on_taskable_type_and_taskable_id" btree (taskable_type, taskable_id)
Raw
2_permissions.txt
Table "public.permissions"
Column | Type | Modifiers
-------------+-------------------+----------------------------------------------------------
id | integer | not null default nextval('permissions_id_seq'::regclass)
user_id | integer |
object_type | character varying |
object_id | integer |
write | boolean |
Indexes:
"permissions_pkey" PRIMARY KEY, btree (id)
"index_permissions_on_object_id_and_object_type" btree (object_id, object_type)
"index_permissions_on_user_id" btree (user_id)
"index_permissions_on_user_id_and_object_id_and_object_type" btree (user_id, object_id, object_type)
Raw
3_query.sql
EXPLAIN (ANALYZE, BUFFERS) SELECT DISTINCT "tasks".* FROM "tasks"
INNER JOIN permissions ON permissions.object_id = tasks.taskable_id AND permissions.object_type = tasks.taskable_type
WHERE "permissions"."user_id" = 39
AND ("tasks"."assigned_to_user_id" IN (16916, 2145, 72155, 60, 65206, 88131, 29278, 72928, 50098, 32120, 63533, 37295, 10905, 50728, 117, 1873, 63235, 1338, 10087, 92522, 83569, 82753, 80877, 39, 78597, 89393, 52056, 86880, 90127, 73665, 2651, 92955, 24719, 54810, 54521, 76770, 64479, 70, 31998, 47714, 591, 55382, 60868, 81439, 79469, 10284, 38190, 52558, 52314, 6359, 1146, 94580, 54496, 1406, 69675, 87660, 9578, 147, 68882, 157, 52785, 176, 50, 83053, 37191, 52972, 65160, 149, 79463, 1279, 59, 4911, 1602, 24455, 66230, 67, 73906, 50729, 1333, 54829, 41474, 47, 53526, 93404, 18643, 78804, 72134, 94608, 63, 46, 24457, 55071, 12966, 20983, 2348, 33001, 37544, 59140, 67674, 44, 40, 68, 73642, 2861, 52617, 1655, 628, 94741, 7589, 592, 69, 1517, 92711, 95912, 179, 94409, 15155, 83223, 26261, 77561,
633, 58, 52221, 501, 2030, 65, 2151, 1256, 168, 17658, 84445, 84479, 8294, 51781, 66325, 71114, 76792, 41, 50731, 80726, 1264, 165, 2020, 71203, 178, 83017, 55736, 1900, 87441, 76111, 1874, 1340, 50732, 52950, 153, 50730, 94615, 45, 5398, 92529, 42, 95861, 49362, 45258, 6021, 2346, 132, 90436, 56534, 89858, 64828, 27707, 54513, 54812, 73907, 50689, 73629, 93409, 76123, 38454, 50867, 44299, 61, 75032, 7001, 62, 86583, 2190, 78648, 36745, 69066, 50228, 56161, 10580, 8722, 96121, 29423, 30725, 2551, 118, 1769, 664, 57, 69296, 78698, 18255, 31635, 63103, 4136, 64, 89944, 73586, 15864, 75327, 45172, 42472)
OR "tasks"."assigned_to_user_id" IS NULL)
AND ("tasks"."archived" != 't')
AND "tasks"."complete" = 't'
AND "tasks"."account_id" = 16
ORDER BY completed_at desc, taskable_name, position
LIMIT 50 OFFSET 0;
Raw
4_explain.txt
Limit (cost=177355.13..177355.96 rows=50 width=259) (actual time=7068.129..7068.298 rows=50 loops=1)
Buffers: shared hit=13499 read=89584, temp read=10713 written=17288
I/O Timings: read=438.279
-> Unique (cost=177355.13..177385.58 rows=1845 width=259) (actual time=7068.128..7068.293 rows=50 loops=1)
Buffers: shared hit=13499 read=89584, temp read=10713 written=17288
I/O Timings: read=438.279
-> Sort (cost=177355.13..177356.06 rows=1845 width=259) (actual time=7068.124..7068.140 rows=52 loops=1)
Sort Key: tasks.completed_at, tasks.taskable_name, tasks."position", tasks.id, tasks.task_id, tasks.completed_by_user_id, tasks.assigned_to_user_id, tasks.complete, tasks.created_at, tasks.updated_at, tasks.due_on, tasks.note, tasks.externally_visible, tasks.taskable_id, tasks.taskable_type, tasks.recurring, tasks.recurring_day_of_week, tasks.recurring_every_so_many_days, tasks.archived, tasks.original_task_id, tasks.name, tasks.recurring_frequency, tasks.reminder, tasks.instructions, tasks.high_priority, tasks.expiration_type, tasks.expiration_date, tasks.expiration_rule, tasks.contingent, tasks.contingency_rule, tasks.number_of_occurrences
Sort Method: external merge Disk: 69352kB
Buffers: shared hit=13499 read=89584, temp read=10713 written=17288
I/O Timings: read=438.279
-> Merge Join (cost=170638.12..177335.12 rows=1845 width=259) (actual time=4090.065..6025.936 rows=349084 loops=1)
Merge Cond: ((tasks.taskable_id = permissions.object_id) AND ((tasks.taskable_type)::text = (permissions.object_type)::text))
Buffers: shared hit=13483 read=89584, temp read=8613 written=8613
I/O Timings: read=438.279
-> Sort (cost=170638.03..170677.65 rows=79245 width=259) (actual time=4089.948..4915.558 rows=347014 loops=1)
Sort Key: tasks.taskable_id, tasks.taskable_type
Sort Method: external merge Disk: 68880kB
Buffers: shared hit=4627 read=87647, temp read=8613 written=8613
I/O Timings: read=419.092
-> Bitmap Heap Scan on tasks (cost=1521.24..169348.39 rows=79245 width=259) (actual time=85.627..1102.414 rows=347014 loops=1)
Recheck Cond: (((account_id = 16) AND (assigned_to_user_id = ANY ('{16916,2145,72155,60,65206,88131,29278,72928,50098,32120,63533,37295,10905,50728,117,1873,63235,1338,10087,92522,83569,82753,80877,39,78597,89393,52056,86880,90127,73665,2651,92955,24719,54810,54521,76770,64479,70,31998,47714,591,55382,60868,81439,79469,10284,38190,52558,52314,6359,1146,94580,54496,1406,69675,87660,9578,147,68882,157,52785,176,50,83053,37191,52972,65160,149,79463,1279,59,4911,1602,24455,66230,67,73906,50729,1333,54829,41474,47,53526,93404,18643,78804,72134,94608,63,46,24457,55071,12966,20983,2348,33001,37544,59140,67674,44,40,68,73642,2861,52617,1655,628,94741,7589,592,69,1517,92711,95912,179,94409,15155,83223,26261,77561,633,58,52221,501,2030,65,2151,1256,168,17658,84445,84479,8294,51781,66325,71114,76792,41,50731,80726,1264,165,2020,71203,178,83017,55736,1900,87441,76111,1874,1340,50732,52950,153,50730,94615,45,5398,92529,42,95861,49362,45258,6021,2346,132,90436,56534,89858,64828,27707,54513,54812,73907,50689,73629,93409,76123,38454,50867,44299,61,75032,7001,62,86583,2190,78648,36745,69066,50228,56161,10580,8722,96121,29423,30725,2551,118,1769,664,57,69296,78698,18255,31635,63103,4136,64,89944,73586,15864,75327,45172,42472}'::integer[]))) OR ((account_id = 16) AND (assigned_to_user_id IS NULL)))
Filter: ((NOT archived) AND complete)
Rows Removed by Filter: 7236
Heap Blocks: exact=90450
Buffers: shared hit=4626 read=87647
I/O Timings: read=419.092
-> BitmapOr (cost=1521.24..1521.24 rows=135110 width=0) (actual time=61.592..61.592 rows=0 loops=1)
Buffers: shared hit=646 read=1177
I/O Timings: read=7.496
-> Bitmap Index Scan on index_tasks_on_account_id_and_assigned_to_user_id (cost=0.00..505.28 rows=27134 width=0) (actual time=60.253..60.253 rows=360098 loops=1)
Index Cond: ((account_id = 16) AND (assigned_to_user_id = ANY ('{16916,2145,72155,60,65206,88131,29278,72928,50098,32120,63533,37295,10905,50728,117,1873,63235,1338,10087,92522,83569,82753,80877,39,78597,89393,52056,86880,90127,73665,2651,92955,24719,54810,54521,76770,64479,70,31998,47714,591,55382,60868,81439,79469,10284,38190,52558,52314,6359,1146,94580,54496,1406,69675,87660,9578,147,68882,157,52785,176,50,83053,37191,52972,65160,149,79463,1279,59,4911,1602,24455,66230,67,73906,50729,1333,54829,41474,47,53526,93404,18643,78804,72134,94608,63,46,24457,55071,12966,20983,2348,33001,37544,59140,67674,44,40,68,73642,2861,52617,1655,628,94741,7589,592,69,1517,92711,95912,179,94409,15155,83223,26261,77561,633,58,52221,501,2030,65,2151,1256,168,17658,84445,84479,8294,51781,66325,71114,76792,41,50731,80726,1264,165,2020,71203,178,83017,55736,1900,87441,76111,1874,1340,50732,52950,153,50730,94615,45,5398,92529,42,95861,49362,45258,6021,2346,132,90436,56534,89858,64828,27707,54513,54812,73907,50689,73629,93409,76123,38454,50867,44299,61,75032,7001,62,86583,2190,78648,36745,69066,50228,56161,10580,8722,96121,29423,30725,2551,118,1769,664,57,69296,78698,18255,31635,63103,4136,64,89944,73586,15864,75327,45172,42472}'::integer[])))
Buffers: shared hit=643 read=1146
I/O Timings: read=7.342
-> Bitmap Index Scan on index_tasks_on_account_id_and_assigned_to_user_id (cost=0.00..1008.04 rows=107976 width=0) (actual time=1.336..1.336 rows=7633 loops=1)
Index Cond: ((account_id = 16) AND (assigned_to_user_id IS NULL))
Buffers: shared hit=3 read=31
I/O Timings: read=0.154
-> Index Only Scan using index_permissions_on_user_id_and_object_id_and_object_type on permissions (cost=0.09..9055.41 rows=8245 width=11) (actual time=0.073..205.122 rows=352618 loops=1)
Index Cond: (user_id = 39)
Heap Fetches: 351361
Buffers: shared hit=8856 read=1937
I/O Timings: read=19.187
Planning time: 1.769 ms
Execution time: 7104.229 ms
(45 rows)
Raw
5_explain_mem.txt
Limit (cost=177355.13..177355.96 rows=50 width=259) (actual time=2846.102..2846.213 rows=50 loops=1)
Buffers: shared hit=9954 read=93128 dirtied=3 written=148
I/O Timings: read=516.349 write=1.561
-> Unique (cost=177355.13..177385.58 rows=1845 width=259) (actual time=2846.101..2846.207 rows=50 loops=1)
Buffers: shared hit=9954 read=93128 dirtied=3 written=148
I/O Timings: read=516.349 write=1.561
-> Sort (cost=177355.13..177356.06 rows=1845 width=259) (actual time=2846.099..2846.108 rows=52 loops=1)
Sort Key: tasks.completed_at, tasks.taskable_name, tasks."position", tasks.id, tasks.task_id, tasks.completed_by_user_id, tasks.assigned_to_user_id, tasks.complete, tasks.created_at, tasks.updated_at, tasks.due_on, tasks.note, tasks.externally_visible, tasks.taskable_id, tasks.taskable_type, tasks.recurring, tasks.recurring_day_of_week, tasks.recurring_every_so_many_days, tasks.archived, tasks.original_task_id, tasks.name, tasks.recurring_frequency, tasks.reminder, tasks.instructions, tasks.high_priority, tasks.expiration_type, tasks.expiration_date, tasks.expiration_rule, tasks.contingent, tasks.contingency_rule, tasks.number_of_occurrences
Sort Method: quicksort Memory: 123626kB
Buffers: shared hit=9954 read=93128 dirtied=3 written=148
I/O Timings: read=516.349 write=1.561
-> Merge Join (cost=170638.12..177335.12 rows=1845 width=259) (actual time=1674.413..2398.581 rows=349084 loops=1)
Merge Cond: ((tasks.taskable_id = permissions.object_id) AND ((tasks.taskable_type)::text = (permissions.object_type)::text))
Buffers: shared hit=9938 read=93128 dirtied=3 written=148
I/O Timings: read=516.349 write=1.561
-> Sort (cost=170638.03..170677.65 rows=79245 width=259) (actual time=1674.321..1738.841 rows=347014 loops=1)
Sort Key: tasks.taskable_id, tasks.taskable_type
Sort Method: quicksort Memory: 122873kB
Buffers: shared hit=1638 read=90635 dirtied=3 written=148
I/O Timings: read=493.684 write=1.561
-> Bitmap Heap Scan on tasks (cost=1521.24..169348.39 rows=79245 width=259) (actual time=80.957..1026.227 rows=347014 loops=1)
Recheck Cond: (((account_id = 16) AND (assigned_to_user_id = ANY ('{16916,2145,72155,60,65206,88131,29278,72928,50098,32120,63533,37295,10905,50728,117,1873,63235,1338,10087,92522,83569,82753,80877,39,78597,89393,52056,86880,90127,73665,2651,92955,24719,54810,54521,76770,64479,70,31998,47714,591,55382,60868,81439,79469,10284,38190,52558,52314,6359,1146,94580,54496,1406,69675,87660,9578,147,68882,157,52785,176,50,83053,37191,52972,65160,149,79463,1279,59,4911,1602,24455,66230,67,73906,50729,1333,54829,41474,47,53526,93404,18643,78804,72134,94608,63,46,24457,55071,12966,20983,2348,33001,37544,59140,67674,44,40,68,73642,2861,52617,1655,628,94741,7589,592,69,1517,92711,95912,179,94409,15155,83223,26261,77561,633,58,52221,501,2030,65,2151,1256,168,17658,84445,84479,8294,51781,66325,71114,76792,41,50731,80726,1264,165,2020,71203,178,83017,55736,1900,87441,76111,1874,1340,50732,52950,153,50730,94615,45,5398,92529,42,95861,49362,45258,6021,2346,132,90436,56534,89858,64828,27707,54513,54812,73907,50689,73629,93409,76123,38454,50867,44299,61,75032,7001,62,86583,2190,78648,36745,69066,50228,56161,10580,8722,96121,29423,30725,2551,118,1769,664,57,69296,78698,18255,31635,63103,4136,64,89944,73586,15864,75327,45172,42472}'::integer[]))) OR ((account_id = 16) AND (assigned_to_user_id IS NULL)))
Filter: ((NOT archived) AND complete)
Rows Removed by Filter: 7236
Heap Blocks: exact=90450
Buffers: shared hit=1638 read=90635 dirtied=3 written=148
I/O Timings: read=493.684 write=1.561
-> BitmapOr (cost=1521.24..1521.24 rows=135110 width=0) (actual time=56.252..56.252 rows=0 loops=1)
Buffers: shared hit=667 read=1156
I/O Timings: read=4.448
-> Bitmap Index Scan on index_tasks_on_account_id_and_assigned_to_user_id (cost=0.00..505.28 rows=27134 width=0) (actual time=55.001..55.001 rows=360098 loops=1)
Index Cond: ((account_id = 16) AND (assigned_to_user_id = ANY ('{16916,2145,72155,60,65206,88131,29278,72928,50098,32120,63533,37295,10905,50728,117,1873,63235,1338,10087,92522,83569,82753,80877,39,78597,89393,52056,86880,90127,73665,2651,92955,24719,54810,54521,76770,64479,70,31998,47714,591,55382,60868,81439,79469,10284,38190,52558,52314,6359,1146,94580,54496,1406,69675,87660,9578,147,68882,157,52785,176,50,83053,37191,52972,65160,149,79463,1279,59,4911,1602,24455,66230,67,73906,50729,1333,54829,41474,47,53526,93404,18643,78804,72134,94608,63,46,24457,55071,12966,20983,2348,33001,37544,59140,67674,44,40,68,73642,2861,52617,1655,628,94741,7589,592,69,1517,92711,95912,179,94409,15155,83223,26261,77561,633,58,52221,501,2030,65,2151,1256,168,17658,84445,84479,8294,51781,66325,71114,76792,41,50731,80726,1264,165,2020,71203,178,83017,55736,1900,87441,76111,1874,1340,50732,52950,153,50730,94615,45,5398,92529,42,95861,49362,45258,6021,2346,132,90436,56534,89858,64828,27707,54513,54812,73907,50689,73629,93409,76123,38454,50867,44299,61,75032,7001,62,86583,2190,78648,36745,69066,50228,56161,10580,8722,96121,29423,30725,2551,118,1769,664,57,69296,78698,18255,31635,63103,4136,64,89944,73586,15864,75327,45172,42472}'::integer[])))
Buffers: shared hit=664 read=1125
I/O Timings: read=4.339
-> Bitmap Index Scan on index_tasks_on_account_id_and_assigned_to_user_id (cost=0.00..1008.04 rows=107976 width=0) (actual time=1.249..1.249 rows=7633 loops=1)
Index Cond: ((account_id = 16) AND (assigned_to_user_id IS NULL))
Buffers: shared hit=3 read=31
I/O Timings: read=0.109
-> Index Only Scan using index_permissions_on_user_id_and_object_id_and_object_type on permissions (cost=0.09..9055.41 rows=8245 width=11) (actual time=0.066..136.272 rows=352618 loops=1)
Index Cond: (user_id = 39)
Heap Fetches: 351361
Buffers: shared hit=8300 read=2493
I/O Timings: read=22.665
Planning time: 1.847 ms
Execution time: 2863.099 ms
(45 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment