sql - Improving Subquery performance in Postgres -

-

i have these 2 tables in database

  student table                   student semester table | column     : type     |       | column     : type     | |------------|----------|       |------------|----------| | student_id : integer  |       | student_id : integer  |       | satquan    : smallint |       | semester   : integer  | | actcomp    : smallint |       | enrolled   : boolean  |  | entryyear  : smallint |       | major      : text     | |-----------------------|       | college    : text     |                                 |-----------------------|

where student_id unique key in student table, , foreign key in student semester table. semester integer 1 first semester, 2 second, , on.

i'm doing queries want students entryyear (and sat and/or act scores), of students associated data student semester table.

currently, queries this:

select * student_semester student_id in(     select student_id student_semester     student_id in(         select student_id student entryyear = 2006     ) , college = 'as' , ... ) order student_id, semester;

but, results in relatively long running queries (400ms) when selecting ~1k students. according execution plan, of time spent doing hash join. ameliorate this, have added satquan, actpcomp, , entryyear columns student_semester table. reduces time run query ~90%, results in lot of redundant data. there better way this?

these indexes have (along implicit indexes on student_id):

create index act_sat_entryyear on student using btree (entryyear, actcomp, sattotal) create index student_id_major_college on student_semester using btree (student_id, major, college)

query plan

query plan hash join  (cost=17311.74..35895.38 rows=81896 width=65) (actual time=121.097..326.934 rows=25680 loops=1)   hash cond: (public.student_semester.student_id = public.student_semester.student_id)   ->  seq scan on student_semester  (cost=0.00..14307.20 rows=698820 width=65) (actual time=0.015..154.582 rows=698820 loops=1)   ->  hash  (cost=17284.89..17284.89 rows=2148 width=8) (actual time=121.062..121.062 rows=1284 loops=1)         buckets: 1024  batches: 1  memory usage: 51kb         ->  hashaggregate  (cost=17263.41..17284.89 rows=2148 width=8) (actual time=120.708..120.871 rows=1284 loops=1)               ->  hash semi join  (cost=1026.68..17254.10 rows=3724 width=8) (actual time=4.828..119.619 rows=6184 loops=1)                     hash cond: (public.student_semester.student_id = student.student_id)                     ->  seq scan on student_semester  (cost=0.00..16054.25 rows=42908 width=4) (actual time=0.013..109.873 rows=42331 loops=1)                           filter: ((college)::text = 'as'::text)                     ->  hash  (cost=988.73..988.73 rows=3036 width=4) (actual time=4.801..4.801 rows=3026 loops=1)                           buckets: 1024  batches: 1  memory usage: 107kb                           ->  bitmap heap scan on student  (cost=71.78..988.73 rows=3036 width=4) (actual time=0.406..3.223 rows=3026 loops=1)                                 recheck cond: (entryyear = 2006)                                 ->  bitmap index scan on student_act_sat_entryyear_index  (cost=0.00..71.03 rows=3036 width=0) (actual time=0.377..0.377 rows=3026 loops=1)                                       index cond: (entryyear = 2006) total runtime: 327.708 ms

i mistaken there not being seq scan in query. think seq scan being done due number of rows match college condition; when change 1 has less students index used. source: https://stackoverflow.com/a/5203827/880928

query entryyear column included student semester table

select * student_semester student_id in(     select student_id student_semester     entryyear = 2006 , collgs = 'as' ) order student_id, semester;

query plan

sort  (cost=18597.13..18800.49 rows=81343 width=65) (actual time=72.946..74.003 rows=25680 loops=1)   sort key: public.student_semester.student_id, public.student_semester.semester   sort method: quicksort  memory: 3546kb   ->  nested loop  (cost=9843.87..11962.91 rows=81343 width=65) (actual time=24.617..40.751 rows=25680 loops=1)         ->  hashaggregate  (cost=9843.87..9845.73 rows=186 width=4) (actual time=24.590..24.836 rows=1284 loops=1)               ->  bitmap heap scan on student_semester  (cost=1612.75..9834.63 rows=3696 width=4) (actual time=10.401..23.637 rows=6184 loops=1)                     recheck cond: (entryyear = 2006)                     filter: ((collgs)::text = 'as'::text)                     ->  bitmap index scan on entryyear_act_sat_semester_enrolled_cumdeg_index  (cost=0.00..1611.82 rows=60192 width=0) (actual time=10.259..10.259 rows=60520 loops=1)                           index cond: (entryyear = 2006)         ->  index scan using student_id_index on student_semester  (cost=0.00..11.13 rows=20 width=65) (actual time=0.003..0.010 rows=20 loops=1284)               index cond: (student_id = public.student_semester.student_id) total runtime: 74.938 ms

the clean version of query is

select ss.*     student s     inner join     student_semester ss using(student_id)     s.entryyear = 2006     , exists (         select 1         student_semester                     college = 'as'             , student_id = s.student_id     ) order ss.student_id, semester

Comments

Post a Comment

Popular posts from this blog

java - Jmockit String final length method mocking Issue -

-
using jmockit 1.2 jar, trying mock string's length method getting unexpected invocation exception: failed: test java.lang.illegalstateexception: missing invocation mocked type @ point; please make sure such invocations appear after declaration of suitable mock field or parameter @ stringdemo.testa$1.<init>(testa.java:17) @ stringdemo.testa.test(testa.java:13) i using testng: @test public void test() throws exception { new expectations() { @mocked("length") string astring; { astring.length(); result = 2; } }; system.out.println(a.showa("test")); } } actual class a: public class { public static int showa(string str){ int a= str.length(); return a; } } this wrong way of recording expected results. shouldn't mock , record string's length() method, record showa() instead. here solution @suppresswarnings("unused...
Read more

What is the difference between data design and data model(ERD) -

-
i have google question , find answer in day. know, data design many many table have list column,and mark pk , fk. why er-diagram have find this? data design process of designing database. main output of data design detailed logical data model of database. some people data design includes of needed logical , physical design choices , physical storage parameters needed generate design in data definition language. technically, database analysis, database analysts (dbas) trained do. while person can both data design , database analysis, these 2 different tasks. data design models data. database analysis takes model , applies 1 or more database engines (relational, hierarchical, nosql). a logical data model 1 of main outputs of data design. data model represented entity relationship diagram, or er diagram.
Read more

ios - Can NSManagedObject conform to NSCoding -

-
i need transfer single object across device. right converting nsmanagedobject dictionary , archiving , sending nsdata. upon receiving unarchiving it. transfer nsmanagedobject archiving , unarchiving instead of creating intermediate data object. @interface test : nsmanagedobject<nscoding> @property (nonatomic, retain) nsstring * title; @end @implementation test @dynamic title; - (id)initwithcoder:(nscoder *)coder { self = [super init]; if (self) { self.title = [coder decodeobjectforkey:@"title"]; //<crash } return self; } - (void)encodewithcoder:(nscoder *)coder { [coder encodeobject:self.title forkey:@"title"]; } @end nsdata *archivedobjects = [nskeyedarchiver archiveddatawithrootobject:testobj]; nsdata *objectsdata = archivedobjects; if ([objectsdata length] > 0) { nsarray *objects = [nskeyedunarchiver unarchiveobjectwithdata:objectsdata]; } the problem above code is. crashes @ self.title in initwithcoder sa...
Read more