[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         | j.benoit at free.fr
 Summary            | horde_prefs table access very slow under high usage
 Queue              | Horde Base
 Version            | 3.1.1
 State              | Feedback
 Priority           | 3. High
 Type               | Bug
 Owners             | 
-----------------------------------------------------------------------


j.benoit at free.fr (2006-06-29 06:55) 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.

95% of the queries accumulating were for new sessions (no preference cache
yet anyway). Even without preference caching, the performance problem
disappeared after creating the indexes. 

>> 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 
My mistake, I should have said "for this particular type of query"

> well enough to understand its EXPLAIN results, but on MySQL it 
> clearly shows that the primary key index is used.

horde=# explain SELECT pref_scope, pref_name, pref_value FROM horde_prefs
WHERE pref_uid = 'jean' AND (pref_scope = 'horde' OR pref_scope = 'horde')
ORDER BY pref_scope  ;
                                      QUERY PLAN                          
           
--------------------------------------------------------------------------------------
 Sort  (cost=5315.06..5315.07 rows=4 width=96)
   Sort Key: pref_scope
   ->  Seq Scan on horde_prefs  (cost=0.00..5315.02 rows=4 width=96)
         Filter: (((pref_uid)::text = 'jean'::text) AND (pref_scope =
'horde'::text))
(4 rows)

The EXPLAIN output is clear enough. It says 'Sequential Scan' with a huge
cost.

Compare with the same query after creating the two indexes :

horde=# explain SELECT pref_scope, pref_name, pref_value FROM horde_prefs
WHERE pref_uid = 'jean' AND (pref_scope = 'horde' OR pref_scope = 'horde')
ORDER BY pref_scope  ;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Sort  (cost=32.08..32.08 rows=2 width=72)
   Sort Key: pref_scope
   ->  Bitmap Heap Scan on horde_prefs  (cost=2.03..32.07 rows=2
width=72)
         Recheck Cond: ((pref_uid)::text = 'jean'::text)
         Filter: (pref_scope = 'horde'::text)
         ->  Bitmap Index Scan on pref_uid_idx  (cost=0.00..2.03 rows=8
width=0)
               Index Cond: ((pref_uid)::text = 'jean'::text)
(7 rows)

The primary key is made of the following fields (from 
horde/scripts/sql/horde_prefs.sql) :

   PRIMARY KEY (pref_uid, pref_scope, pref_name)

But, the query doesn't mention pref_name, only pref_uid and pref_scope.

I guess creating those 2 indexes (pref_uid_idx and pref_scope_idx) won't
hurt, even in MySQL.