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@bar' AND ownerid=7;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------Seq Scan
onmb (cost=0.00..570.96 rows=3 width=4) (actual time=9.443..25.039 rows=1 loops=1) Filter: ((name ~~* 'foo@bar'::text)
AND(ownerid = 7)) Rows Removed by Filter: 34827Total 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@jhcloos.com> OpenPGP: 1024D/ED7DAEA6