Re: Testing 9.2 in ~production environment - Mailing list pgsql-hackers

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