MySQL does not use either of these indexes and always uses the primary 
key index.  Chuck confirms this.  I also tested Oracle:



SQL> set autotrace traceonly explain



With only the primary key index, the explain output is:



Execution Plan

----------------------------------------------------------

    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=35)

    1    0   TABLE ACCESS (BY INDEX ROWID) OF 'HORDE_PREFS' (Cost=4 
Card=1 Bytes=35)

    2    1     INDEX (RANGE SCAN) OF 'PK_HORDE_PREFS' (UNIQUE) (Cost=3 Card=1)



With the addition of the two one-column indexes, the explain output is:



Execution Plan

----------------------------------------------------------

    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=35)

    1    0   SORT (ORDER BY) (Cost=4 Card=1 Bytes=35)

    2    1     TABLE ACCESS (BY INDEX ROWID) OF 'HORDE_PREFS' (Cost=2 
Card=1 Bytes=35)

    3    2       INDEX (RANGE SCAN) OF 'PREF_UID_IDX' (NON-UNIQUE) 
(Cost=1 Card=6)



With the addition of one two-column index, the explain output is:



Execution Plan

----------------------------------------------------------

    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=35)

    1    0   TABLE ACCESS (BY INDEX ROWID) OF 'HORDE_PREFS' (Cost=2 
Card=1 Bytes=35)

    2    1     INDEX (RANGE SCAN) OF 'PREF_UID_SCOPE_IDX' (NON-UNIQUE) 
(Cost=1 Card=1)

I tested again with MySQL, and it also improves performance there a 
bit, even though not nearly as much as with PostgreSQL.

The indexes have been added to all SQL scripts now.

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.

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

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

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.

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