Skip to content

Instantly share code, notes, and snippets.

@jstanley0

jstanley0/api search query analysis

Created August 23, 2013 19:53
Show Gist options
  • Save jstanley0/6323307 to your computer and use it in GitHub Desktop.
Save jstanley0/6323307 to your computer and use it in GitHub Desktop.
Download ZIP
explain analyze for api searches
Raw
api search query analysis
discussion topics
matched term
beta7.cluster7=> SELECT * FROM "discussion_topics" WHERE ((((LOWER(discussion_topics.title) LIKE '%pwn%')) AND ("discussion_topics"."type" IS NULL)) AND ((((LOWER(discussion_topics.title) LIKE '%pwn%')) AND ("discussion_topics"."type" IS NULL)) AND ("discussion_topics".context_id = 782370 AND "discussion_topics".context_type = 'Course' AND (discussion_topics.workflow_state != 'deleted')))) ORDER BY discussion_topics.position DESC, discussion_topics.created_at DESC, discussion_topics.position DESC, discussion_topics.created_at DESC LIMIT 10 OFFSET 0
beta7.cluster7-> ;
beta7.cluster7=>
beta7.cluster7=>
beta7.cluster7=> explain analyze SELECT * FROM "discussion_topics" WHERE ((((LOWER(discussion_topics.title) LIKE '%pwn%')) AND ("discussion_topics"."type" IS NULL)) AND ((((LOWER(discussion_topics.title) LIKE '%pwn%')) AND ("discussion_topics"."type" IS NULL)) AND ("discussion_topics".context_id = 782370 AND "discussion_topics".context_type = 'Course' AND (discussion_topics.workflow_state != 'deleted')))) ORDER BY discussion_topics.position DESC, discussion_topics.created_at DESC, discussion_topics.position DESC, discussion_topics.created_at DESC LIMIT 10 OFFSET 0;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=9.46..9.46 rows=1 width=770) (actual time=270.069..270.072 rows=1 loops=1)
-> Sort (cost=9.46..9.46 rows=1 width=770) (actual time=270.065..270.066 rows=1 loops=1)
Sort Key: "position", created_at
Sort Method: quicksort Memory: 25kB
-> Index Scan using index_trgm_discussion_topics_title on discussion_topics (cost=0.01..9.45 rows=1 width=770) (actual time=67.729..270.046 rows=1 loops=1)
Index Cond: ((lower((title)::text) ~~ '%pwn%'::text) AND (lower((title)::text) ~~ '%pwn%'::text))
Filter: ((type IS NULL) AND (type IS NULL) AND ((workflow_state)::text <> 'deleted'::text) AND (context_id = 782370) AND ((context_type)::text = 'Course'::text))
Total runtime: 270.151 ms
(8 rows)
unmatched term
beta7.cluster7=> explain analyze SELECT * FROM "discussion_topics" WHERE ((((LOWER(discussion_topics.title) LIKE '%poon%')) AND ("discussion_topics"."type" IS NULL)) AND ((((LOWER(discussion_topics.title) LIKE '%poon%')) AND ("discussion_topics"."type" IS NULL)) AND ("discussion_topics".context_id = 782370 AND "discussion_topics".context_type = 'Course' AND (discussion_topics.workflow_state != 'deleted')))) ORDER BY discussion_topics.position DESC, discussion_topics.created_at DESC, discussion_topics.position DESC, discussion_topics.created_at DESC LIMIT 10 OFFSET 0 ;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=9.46..9.46 rows=1 width=770) (actual time=235.853..235.853 rows=0 loops=1)
-> Sort (cost=9.46..9.46 rows=1 width=770) (actual time=235.850..235.850 rows=0 loops=1)
Sort Key: "position", created_at
Sort Method: quicksort Memory: 25kB
-> Index Scan using index_trgm_discussion_topics_title on discussion_topics (cost=0.01..9.45 rows=1 width=770) (actual time=235.840..235.840 rows=0 loops=1)
Index Cond: ((lower((title)::text) ~~ '%poon%'::text) AND (lower((title)::text) ~~ '%poon%'::text))
Filter: ((type IS NULL) AND (type IS NULL) AND ((workflow_state)::text <> 'deleted'::text) AND (context_id = 782370) AND ((context_type)::text = 'Course'::text))
Total runtime: 235.933 ms
(8 rows)
-----
external tools
matched term
beta7.cluster7=> explain analyze SELECT * FROM "context_external_tools" WHERE ((((LOWER(context_external_tools.name) LIKE '%what%')) AND (context_external_tools.workflow_state<>'deleted')) AND ((((LOWER(context_external_tools.name) LIKE '%what%')) AND (context_external_tools.workflow_state<>'deleted')) AND ("context_external_tools".context_id = 782370 AND "context_external_tools".context_type = 'Course'))) ORDER BY name LIMIT 10 OFFSET 0 ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=8.30..8.31 rows=1 width=648) (actual time=2.092..2.095 rows=1 loops=1)
-> Sort (cost=8.30..8.31 rows=1 width=648) (actual time=2.089..2.090 rows=1 loops=1)th
Sort Key: name
Sort Method: quicksort Memory: 25kB
-> Index Scan using index_trgm_context_external_tools_name on context_external_tools (cost=0.01..8.29 rows=1 width=648) (actual time=0.136..2.079 rows=1 loops=1)
Index Cond: ((lower((name)::text) ~~ '%what%'::text) AND (lower((name)::text) ~~ '%what%'::text))
Filter: (((workflow_state)::text <> 'deleted'::text) AND ((workflow_state)::text <> 'deleted'::text) AND (context_id = 782370) AND ((context_type)::text = 'Course'::text))
Total runtime: 2.157 ms
(8 rows)
unmatched term
beta7.cluster7=> explain analyze SELECT * FROM "context_external_tools" WHERE ((((LOWER(context_external_tools.name) LIKE '%huhwhat%')) AND (context_external_tools.workflow_state<>'deleted')) AND ((((LOWER(context_external_tools.name) LIKE '%huhwhat%')) AND (context_external_tools.workflow_state<>'deleted')) AND ("context_external_tools".context_id = 782370 AND "context_external_tools".context_type = 'Course'))) ORDER BY name LIMIT 10 OFFSET 0 ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=8.30..8.31 rows=1 width=648) (actual time=1.195..1.195 rows=0 loops=1)
-> Sort (cost=8.30..8.31 rows=1 width=648) (actual time=1.193..1.193 rows=0 loops=1)
Sort Key: name
Sort Method: quicksort Memory: 25kB
-> Index Scan using index_trgm_context_external_tools_name on context_external_tools (cost=0.01..8.29 rows=1 width=648) (actual time=1.185..1.185 rows=0 loops=1)
Index Cond: ((lower((name)::text) ~~ '%huhwhat%'::text) AND (lower((name)::text) ~~ '%huhwhat%'::text))
Filter: (((workflow_state)::text <> 'deleted'::text) AND ((workflow_state)::text <> 'deleted'::text) AND (context_id = 782370) AND ((context_type)::text = 'Course'::text))
Total runtime: 1.257 ms
(8 rows)
-----
files
matched term
beta7.cluster7=> explain analyze SELECT * FROM "attachments" WHERE ((((LOWER(attachments.display_name) LIKE '%an-%')) AND (attachments.file_state<>'deleted')) AND ((((LOWER(attachments.display_name) LIKE '%an-%')) AND (attachments.file_state<>'deleted')) AND ("attachments".context_id = 782370 AND "attachments".context_type = 'Course'))) ORDER BY collkey(attachments.display_name, 'root', true, 2, true), collkey(attachments.display_name, 'root', true, 2, true) LIMIT 10 OFFSET 0
beta7.cluster7-> ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=161.51..161.52 rows=1 width=2256) (actual time=3320.248..3320.252 rows=1 loops=1)
-> Sort (cost=161.51..161.52 rows=1 width=2256) (actual time=3320.245..3320.246 rows=1 loops=1)
Sort Key: (collkey(display_name, 'root'::text, true, 2, true))
Sort Method: quicksort Memory: 26kB
-> Bitmap Heap Scan on attachments (cost=157.47..161.50 rows=1 width=2256) (actual time=3320.226..3320.228 rows=1 loops=1)
Recheck Cond: ((context_id = 782370) AND ((context_type)::text = 'Course'::text) AND (lower(display_name) ~~ '%an-%'::text))
Filter: (((file_state)::text <> 'deleted'::text) AND ((file_state)::text <> 'deleted'::text))
-> BitmapAnd (cost=157.47..157.47 rows=1 width=0) (actual time=3320.139..3320.139 rows=0 loops=1)
-> Bitmap Index Scan on index_attachments_on_context_id_and_context_type (cost=0.00..15.43 rows=381 width=0) (actual time=0.030..0.030 rows=1 loops=1)
Index Cond: ((context_id = 782370) AND ((context_type)::text = 'Course'::text))
-> Bitmap Index Scan on index_trgm_attachments_display_name (cost=0.00..141.79 rows=1739 width=0) (actual time=3320.099..3320.099 rows=279004 loops=1)
Index Cond: ((lower(display_name) ~~ '%an-%'::text) AND (lower(display_name) ~~ '%an-%'::text))
Total runtime: 3320.357 ms
(13 rows)
unmatched term
beta7.cluster7=> explain analyze SELECT * FROM "attachments" WHERE ((((LOWER(attachments.display_name) LIKE '%bn-%')) AND (attachments.file_state<>'deleted')) AND ((((LOWER(attachments.display_name) LIKE '%bn-%')) AND (attachments.file_state<>'deleted')) AND ("attachments".context_id = 782370 AND "attachments".context_type = 'Course'))) ORDER BY collkey(attachments.display_name, 'root', true, 2, true), collkey(attachments.display_name, 'root', true, 2, true) LIMIT 10 OFFSET 0 ;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=20.44..20.45 rows=1 width=2256) (actual time=2685.099..2685.099 rows=0 loops=1)
-> Sort (cost=20.44..20.45 rows=1 width=2256) (actual time=2685.096..2685.096 rows=0 loops=1)
Sort Key: (collkey(display_name, 'root'::text, true, 2, true))
Sort Method: quicksort Memory: 25kB
-> Index Scan using index_trgm_attachments_display_name on attachments (cost=0.01..20.43 rows=1 width=2256) (actual time=2685.086..2685.086 rows=0 loops=1)
Index Cond: ((lower(display_name) ~~ '%bn-%'::text) AND (lower(display_name) ~~ '%bn-%'::text))
Filter: (((file_state)::text <> 'deleted'::text) AND ((file_state)::text <> 'deleted'::text) AND (context_id = 782370) AND ((context_type)::text = 'Course'::text))
Total runtime: 2685.197 ms
(8 rows)
-----
assignment
query for matching term
beta7.cluster7=> explain analyze SELECT "assignments"."id" AS t0_r0, "assignments"."title" AS t0_r1, "assignments"."description" AS t0_r2, "assignments"."due_at" AS t0_r3, "assignments"."unlock_at" AS t0_r4, "assignments"."lock_at" AS t0_r5, "assignments"."points_possible" AS t0_r6, "assignments"."min_score" AS t0_r7, "assignments"."max_score" AS t0_r8, "assignments"."mastery_score" AS t0_r9, "assignments"."grading_type" AS t0_r10, "assignments"."submission_types" AS t0_r11, "assignments"."before_quiz_submission_types" AS t0_r12, "assignments"."workflow_state" AS t0_r13, "assignments"."context_id" AS t0_r14, "assignments"."context_type" AS t0_r15, "assignments"."assignment_group_id" AS t0_r16, "assignments"."grading_scheme_id" AS t0_r17, "assignments"."grading_standard_id" AS t0_r18, "assignments"."location" AS t0_r19, "assignments"."created_at" AS t0_r20, "assignments"."updated_at" AS t0_r21, "assignments"."group_category" AS t0_r22, "assignments"."submissions_downloads" AS t0_r23, "assignments"."peer_review_count" AS t0_r24, "assignments"."peer_reviews_due_at" AS t0_r25, "assignments"."peer_reviews_assigned" AS t0_r26, "assignments"."peer_reviews" AS t0_r27, "assignments"."automatic_peer_reviews" AS t0_r28, "assignments"."all_day" AS t0_r29, "assignments"."all_day_date" AS t0_r30, "assignments"."could_be_locked" AS t0_r31, "assignments"."cloned_item_id" AS t0_r32, "assignments"."context_code" AS t0_r33, "assignments"."position" AS t0_r34, "assignments"."migration_id" AS t0_r35, "assignments"."grade_group_students_individually" AS t0_r36, "assignments"."anonymous_peer_reviews" AS t0_r37, "assignments"."time_zone_edited" AS t0_r38, "assignments"."turnitin_enabled" AS t0_r39, "assignments"."allowed_extensions" AS t0_r40, "assignments"."needs_grading_count" AS t0_r41, "assignments"."turnitin_settings" AS t0_r42, "assignments"."muted" AS t0_r43, "assignments"."group_category_id" AS t0_r44, "assignments"."freeze_on_copy" AS t0_r45, "assignments"."copied" AS t0_r46, "assignment_groups"."id" AS t1_r0, "assignment_groups"."name" AS t1_r1, "assignment_groups"."rules" AS t1_r2, "assignment_groups"."default_assignment_name" AS t1_r3, "assignment_groups"."position" AS t1_r4, "assignment_groups"."assignment_weighting_scheme" AS t1_r5, "assignment_groups"."group_weight" AS t1_r6, "assignment_groups"."context_id" AS t1_r7, "assignment_groups"."context_type" AS t1_r8, "assignment_groups"."workflow_state" AS t1_r9, "assignment_groups"."created_at" AS t1_r10, "assignment_groups"."updated_at" AS t1_r11, "assignment_groups"."cloned_item_id" AS t1_r12, "assignment_groups"."context_code" AS t1_r13, "assignment_groups"."migration_id" AS t1_r14, "rubric_associations"."id" AS t2_r0, "rubric_associations"."rubric_id" AS t2_r1, "rubric_associations"."association_id" AS t2_r2, "rubric_associations"."association_type" AS t2_r3, "rubric_associations"."use_for_grading" AS t2_r4, "rubric_associations"."created_at" AS t2_r5, "rubric_associations"."updated_at" AS t2_r6, "rubric_associations"."title" AS t2_r7, "rubric_associations"."description" AS t2_r8, "rubric_associations"."summary_data" AS t2_r9, "rubric_associations"."purpose" AS t2_r10, "rubric_associations"."url" AS t2_r11, "rubric_associations"."context_id" AS t2_r12, "rubric_associations"."context_type" AS t2_r13, "rubric_associations"."hide_score_total" AS t2_r14, "rubric_associations"."bookmarked" AS t2_r15, "rubric_associations"."context_code" AS t2_r16, "rubrics"."id" AS t3_r0, "rubrics"."user_id" AS t3_r1, "rubrics"."rubric_id" AS t3_r2, "rubrics"."context_id" AS t3_r3, "rubrics"."context_type" AS t3_r4, "rubrics"."data" AS t3_r5, "rubrics"."points_possible" AS t3_r6, "rubrics"."title" AS t3_r7, "rubrics"."description" AS t3_r8, "rubrics"."created_at" AS t3_r9, "rubrics"."updated_at" AS t3_r10, "rubrics"."reusable" AS t3_r11, "rubrics"."public" AS t3_r12, "rubrics"."read_only" AS t3_r13, "rubrics"."association_count" AS t3_r14, "rubrics"."free_form_criterion_comments" AS t3_r15, "rubrics"."context_code" AS t3_r16, "rubrics"."migration_id" AS t3_r17, "rubrics"."hide_score_total" AS t3_r18, "rubrics"."workflow_state" AS t3_r19 FROM "assignments" LEFT OUTER JOIN "assignment_groups" ON "assignment_groups".id = "assignments".assignment_group_id LEFT OUTER JOIN "rubric_associations" ON "rubric_associations".association_id = "assignments".id AND "rubric_associations".association_type = 'Assignment'AND rubric_associations.purpose = 'grading' LEFT OUTER JOIN "rubric_associations" rubrics_assignments_join ON ("assignments"."id" = "rubrics_assignments_join"."association_id" AND "rubrics_assignments_join"."association_type" = 'Assignment') LEFT OUTER JOIN "rubrics" ON ("rubrics"."id" = "rubrics_assignments_join"."rubric_id") AND rubric_associations.purpose = 'grading' WHERE (((LOWER(assignments.title) LIKE '%unnamed%')) AND ("assignments".context_id = 782370 AND "assignments".context_type = 'Course' AND (assignments.workflow_state != 'deleted'))) ORDER BY assignment_groups.position, assignments.position;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=192.04..192.04 rows=1 width=3877) (actual time=0.148..0.149 rows=1 loops=1)
Sort Key: assignment_groups."position", assignments."position"
Sort Method: quicksort Memory: 25kB
-> Nested Loop Left Join (cost=0.00..192.03 rows=1 width=3877) (actual time=0.106..0.116 rows=1 loops=1)
Join Filter: ((rubric_associations.purpose)::text = 'grading'::text)
-> Nested Loop Left Join (cost=0.00..183.73 rows=1 width=2754) (actual time=0.101..0.109 rows=1 loops=1)
-> Nested Loop Left Join (cost=0.00..172.58 rows=1 width=2746) (actual time=0.094..0.100 rows=1 loops=1)
-> Nested Loop Left Join (cost=0.00..161.43 rows=1 width=1565) (actual time=0.084..0.089 rows=1 loops=1)
-> Index Scan using index_assignments_on_context_id_and_context_type on assignments (cost=0.00..153.08 rows=1 width=1425) (actual time=0.062..0.063 rows=1 loops=1)
Index Cond: ((context_id = 782370) AND ((context_type)::text = 'Course'::text))
Filter: (((workflow_state)::text <> 'deleted'::text) AND (lower((title)::text) ~~ '%unnamed%'::text))
-> Index Scan using assignment_groups_pkey on assignment_groups (cost=0.00..8.33 rows=1 width=140) (actual time=0.009..0.010 rows=1 loops=1)
Index Cond: (id = assignments.assignment_group_id)
-> Index Scan using index_rubric_associations_on_aid_and_atype on rubric_associations (cost=0.00..11.13 rows=1 width=1181) (actual time=0.006..0.006 rows=0 loops=1)
Index Cond: ((association_id = assignments.id) AND ((association_type)::text = 'Assignment'::text))
Filter: ((purpose)::text = 'grading'::text)
-> Index Scan using index_rubric_associations_on_aid_and_atype on rubric_associations rubrics_assignments_join (cost=0.00..11.13 rows=2 width=16) (actual time=0.003..0.003 rows=0 loops=1)
Index Cond: ((assignments.id = association_id) AND ((association_type)::text = 'Assignment'::text))
-> Index Scan using rubrics_pkey on rubrics (cost=0.00..8.28 rows=1 width=1131) (actual time=0.001..0.001 rows=0 loops=1)
Index Cond: (id = rubrics_assignments_join.rubric_id)
Total runtime: 0.745 ms
(21 rows)
query for mismatching term
beta7.cluster7=> explain analyze SELECT assignments.id, "assignments"."id" AS t0_r0, "assignments"."title" AS t0_r1, "assignments"."description" AS t0_r2, "assignments"."due_at" AS t0_r3, "assignments"."unlock_at" AS t0_r4, "assignments"."lock_at" AS t0_r5, "assignments"."points_possible" AS t0_r6, "assignments"."min_score" AS t0_r7, "assignments"."max_score" AS t0_r8, "assignments"."mastery_score" AS t0_r9, "assignments"."grading_type" AS t0_r10, "assignments"."submission_types" AS t0_r11, "assignments"."before_quiz_submission_types" AS t0_r12, "assignments"."workflow_state" AS t0_r13, "assignments"."context_id" AS t0_r14, "assignments"."context_type" AS t0_r15, "assignments"."assignment_group_id" AS t0_r16, "assignments"."grading_scheme_id" AS t0_r17, "assignments"."grading_standard_id" AS t0_r18, "assignments"."location" AS t0_r19, "assignments"."created_at" AS t0_r20, "assignments"."updated_at" AS t0_r21, "assignments"."group_category" AS t0_r22, "assignments"."submissions_downloads" AS t0_r23, "assignments"."peer_review_count" AS t0_r24, "assignments"."peer_reviews_due_at" AS t0_r25, "assignments"."peer_reviews_assigned" AS t0_r26, "assignments"."peer_reviews" AS t0_r27, "assignments"."automatic_peer_reviews" AS t0_r28, "assignments"."all_day" AS t0_r29, "assignments"."all_day_date" AS t0_r30, "assignments"."could_be_locked" AS t0_r31, "assignments"."cloned_item_id" AS t0_r32, "assignments"."context_code" AS t0_r33, "assignments"."position" AS t0_r34, "assignments"."migration_id" AS t0_r35, "assignments"."grade_group_students_individually" AS t0_r36, "assignments"."anonymous_peer_reviews" AS t0_r37, "assignments"."time_zone_edited" AS t0_r38, "assignments"."turnitin_enabled" AS t0_r39, "assignments"."allowed_extensions" AS t0_r40, "assignments"."needs_grading_count" AS t0_r41, "assignments"."turnitin_settings" AS t0_r42, "assignments"."muted" AS t0_r43, "assignments"."group_category_id" AS t0_r44, "assignments"."freeze_on_copy" AS t0_r45, "assignments"."copied" AS t0_r46, "assignment_groups"."id" AS t1_r0, "assignment_groups"."name" AS t1_r1, "assignment_groups"."rules" AS t1_r2, "assignment_groups"."default_assignment_name" AS t1_r3, "assignment_groups"."position" AS t1_r4, "assignment_groups"."assignment_weighting_scheme" AS t1_r5, "assignment_groups"."group_weight" AS t1_r6, "assignment_groups"."context_id" AS t1_r7, "assignment_groups"."context_type" AS t1_r8, "assignment_groups"."workflow_state" AS t1_r9, "assignment_groups"."created_at" AS t1_r10, "assignment_groups"."updated_at" AS t1_r11, "assignment_groups"."cloned_item_id" AS t1_r12, "assignment_groups"."context_code" AS t1_r13, "assignment_groups"."migration_id" AS t1_r14, "rubric_associations"."id" AS t2_r0, "rubric_associations"."rubric_id" AS t2_r1, "rubric_associations"."association_id" AS t2_r2, "rubric_associations"."association_type" AS t2_r3, "rubric_associations"."use_for_grading" AS t2_r4, "rubric_associations"."created_at" AS t2_r5, "rubric_associations"."updated_at" AS t2_r6, "rubric_associations"."title" AS t2_r7, "rubric_associations"."description" AS t2_r8, "rubric_associations"."summary_data" AS t2_r9, "rubric_associations"."purpose" AS t2_r10, "rubric_associations"."url" AS t2_r11, "rubric_associations"."context_id" AS t2_r12, "rubric_associations"."context_type" AS t2_r13, "rubric_associations"."hide_score_total" AS t2_r14, "rubric_associations"."bookmarked" AS t2_r15, "rubric_associations"."context_code" AS t2_r16, "rubrics"."id" AS t3_r0, "rubrics"."user_id" AS t3_r1, "rubrics"."rubric_id" AS t3_r2, "rubrics"."context_id" AS t3_r3, "rubrics"."context_type" AS t3_r4, "rubrics"."data" AS t3_r5, "rubrics"."points_possible" AS t3_r6, "rubrics"."title" AS t3_r7, "rubrics"."description" AS t3_r8, "rubrics"."created_at" AS t3_r9, "rubrics"."updated_at" AS t3_r10, "rubrics"."reusable" AS t3_r11, "rubrics"."public" AS t3_r12, "rubrics"."read_only" AS t3_r13, "rubrics"."association_count" AS t3_r14, "rubrics"."free_form_criterion_comments" AS t3_r15, "rubrics"."context_code" AS t3_r16, "rubrics"."migration_id" AS t3_r17, "rubrics"."hide_score_total" AS t3_r18, "rubrics"."workflow_state" AS t3_r19 FROM "assignments" LEFT OUTER JOIN "assignment_groups" ON "assignment_groups".id = "assignments".assignment_group_id LEFT OUTER JOIN "rubric_associations" ON "rubric_associations".association_id = "assignments".id AND "rubric_associations".association_type = 'Assignment'AND rubric_associations.purpose = 'grading' LEFT OUTER JOIN "rubric_associations" rubrics_assignments_join ON ("assignments"."id" = "rubrics_assignments_join"."association_id" AND "rubrics_assignments_join"."association_type" = 'Assignment') LEFT OUTER JOIN "rubrics" ON ("rubrics"."id" = "rubrics_assignments_join"."rubric_id") AND rubric_associations.purpose = 'grading' INNER JOIN "assignment_overrides" ON assignment_overrides.assignment_id = assignments.id WHERE (((LOWER(assignments.title) LIKE '%kzaghblech%')) AND ("assignments".context_id = 782370 AND "assignments".context_type = 'Course' AND (assignments.workflow_state != 'deleted'))) ORDER BY assignment_groups.position, assignments.position
beta7.cluster7-> ;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=371.17..371.18 rows=1 width=3877) (actual time=73.411..73.411 rows=0 loops=1)
Sort Key: assignment_groups."position", assignments."position"
Sort Method: quicksort Memory: 25kB
-> Nested Loop Left Join (cost=28.68..371.16 rows=1 width=3877) (actual time=73.389..73.389 rows=0 loops=1)
Join Filter: ((rubric_associations.purpose)::text = 'grading'::text)
-> Nested Loop Left Join (cost=28.68..362.87 rows=1 width=2754) (actual time=73.387..73.387 rows=0 loops=1)
-> Nested Loop Left Join (cost=28.68..351.71 rows=1 width=2746) (actual time=73.384..73.384 rows=0 loops=1)
-> Nested Loop Left Join (cost=28.68..340.56 rows=1 width=1565) (actual time=73.382..73.382 rows=0 loops=1)
-> Hash Join (cost=28.68..332.22 rows=1 width=1425) (actual time=73.379..73.379 rows=0 loops=1)
Hash Cond: (assignment_overrides.assignment_id = assignments.id)
-> Seq Scan on assignment_overrides (cost=0.00..270.93 rows=8693 width=8) (actual time=0.010..0.010 rows=1 loops=1)
-> Hash (cost=28.67..28.67 rows=1 width=1425) (actual time=73.337..73.337 rows=0 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 0kB
-> Bitmap Heap Scan on assignments (cost=24.65..28.67 rows=1 width=1425) (actual time=73.334..73.334 rows=0 loops=1)
Recheck Cond: ((context_id = 782370) AND ((context_type)::text = 'Course'::text) AND (lower((title)::text) ~~ '%kzaghblech%'::text))
Filter: ((workflow_state)::text <> 'deleted'::text)
-> BitmapAnd (cost=24.65..24.65 rows=1 width=0) (actual time=73.306..73.306 rows=0 loops=1)
-> Bitmap Index Scan on index_assignments_on_context_id_and_context_type (cost=0.00..5.06 rows=43 width=0) (actual time=0.040..0.040 rows=7 loops=1)
Index Cond: ((context_id = 782370) AND ((context_type)::text = 'Course'::text))
-> Bitmap Index Scan on index_trgm_assignments_title (cost=0.00..19.33 rows=215 width=0) (actual time=73.258..73.258 rows=0 loops=1)
Index Cond: (lower((title)::text) ~~ '%kzaghblech%'::text)
-> Index Scan using assignment_groups_pkey on assignment_groups (cost=0.00..8.33 rows=1 width=140) (never executed)
Index Cond: (id = assignments.assignment_group_id)
-> Index Scan using index_rubric_associations_on_aid_and_atype on rubric_associations (cost=0.00..11.13 rows=1 width=1181) (never executed)
Index Cond: ((association_id = assignments.id) AND ((association_type)::text = 'Assignment'::text))
Filter: ((purpose)::text = 'grading'::text)
-> Index Scan using index_rubric_associations_on_aid_and_atype on rubric_associations rubrics_assignments_join (cost=0.00..11.13 rows=2 width=16) (never executed)
Index Cond: ((assignments.id = association_id) AND ((association_type)::text = 'Assignment'::text))
-> Index Scan using rubrics_pkey on rubrics (cost=0.00..8.28 rows=1 width=1131) (never executed)
Index Cond: (id = rubrics_assignments_join.rubric_id)
Total runtime: 74.133 ms
(31 rows)
-----
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment