Grouper Developers Forum

[Shilen Patel <>]

> So there's some inefficiency here making that query unusable. Maybe  
> a missing index for
> the query by user session?

Possibly or postgres is not optimized to run one of the queries.  I'll  
try to reproduce what you're seeing to figure out where things are  
slowing down.  If it's possible for you to send me an xml export of  
your data (mask subjectIds and group names if needed), that would help.

Thanks!

-- Shilen



On Nov 19, 2009, at 3:48 PM, Jim Fox wrote:

> > Once you do that, can you let us know the number of groups and  
> > memberships you have?
> >
> > select count(*) from grouper_groups;
> > select count(*) from grouper_memberships;
> >
> > And how many groups are returned when you performed the query that  
> > took you 20 seconds using the user session?
> >
> > Also it might be good to run analyze on your tables after the  
> > upgrade (or data import).  For instance....
> >
> > analyze grouper_groups;
> > analyze grouper_memberships;
> > analyze grouper_group_set;
>
> I started with a fresh install of grouper 1.5 (no upgrade of the DB)
>
> For Shilen's queries I get:
>
> -----------------------------------------------------------------
>
> grouper15=# select count(*) from grouper_groups;
>  count
> -------
>  17619
> (1 row)
>
> grouper15=# select count(*) from grouper_memberships;
>  count
> -------
>  68236
> (1 row)
>
> grouper15=# analyze verbose grouper_groups;
> INFO:  analyzing "public.grouper_groups"
> INFO:  "grouper_groups": scanned 3000 of 90650 pages, containing 629  
> live rows and 27851 dead rows; 629 rows in sample, 19006 estimated  
> total rows
> ANALYZE
>
> grouper15=# analyze verbose grouper_memberships;
> INFO:  analyzing "public.grouper_memberships"
> INFO:  "grouper_memberships": scanned 3000 of 3635 pages, containing  
> 56267 live rows and 14575 dead rows; 3000 rows in sample, 68177  
> estimated total rows
> ANALYZE
> grouper15=# analyze verbose grouper_group_set;
> INFO:  analyzing "public.grouper_group_set"
> INFO:  "grouper_group_set": scanned 3000 of 10407 pages, containing  
> 50817 live rows and 108 dead rows; 3000 rows in sample, 176284  
> estimated total rows
> ANALYZE
>
> ----------------------------------------
>
> Now I run these commands in gsh:  (basically a query of groups for a  
> member)
>
> (this one uses a root session)
>
> ---------
> import edu.internet2.middleware.grouper.filter.GrouperQuery
> import edu.internet2.middleware.grouper.filter.GroupMemberFilter
> fox=SubjectFinder.findByIdentifier("fox")
> session=GrouperSession.startRootSession()
> root=StemFinder.findRootStem(session)
> GroupMemberFilter mf = new GroupMemberFilter(fox, root)
> GrouperQuery query = GrouperQuery.createQuery(session, mf)
> p("start getGroups for fox with root session")
> System.currentTimeMillis()
> query.getGroups().size()
> p("end getGroups")
> System.currentTimeMillis()
> ---------
>
> This shows (minus the startup commentary)
>
>    start getGroups for fox with root session
>    java.lang.Long: 1258662253227
>    29
>    end getGroups
>    java.lang.Long: 1258662254196
>
>    ++++++++ note elapsed time of  1 sec
>
>
>
> (this one uses a user session)
>
> ----------
> import edu.internet2.middleware.grouper.filter.GrouperQuery
> import edu.internet2.middleware.grouper.filter.GroupMemberFilter
> donn=SubjectFinder.findByIdentifier("donn")
> fox=SubjectFinder.findByIdentifier("fox")
> session=GrouperSession.start(donn)
> root=StemFinder.findRootStem(session)
> GroupMemberFilter mf = new GroupMemberFilter(fox, root)
> GrouperQuery query = GrouperQuery.createQuery(session, mf)
> p("start getGroups for fox with session by donn")
> System.currentTimeMillis()
> query.getGroups().size()
> p("end getGroups")
> System.currentTimeMillis()
> ---------
>
> This shows
>
>    start getGroups for fox with session by donn
>    java.lang.Long: 1258662313576
>    29
>    end getGroups
>    java.lang.Long: 1258662346952
>
>    ++++++++ note elapsed time of 33 sec
>
>
>
> So there's some inefficiency here making that query unusable. Maybe  
> a missing index for
> the query by user session?
>
> If I do the latter query with a user who is in only three groups
> it runs in about 7 seconds.
>
> Jim
>
>
>
>
>
> o