Re: Poor query performance on one of two "like" databases in production.

OK, getting closer.

If I comment out the last line ( AND users.user_id NOT IN (SELECT user_id FROM mxl_user_group)) the optimizer goes for a Merge Join (yea!) and the query runs in 30 seconds. So something with this NOT IN clause is throwing everything off.

EXPLAIN SELECT substring(users.email from '^.*\@') || domains.domain as email,
users.customer_id,
users.password,
p.policy_set_id,
users.user_id,
domains.auth_type
FROM
(
SELECT d.customer_id, d.domain, d.domain_id, d.auth_type, d.active from mxl_domain d
UNION ALL
SELECT d.customer_id, da.domain, da.domain_id, d.auth_type, da.active from mxl_domain d, mxl_domain_alias da WHERE da.domain_id = d.domain_id
) as domains,
(
SELECT u.email, u.customer_id, u.user_id, u.domain_id, u.password, u.active from mxl_user u
UNION ALL
SELECT ua.email, u.customer_id, u.user_id, u.domain_id, u.password, ua.active from mxl_user u, mxl_user_alias ua WHERE ua.user_id = u.user_id
) as users,
wds_policy_set p
WHERE users.customer_id = p.id
AND users.customer_id = domains.customer_id
AND users.domain_id = domains.domain_id
AND p.default_flag = 1
AND p.web_access_flag = 1
AND p.scope = 3
AND domains.active = 1
AND users.active != 0
AND p.active = 1
-- AND users.user_id NOT IN (SELECT user_id FROM mxl_user_group);

postgres> psql -Upostgres -dmxl -fbadquery.sql
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=2077204.59..2100972.23 rows=490819 width=1564)
Merge Cond: ((p.id = u.customer_id) AND (d.domain_id = u.domain_id))
-> Sort (cost=38160.35..38471.30 rows=124381 width=536)
Sort Key: p.id, d.domain_id
-> Hash Join (cost=578.15..10203.83 rows=124381 width=536)
Hash Cond: (d.customer_id = p.id)
-> Append (cost=0.00..4989.45 rows=77541 width=528)
-> Seq Scan on mxl_domain d (cost=0.00..1810.88 rows=64390 width=28)
Filter: (active = 1)
-> Subquery Scan "*SELECT* 2" (cost=2454.78..3178.57 rows=13151 width=528)
-> Hash Join (cost=2454.78..3047.06 rows=13151 width=33)
Hash Cond: (da.domain_id = d.domain_id)
-> Seq Scan on mxl_domain_alias da (cost=0.00..296.39 rows=13151 width=25)
Filter: (active = 1)
-> Hash (cost=1649.90..1649.90 rows=64390 width=12)
-> Seq Scan on mxl_domain d (cost=0.00..1649.90 rows=64390 width=12)
-> Hash (cost=525.74..525.74 rows=4193 width=8)
-> Seq Scan on wds_policy_set p (cost=0.00..525.74 rows=4193 width=8)
Filter: ((default_flag = 1) AND (web_access_flag = 1) AND (active = 1) AND (scope = 3))
-> Materialize (cost=2039044.24..2064831.74 rows=2063000 width=1044)
-> Sort (cost=2039044.24..2044201.74 rows=2063000 width=1044)
Sort Key: u.customer_id, u.domain_id
-> Append (cost=0.00..162401.13 rows=2063000 width=1044)
-> Seq Scan on mxl_user u (cost=0.00..64467.53 rows=1811146 width=52)
Filter: (active <> 0)
-> Subquery Scan "*SELECT* 2" (cost=8.23..97933.60 rows=251854 width=1044)
-> Merge Join (cost=8.23..95415.06 rows=251854 width=55)
Merge Cond: (u.user_id = ua.user_id)
-> Index Scan using mxl_user_pkey on mxl_user u (cost=0.00..77564.77 rows=1878522 width=28)
-> Index Scan using mxl_user_alias_uid_idx on mxl_user_alias ua (cost=0.00..10109.21 rows=251854 width=31)
Filter: (ua.active <> 0)
(31 rows)

postgres> time psql -Upostgres -dmxl -fbadquery.sql -o kda.out

real 0m32.344s
user 0m2.101s
sys 0m0.314s

On 5/14/10 12:55 PM, "Josh Kupershmidt" <schmiddy(at)gmail(dot)com> wrote:

On Fri, May 14, 2010 at 1:28 PM, <Keaton_Adams(at)mcafee(dot)com> wrote:

> I did run an ANALYZE, then vacuum full, then another ANALYZE on all tables involved in the query. That didn't change things. I compared QUERY TUNING settings in both postgresql.conf files and they are identical. There is a difference in row counts in each table between the databases, but not by much.

Can we see EXPLAIN ANALYZE instead of just EXPLAIN output for the
queries on the two servers?

Also, can you try CLUSTER on the tables involved instead of VACUUM
FULL, to avoid introducing index bloat?

Josh