[kronolith] performance on large systems

[John Madden]

Does anyone have any experience on large Kronolith installs?  I'm 
starting to run through imports of data, getting ready for a soft 
roll-out at the end of this month and I'm finding that there's some 
really slow SQL being executed.  Loading the main kronolith page takes 
in the neighborhood of 4-6 seconds on a fairly beefy pgsql box and that 
will likely be a show-stopper.

By large, I mean somewhere in the neighborhood of 500,000 calendars. 
With that amount of data, I'm thinking the regular expressions in 
queries such as this one are problematic:

SELECT DISTINCT s.*  FROM kronolith_shares s  LEFT JOIN 
kronolith_shares_users AS u ON u.share_id = s.share_id 
 

LEFT JOIN kronolith_shares_groups AS g ON g.share_id = s.share_id WHERE 
s.share_owner = 'username' OR (CASE WHEN
CAST(s.perm_creator AS VARCHAR) ~ '^-?[0-9]+$' THEN (CAST(s.perm_creator 
AS INTEGER) & 2) <> 0 ELSE FALSE END) OR (CASE WHEN
CAST(s.perm_default AS VARCHAR) ~ '^-?[0-9]+$' THEN (CAST(s.perm_default 
AS INTEGER) & 2) <> 0 ELSE FALSE END) OR ( u.user_uid
='username' AND (CASE WHEN CAST(u.perm AS VARCHAR) ~ '^-?[0-9]+$' THEN 
(CAST(u.perm AS INTEGER) & 2) <> 0 ELSE FALSE END)) OR
(g.group_uid IN (139204) AND (CASE WHEN CAST(g.perm AS VARCHAR) ~ 
'^-?[0-9]+$' THEN (CAST(g.perm AS INTEGER) & 2) <> 0 ELSE
FALSE END)) ORDER BY s.attribute_name ASC;

...Note that with that 4-6 second wall time I've as yet only loaded 
about 120,000 calendars, so I do expect this problem to get worse.  Why 
the regexp's?  Can they be eliminated?  Anything else I should look for 
performance-wise?

EXPLAIN for the above query:

Unique  (cost=51593.93..54666.63 rows=122908 width=580) (actual 
time=1451.643..1451.660 rows=3 loops=1)
    ->  Sort  (cost=51593.93..51901.20 rows=122908 width=580) (actual 
time=1451.639..1451.643 rows=3 loops=1)
          Sort Key: s.attribute_name, s.share_id, s.share_name, 
s.share_owner, s.share_flags, s.perm_creator, s.perm_default, 
s.perm_guest, s.attribute_desc
          ->  Merge Left Join  (cost=0.00..8853.26 rows=122908 
width=580) (actual time=0.074..1451.603 rows=3 loops=1)
                Merge Cond: (s.share_id = g.share_id)
                Filter: (((s.share_owner)::text = 'jmadden'::text) OR 
CASE WHEN (((s.perm_creator)::character varying)::text ~ 
'^-?[0-9]+$'::text) THEN (((s.perm_creator)::integer & 2) <> 0) ELSE 
false END OR CASE WHEN (((s.perm_default)::character varying)::text ~ 
'^-?[0-9]+$'::text) THEN (((s.perm_default)::integer & 2) <> 0) ELSE 
false END OR (((u.user_uid)::text = 'jmadden'::text) AND CASE WHEN 
(((u.perm)::character varying)::text ~ '^-?[0-9]+$'::text) THEN 
(((u.perm)::integer & 2) <> 0) ELSE false END) OR (((g.group_uid)::text 
= '139204'::text) AND CASE WHEN (((g.perm)::character varying)::text ~ 
'^-?[0-9]+$'::text) THEN (((g.perm)::integer & 2) <> 0) ELSE false END))
                ->  Merge Left Join  (cost=0.00..6371.65 rows=122908 
width=593) (actual time=0.053..737.928 rows=126130 loops=1)
                      Merge Cond: (s.share_id = u.share_id)
                      ->  Index Scan using kronolith_shares_pkey on 
kronolith_shares s  (cost=0.00..5020.51 rows=122908 width=580) (actual 
time=0.028..233.162 rows=123020 loops=1)
                      ->  Index Scan using 
kronolith_shares_users_share_id_idx on kronolith_shares_users u 
(cost=0.00..801.21 rows=19413 width=17) (actual time=0.014..37.860 
rows=19409 loops=1)
                ->  Index Scan using 
kronolith_shares_groups_share_id_idx on kronolith_shares_groups g 
(cost=0.00..925.62 rows=17839 width=16) (actual time=0.016..48.678 
rows=20911 loops=1)
  Total runtime: 1451.774 ms


Thanks,
   John




-- 
John Madden
Sr UNIX Systems Engineer
Ivy Tech Community College of Indiana
jmadden at ivytech.edu