All,
Wheeler has been doing some head-to-head performance testing of
Bricolage performance on 8.4 vs. 9.0. So far, 9.0 has been slower, and
I've been trying to track down the particular areas where it is.
One appears to be prepared query parsing. One test of the Bricolage
test suite creates 89 new prepared queries.
9.0's median parse time is 30% slower than 8.4 ( .2ms vs. .134ms). More
seriously, the top 3 slowest parses take 900% as much time as they do on
8.4, and account for 50% of overall prepared query parsing time. Those
appear to be these queries, although the pg_log makes it hard to relate
a parse to what query is being prepared:
execute dbdpg_p81932_1: SELECT u.id, u.login, u.password,
u.active, p.prefix, p.fname, p.mname, p.lname, p.suffix, p.active,
m.grp__id, 1 FROM person p, usr u, member m, user_member c WHERE p.id = u.id AND u.id = c.object_id AND
c.member__id= m.id
AND m.active = '1' AND u.id = $1 ORDER BY LOWER(p.lname),
LOWER(p.fname), LOWER(p.mname), u.id
execute dbdpg_p81932_2: SELECT DISTINCT a.id FROM
site a, member m, site_member c WHERE a.id = c.object_id AND
c.member__id = m.id AND m.active = '1' AND LOWER(a.name) LIKE LOWER($1) ORDER BY a.id
execute dbdpg_p81932_27: DELETE FROM org WHERE id > 1
-- -- Josh Berkus PostgreSQL Experts Inc.
http://www.pgexperts.com