Re: Testing 9.2 in ~production environment

Updating pg_database to set datctype='C' did solve the speed issues with
the two largs dbs.

Presumably, since LC_CTYPE=en_US.UTF-8 was in the env when I ran pg_restore,
it overrode the ctype setting in the dump files.

Some of the slow selects do use ilike; even w/ datctype='C' the indices
are skipped for at least this query:

# explain analyze SELECT mb_id FROM mb WHERE name ILIKE 'foo(at)bar' AND ownerid=7;

QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Seq Scan on mb (cost=0.00..570.96 rows=3 width=4) (actual time=9.443..25.039 rows=1 loops=1)
Filter: ((name ~~* 'foo(at)bar'::text) AND (ownerid = 7))
Rows Removed by Filter: 34827
Total runtime: 25.071 ms
(4 rows)

The mb table has several indices, including separate ones on name and ownerid.

(not my design, btw. And I really do need to re-write the middleware....)

Whether it is strcoll(3) (even though LC_COLLATE is explicitly C) or
LIKE, it does make a significant difference for those two apps.

-JimC
--
James Cloos <cloos(at)jhcloos(dot)com> OpenPGP: 1024D/ED7DAEA6