Tickets :: Comment on [#4079] horde_prefs table access very slow under high usage

* Your Email Address
* Spam protection	Enter the letters below: .___.__ .__..___.\ / [__ [ __\| \| _/ >< \| [_./\|__\|./__./ \
Comment	> > > With a few thousand user and a few hundreds simultaneous access to > horde, the application becomes too slow. > > > > I tracked down the problem to too many sequential lookup in the table > horde_prefs. > > At peak hours, the same type of SQL request start to pile up in the > database : > > > > SELECT pref_scope, pref_name, pref_value FROM horde_prefs WHERE > pref_uid = 'xxxx' AND (pref_scope = 'horde' OR pref_scope = 'horde') > ORDER BY pref_scope > > > > In our database, this table containe over 62000 entries. > > > > In pg_stat_user_tables (a system view providing detailed statistics > of database usage in PostgreSQL), we can see a high rate of > sequential lookups : > > > > relid \| schemaname \| relname \| seq_scan \| > seq_tup_read \| idx_scan \| idx_tup_fetch \| n_tup_ins \| n_tup_upd \| > n_tup_del > > 16416 \| public \| horde_prefs \| 128826 \| > 8010852425 \| \| \| 47 \| 3512 \| 0 > > > > Other thing to notice is there is no index used. > > > > > > The SQL creation script confirms that the table horde_prefs is not > using any index. > > > > > > Creating two indexes solved the problem. > > (see attached patch) > > > > Environment : > > OS : FreeBSD 6.0-Release-p6 > > Horde : 3.1.1 > > DB : postgresql 8.1.4
Attachment
Watch this ticket