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

* Your Email Address
* Spam protection	Enter the letters below: \ /.__ .___..__ .___. >< [__) _/ [__) _/ / \\| \./__.\| \./__.
Comment	>> 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.
Attachment
Watch this ticket