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 on mb (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: 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 <[email protected]> OpenPGP: 1024D/ED7DAEA6
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
