Created
November 19, 2015 15:20
-
-
Save jcreed/ad29fb50c03d2768c484 to your computer and use it in GitHub Desktop.
WA-3876 Search Query analyzed
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Limit (cost=10966.16..10966.26 rows=1 width=3246) (actual time=95728.042..95728.058 rows=25 loops=1) | |
| -> Unique (cost=10966.16..10966.26 rows=1 width=3246) (actual time=95728.041..95728.055 rows=25 loops=1) | |
| -> Sort (cost=10966.16..10966.17 rows=1 width=3246) (actual time=95728.040..95728.041 rows=25 loops=1) | |
| Sort Key: (lower((applicants.last_name)::text)), (lower((applicants.first_name)::text)), applicants.id, applicants.ssn, applicants.created_at, applicants.updated_at, applicants.score, applicants.identifier, applicants.email, applicants.last_name, applicants.middle_name, applicants.first_name, applicants.suffix, applicants.title, applicants.last_refresh, applicants.date_added, applicants.cas_id, applicants.association_id, applicants.application_complete, applicants.verified, applicants.verified_date, applicants.professional_pin, applicants.match_id, applicants.pre_submission_release_authorized, applicants.photo_file_name, applicants.photo_content_type, applicants.photo_file_size, applicants.photo_updated_at, applicants.id_number, applicants.id_number_type, applicants.visible_for_advisor, applicants.direct_applicant_complete, applicants.email_type_id, applicants.dentpin, (lower((applicants.cas_id)::text)), (lower((applicants.email)::text)) | |
| Sort Method: quicksort Memory: 84kB | |
| -> Nested Loop (cost=9694.37..10966.15 rows=1 width=3246) (actual time=436.983..95725.856 rows=223 loops=1) | |
| Join Filter: (designations.applicant_id = designations_1.applicant_id) | |
| Rows Removed by Join Filter: 1157357 | |
| -> Nested Loop (cost=9659.27..9790.24 rows=1 width=4) (actual time=53.725..115.890 rows=4905 loops=1) | |
| -> Nested Loop (cost=9658.99..9781.93 rows=1 width=8) (actual time=53.723..93.384 rows=4905 loops=1) | |
| -> HashAggregate (cost=9658.56..9658.74 rows=18 width=4) (actual time=53.712..58.681 rows=5274 loops=1) | |
| Group Key: designations_2.id | |
| -> Nested Loop (cost=1207.82..9658.52 rows=18 width=4) (actual time=21.704..51.688 rows=5274 loops=1) | |
| -> Nested Loop (cost=1207.54..9652.84 rows=18 width=12) (actual time=21.701..42.213 rows=5274 loops=1) | |
| -> Hash Semi Join (cost=1207.26..9641.71 rows=35 width=8) (actual time=21.695..31.645 rows=5274 loops=1) | |
| Hash Cond: (designations_2.applicant_id = applicants_1.id) | |
| -> Index Scan using index_designations_on_program_id on designations designations_2 (cost=0.43..8409.70 rows=9440 width=16) (actual time=0.011..5.691 rows=8128 loops=1) | |
| Index Cond: (program_id = 21152) | |
| -> Hash (cost=1193.73..1193.73 rows=1048 width=4) (actual time=21.671..21.671 rows=5274 loops=1) | |
| Buckets: 1024 Batches: 1 Memory Usage: 186kB | |
| -> HashAggregate (cost=1172.77..1183.25 rows=1048 width=4) (actual time=20.322..21.029 rows=5274 loops=1) | |
| Group Key: applicants_1.id | |
| -> Nested Loop (cost=35.10..1170.15 rows=1048 width=4) (actual time=0.389..19.080 rows=5274 loops=1) | |
| -> Hash Join (cost=34.68..668.21 rows=1048 width=4) (actual time=0.380..8.052 rows=5274 loops=1) | |
| Hash Cond: (designations_3.application_status_id = statuses_4.id) | |
| -> Nested Loop (cost=1.14..616.42 rows=2072 width=8) (actual time=0.021..6.253 rows=8128 loops=1) | |
| -> Nested Loop (cost=0.71..197.80 rows=19 width=8) (actual time=0.016..0.021 rows=1 loops=1) | |
| -> Index Only Scan using index_user_identity_programs_on_user_identity_id_and_program_id on user_identity_programs user_identity_programs_1 (cost=0.42..43.72 rows=19 width=4) (actual time=0.007..0.009 rows=2 loops=1) | |
| Index Cond: (user_identity_id = 70575) | |
| Heap Fetches: 2 | |
| -> Index Scan using programs_pkey on programs programs_1 (cost=0.29..8.10 rows=1 width=4) (actual time=0.003..0.004 rows=0 loops=2) | |
| Index Cond: (id = user_identity_programs_1.program_id) | |
| Filter: ((type)::text = 'Program'::text) | |
| -> Index Scan using index_designations_on_program_id on designations designations_3 (cost=0.43..17.84 rows=419 width=12) (actual time=0.004..4.675 rows=8128 loops=1) | |
| Index Cond: (program_id = programs_1.id) | |
| -> Hash (cost=26.98..26.98 rows=525 width=4) (actual time=0.351..0.351 rows=440 loops=1) | |
| Buckets: 1024 Batches: 1 Memory Usage: 16kB | |
| -> Seq Scan on statuses statuses_4 (cost=0.00..26.98 rows=525 width=4) (actual time=0.005..0.247 rows=440 loops=1) | |
| Filter: (show AND ((type)::text = 'ApplicationStatus'::text)) | |
| Rows Removed by Filter: 598 | |
| -> Index Only Scan using applicants_pkey on applicants applicants_1 (cost=0.42..0.47 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=5274) | |
| Index Cond: (id = designations_3.applicant_id) | |
| Heap Fetches: 5274 | |
| -> Index Scan using application_statuses_pkey on statuses statuses_2 (cost=0.28..0.31 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=5274) | |
| Index Cond: (id = designations_2.application_status_id) | |
| Filter: (show AND ((type)::text = 'ApplicationStatus'::text)) | |
| -> Index Only Scan using application_statuses_pkey on statuses statuses_3 (cost=0.28..0.31 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=5274) | |
| Index Cond: (id = designations_2.application_status_id) | |
| Heap Fetches: 5274 | |
| -> Index Scan using designations_pkey on designations designations_1 (cost=0.43..6.83 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=5274) | |
| Index Cond: (id = designations_2.id) | |
| Filter: (application_status_id = 861) | |
| Rows Removed by Filter: 0 | |
| -> Index Only Scan using application_statuses_pkey on statuses statuses_1 (cost=0.28..8.29 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=4905) | |
| Index Cond: (id = 861) | |
| Heap Fetches: 4905 | |
| -> Nested Loop (cost=35.10..1175.39 rows=41 width=3250) (actual time=0.069..19.465 rows=236 loops=4905) | |
| -> Hash Join (cost=34.68..668.21 rows=1048 width=4) (actual time=0.015..7.006 rows=5274 loops=4905) | |
| Hash Cond: (designations.application_status_id = statuses.id) | |
| -> Nested Loop (cost=1.14..616.42 rows=2072 width=8) (actual time=0.014..5.640 rows=8128 loops=4905) | |
| -> Nested Loop (cost=0.71..197.80 rows=19 width=8) (actual time=0.008..0.012 rows=1 loops=4905) | |
| -> Index Only Scan using index_user_identity_programs_on_user_identity_id_and_program_id on user_identity_programs (cost=0.42..43.72 rows=19 width=4) (actual time=0.004..0.005 rows=2 loops=4905) | |
| Index Cond: (user_identity_id = 70575) | |
| Heap Fetches: 9810 | |
| -> Index Scan using programs_pkey on programs (cost=0.29..8.10 rows=1 width=4) (actual time=0.002..0.003 rows=0 loops=9810) | |
| Index Cond: (id = user_identity_programs.program_id) | |
| Filter: ((type)::text = 'Program'::text) | |
| -> Index Scan using index_designations_on_program_id on designations (cost=0.43..17.84 rows=419 width=12) (actual time=0.005..4.128 rows=8128 loops=4905) | |
| Index Cond: (program_id = programs.id) | |
| -> Hash (cost=26.98..26.98 rows=525 width=4) (actual time=0.321..0.321 rows=440 loops=1) | |
| Buckets: 1024 Batches: 1 Memory Usage: 16kB | |
| -> Seq Scan on statuses (cost=0.00..26.98 rows=525 width=4) (actual time=0.004..0.236 rows=440 loops=1) | |
| Filter: (show AND ((type)::text = 'ApplicationStatus'::text)) | |
| Rows Removed by Filter: 598 | |
| -> Index Scan using applicants_pkey on applicants (cost=0.42..0.47 rows=1 width=3246) (actual time=0.002..0.002 rows=0 loops=25868970) | |
| Index Cond: (id = designations.applicant_id) | |
| Filter: (((last_name)::text ~~* '%z%'::text) AND ((first_name)::text ~~* '%a%'::text)) | |
| Rows Removed by Filter: 1 | |
| Planning time: 6.679 ms | |
| Execution time: 95728.447 ms |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment