From: | James Cloos <cloos(at)jhcloos(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Josh Berkus <josh(at)agliodbs(dot)com>, Peter Eisentraut <peter_e(at)gmx(dot)net> |
Subject: | Re: Testing 9.2 in ~production environment |
Date: | 2012-06-20 18:53:58 |
Message-ID: | m34nq5ak69.fsf@carbon.jhcloos.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(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
From | Date | Subject | |
---|---|---|---|
Next Message | Simon Riggs | 2012-06-20 18:56:35 | Re: [PATCH 10/16] Introduce the concept that wal has a 'origin' node |
Previous Message | Simon Riggs | 2012-06-20 18:51:32 | Re: [PATCH 10/16] Introduce the concept that wal has a 'origin' node |