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

[bugs@bugs.horde.org]

DO NOT REPLY TO THIS MESSAGE. THIS EMAIL ADDRESS IS NOT MONITORED.

Ticket URL: http://bugs.horde.org/ticket/?id=4079
-----------------------------------------------------------------------
 Ticket             | 4079
 Updated By         | Jan Schneider <jan at horde.org>
 Summary            | horde_prefs table access very slow under high usage
 Queue              | Horde Base
 Version            | 3.1.1
-State              | Unconfirmed
+State              | Feedback
 Priority           | 3. High
 Type               | Bug
 Owners             | 
-----------------------------------------------------------------------


Jan Schneider <jan at horde.org> (2006-06-28 15:58) wrote:

> 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

Doh, looks like the preference cache was broken in Horde 3.1.1, and noone
noticed so far. Fixed in CVS.

> 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.

That's not true, there is even a primary key. I don't know PostgreSQL well
enough to understand its EXPLAIN results, but on MySQL it clearly shows
that the primary key index is used.