[horde] Horde 3.1.3 and datatree related performance problems

[Chris Stromsoe]

On Sun, 19 Nov 2006, Chuck Hagenbuch wrote:
> Quoting Chris Stromsoe <cbs at cts.ucla.edu>:
>
>> I have partially fixed the kronolith problems.  I've added two indexes 
>> which have cut query time down significantly.
>> 
>> 1) There is no index created for horde_datatree(datatree_id).  I added 
>> one and cut one set of queries from ~30s down to .5s:
>
> Really? I thought PRIMARY KEY would automatically create an index. 
> That's not true?

Interesting.  No, it looks like you're right.  Creating a PRIMARY KEY did 
auto-create an index.  I have no idea why the query planner wasn't using 
it.  The only difference between the two indices is that the auto-created 
index has the "primary_key" label attached:

horde=# \d horde_datatree_pkey
Index "public.horde_datatree_pkey"
    Column    |  Type
-------------+---------
  datatree_id | integer
primary key, btree, for table "public.horde_datatree"

horde=# \d datatree_id_idx
Index "public.datatree_id_idx"
    Column    |  Type
-------------+---------
  datatree_id | integer
btree, for table "public.horde_datatree"


I'll remove the extra index and play around with it some more to try and 
see if I can figure out why adding a second index on the same data caused 
the planner to start using it.


>> Calls to listCalendars() with $ownersonly = false take forever to 
>> complete; loading calendars.php takes more than 20 minutes.  Changing 
>> listCalendars() to force $ownersonly = true fixes the performance 
>> problem, but I suspect won't be a very useful solution.
>> 
>> Any ideas about where to look to speed things up?
>
> Probably back to the datatree code again, perhaps in the creation of 
> large arrays.

Thanks.

In my previous installation of 3.0, I had hacked listCalendars to only 
ever list the single default calendar.  There has been some interest in 
offering shared calendaring with 3.1, so I haven't applied the same hacks. 
It looks now like listCalendars() is returning every single calendar in 
the system (close to 13000 of them).

Is there a permission settins that I can apply to the datatree table to 
"unshare" all of the calendars so that listCalendars() doesn't return all 
of them?  Ideally, I'd like listCalendars() to return only the default 
calendar plus any other calendars that have been explicitly shared with 
that person.  I will be removing the ability to share calendars with all 
users or with unauthenticated users.


-Chris