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. > > > > 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. > > > >
Attachment
Watch this ticket

Saved Queries