[PERFORM] Wierd issues
I lost the email that had the fix for this and now I need it again can someone or tom let me know what the fix was, I cant find it in any of my emails or archived on the internet This is what I got Two servers, one debian, one fedora Debain dual 3ghz, 1 gig ram, ide, PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4 Fedora: Dual 3ghz, 1 gig ram, scsi, PostgreSQL 7.3.4-RH on i386-redhat-linux-gnu, compiled by GCC i386-redhat-linux-gcc (GCC) 3.3.2 20031022 (Red Hat Linux 3.3.2-1) Both have same databases, Both have had vacume full ran on them. Both doing the same query Select * from vpopmail; The vpopmail is a view, this is the view View vpopmail Column | Type | Modifiers ---++--- pw_name | character varying(32) | pw_domain | character varying(64) | pw_passwd | character varying | pw_uid | integer | pw_gid | integer | pw_gecos | character varying | pw_dir | character varying(160) | pw_shell | character varying(20) | View definition: SELECT ea.email_name AS pw_name, ea.domain AS pw_domain, get_pwd(u.username, '127.0.0.1'::varchar, '101'::varchar, 'MD5'::varchar) AS pw_passwd, 0 AS pw_uid, 0 AS pw_gid, ''::varchar AS pw_gecos, ei.directory AS pw_dir, ei.quota AS pw_shell FROM email_addresses ea, email_info ei, users u, user_resources ur WHERE (ea.user_resource_id = ei.user_resource_id) AND (get_pwd(u.username, '127.0.0.1'::varchar, '101'::varchar, 'MD5'::varchar) IS NOT NULL)) AND (ur.id = ei.user_resource_id)) AND (u.id = ur.user_id)) AND (NOT (EXISTS (SELECT forwarding.email_id FROM forwarding WHERE (forwarding.email_id = ea.id); Both are set to the same buffers and everything this is the execution time: Debian: Total runtime: 35594.81 msec Fedora: Total runtime: 2279869.08 msec Huge difference as you can see here are the pastes of the stuff Debain: user_acl=# explain analyze SELECT count(*) from vpopmail; NOTICE: QUERY PLAN: Aggregate (cost=438231.94..438231.94 rows=1 width=20) (actual time=35594.67..35594.67 rows=1 loops=1) - Hash Join (cost=434592.51..438142.51 rows=35774 width=20) (actual time=34319.24..35537.11 rows=70613 loops=1) - Seq Scan on email_info ei (cost=0.00..1721.40 rows=71640 width=4) (actual time=0.04..95.13 rows=71689 loops=1) - Hash (cost=434328.07..434328.07 rows=35776 width=16) (actual time=34319.00..34319.00 rows=0 loops=1) - Hash Join (cost=430582.53..434328.07 rows=35776 width=16) (actual time=2372.45..34207.21 rows=70613 loops=1) - Seq Scan on users u (cost=0.00..1938.51 rows=71283 width=4) (actual time=0.81..30119.58 rows=70809 loops=1) - Hash (cost=430333.64..430333.64 rows=35956 width=12) (actual time=2371.51..2371.51 rows=0 loops=1) - Hash Join (cost=2425.62..430333.64 rows=35956 width=12) (actual time=176.73..2271.14 rows=71470 loops=1) - Seq Scan on email_addresses ea (cost=0.00..426393.25 rows=35956 width=4) (actual time=0.06..627.49 rows=71473 loops=1) SubPlan - Index Scan using forwarding_idx on forwarding (cost=0.00..5.88 rows=1 width=4) (actual time=0.00..0.00 rows=0 loops=71960) - Hash (cost=1148.37..1148.37 rows=71637 width=8) (actual time=176.38..176.38 rows=0 loops=1) - Seq Scan on user_resources ur (cost=0.00..1148.37 rows=71637 width=8) (actual time=0.03..82.21 rows=71686 loops=1) Total runtime: 35594.81 msec EXPLAIN And for fedora its Aggregate (cost=416775.52..416775.52 rows=1 width=20) (actual time=2279868.57..2279868.58 rows=1 loops=1) - Hash Join (cost=413853.79..416686.09 rows=35772 width=20) (actual time=2279271.26..2279803.91 rows=70841 loops=1) Hash Cond: (outer.user_resource_id = inner.id) - Seq Scan on email_info ei (cost=0.00..1666.07 rows=71907 width=4) (actual time=8.12..171.10 rows=71907 loops=1) - Hash (cost=413764.36..413764.36 rows=35772 width=16) (actual time=2279263.03..2279263.03 rows=0 loops=1) - Hash Join (cost=410712.87..413764.36 rows=35772 width=16) (actual time=993.90..2279008.72 rows=70841 loops=1) Hash Cond: (outer.id = inner.user_id) - Seq Scan on users u (cost=0.00..1888.85 rows=71548 width=4) (actual time=18.38..2277152.51 rows=71028 loops=1) Filter: (get_pwd(username, '127.0.0.1'::character varying, '101'::character varying, 'MD5'::character varying) IS NOT NULL) - Hash (cost=410622.99..410622.99 rows=35952 width=12) (actual time=975.40..975.40 rows=0 loops=1) - Hash Join (cost=408346.51..410622.99 rows=35952 width=12) (actual time=507.52..905.91 rows=71697 loops=1) Hash Cond: (outer.id = inner.user_resource_id) - Seq Scan on user_resources ur (cost=0.00..1108.04 rows=71904 width=8) (actual time=0.05..95.65 rows=71904 loops=1) - Hash (cost=408256.29..408256.29 rows=36091 width=4) (actual time=507.33..507.33 rows=0 loops=1) - Seq Scan on email_addresses ea (cost=0.00..408256.29 rows=36091 width=4) (actual time=0.15..432.83 rows=71700 loops=1) Filter: (NOT (subplan)) SubPlan - Index Scan using forwarding_idx on forwarding (cost=0.00..5.63 rows=1 width=4)
Re: [PERFORM] Wierd issues
Yes I did do analyze the here is the get_pwd function -- Function: public.get_pwd(varchar, varchar, varchar, varchar) -- DROP FUNCTION public.get_pwd(varchar, varchar, varchar, varchar); CREATE OR REPLACE FUNCTION public.get_pwd(varchar, varchar, varchar, varchar) RETURNS varchar AS ' DECLARE p_username ALIAS for $1; p_server ALIAS for $2; p_service ALIAS for $3; p_pwd_type ALIAS for $4; l_resource_id integer; l_server_id integer; l_service_id integer; l_allow_deny char(1); l_user_id integer; l_account_id integer; l_passwd varchar(40); begin -- get server identifier select id into l_server_id from servers s where address = p_server; if NOT FOUND then -- try to get default server select id into l_server_id from servers s where address = \'default\'; end if; if l_server_id isnull then return NULL; end if; -- get service identifier select id into l_service_id from services s where radius_service = p_service; if l_service_id isnull then return NULL; end if; -- get resource identifier (server/service combination) select id into l_resource_id from resources r where service_id = l_service_id and server_id = l_server_id; -- could not find resource via server_id, now look via server\'s group if any if l_resource_id isnull then select id into l_resource_id from resources r where service_id = l_service_id and server_group_id = (select server_group_id from servers where id = l_server_id); end if; -- could not determine resource user wants to access, so deny by returning NULL passwd if l_resource_id isnull then return NULL; end if; -- at this point we have a valid resource_id -- determine if valid username select u.id, u.account_id into l_user_id, l_account_id from users u, accounts a where u.username = upper(p_username) -- always uppercase in DB and u.del_id = 0 and u.status = \'A\' and a.status = \'A\' and u.account_id = a.id; -- if active user not found then return NULL for passwd if l_user_id isnull then return null; end if; -- user specific control select allow_deny into l_allow_deny from users_acl where resource_id = l_resource_id and user_id = l_user_id; if l_allow_deny = \'D\' then return NULL; elsif l_allow_deny isnull then -- no user-specific control select max(allow_deny) -- \'D\' is \'A\' hence deny takes precedence if conflict across groups into l_allow_deny from users_acl where resource_id = l_resource_id and user_group_id in (select user_group_id from user_group_assignments where user_id = l_user_id); elsif l_allow_deny = \'A\' then -- do nothing; -- get and return passwd below end if; if l_allow_deny isnull or l_allow_deny = \'D\' then return NULL; elsif l_allow_deny = \'A\' then select password into l_passwd from user_pwds where password_type = upper(p_pwd_type) and user_id = l_user_id; return l_passwd; else return null; end if; end; ' LANGUAGE 'plpgsql' VOLATILE; -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Friday, April 09, 2004 8:02 AM To: Andrew Matthews Cc: [EMAIL PROTECTED] Subject: Re: [PERFORM] Wierd issues Andrew Matthews [EMAIL PROTECTED] writes: [ PG 7.3.4 much slower than 7.2.1 ] Both have same databases, Both have had vacume full ran on them. You did ANALYZE too, right? The bulk of the time is evidently going into the seqscan on users in each case: - Seq Scan on users u (cost=0.00..1938.51 rows=71283 width=4) (actual time=0.81..30119.58 rows=70809 loops=1) - Seq Scan on users u (cost=0.00..1888.85 rows=71548 width=4) (actual time=18.38..2277152.51 rows=71028 loops=1) Filter: (get_pwd(username, '127.0.0.1'::character varying, '101'::character varying, 'MD5'::character varying) IS NOT NULL) I have to suspect that the inefficiency is inside this get_pwd() function, but you didn't tell us anything about that... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PERFORM] Wierd issues
This is what I got Two servers, one debian, one fedora Debain dual 3ghz, 1 gig ram, ide, PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4 Fedora: Dual 3ghz, 1 gig ram, scsi, PostgreSQL 7.3.4-RH on i386-redhat-linux-gnu, compiled by GCC i386-redhat-linux-gcc (GCC) 3.3.2 20031022 (Red Hat Linux 3.3.2-1) Both have same databases, Both have had vacume full ran on them. Both doing the same query Select * from vpopmail; The vpopmail is a view, this is the view View vpopmail Column | Type | Modifiers ---++--- pw_name | character varying(32) | pw_domain | character varying(64) | pw_passwd | character varying | pw_uid | integer | pw_gid | integer | pw_gecos | character varying | pw_dir | character varying(160) | pw_shell | character varying(20) | View definition: SELECT ea.email_name AS pw_name, ea.domain AS pw_domain, get_pwd(u.username, '127.0.0.1'::varchar, '101'::varchar, 'MD5'::varchar) AS pw_passwd, 0 AS pw_uid, 0 AS pw_gid, ''::varchar AS pw_gecos, ei.directory AS pw_dir, ei.quota AS pw_shell FROM email_addresses ea, email_info ei, users u, user_resources ur WHERE (ea.user_resource_id = ei.user_resource_id) AND (get_pwd(u.username, '127.0.0.1'::varchar, '101'::varchar, 'MD5'::varchar) IS NOT NULL)) AND (ur.id = ei.user_resource_id)) AND (u.id = ur.user_id)) AND (NOT (EXISTS (SELECT forwarding.email_id FROM forwarding WHERE (forwarding.email_id = ea.id); Both are set to the same buffers and everything this is the execution time: Debian: Total runtime: 35594.81 msec Fedora: Total runtime: 2279869.08 msec Huge difference as you can see here are the pastes of the stuff Debain: user_acl=# explain analyze SELECT count(*) from vpopmail; NOTICE: QUERY PLAN: Aggregate (cost=438231.94..438231.94 rows=1 width=20) (actual time=35594.67..35594.67 rows=1 loops=1) - Hash Join (cost=434592.51..438142.51 rows=35774 width=20) (actual time=34319.24..35537.11 rows=70613 loops=1) - Seq Scan on email_info ei (cost=0.00..1721.40 rows=71640 width=4) (actual time=0.04..95.13 rows=71689 loops=1) - Hash (cost=434328.07..434328.07 rows=35776 width=16) (actual time=34319.00..34319.00 rows=0 loops=1) - Hash Join (cost=430582.53..434328.07 rows=35776 width=16) (actual time=2372.45..34207.21 rows=70613 loops=1) - Seq Scan on users u (cost=0.00..1938.51 rows=71283 width=4) (actual time=0.81..30119.58 rows=70809 loops=1) - Hash (cost=430333.64..430333.64 rows=35956 width=12) (actual time=2371.51..2371.51 rows=0 loops=1) - Hash Join (cost=2425.62..430333.64 rows=35956 width=12) (actual time=176.73..2271.14 rows=71470 loops=1) - Seq Scan on email_addresses ea (cost=0.00..426393.25 rows=35956 width=4) (actual time=0.06..627.49 rows=71473 loops=1) SubPlan - Index Scan using forwarding_idx on forwarding (cost=0.00..5.88 rows=1 width=4) (actual time=0.00..0.00 rows=0 loops=71960) - Hash (cost=1148.37..1148.37 rows=71637 width=8) (actual time=176.38..176.38 rows=0 loops=1) - Seq Scan on user_resources ur (cost=0.00..1148.37 rows=71637 width=8) (actual time=0.03..82.21 rows=71686 loops=1) Total runtime: 35594.81 msec EXPLAIN And for fedora its Aggregate (cost=416775.52..416775.52 rows=1 width=20) (actual time=2279868.57..2279868.58 rows=1 loops=1) - Hash Join (cost=413853.79..416686.09 rows=35772 width=20) (actual time=2279271.26..2279803.91 rows=70841 loops=1) Hash Cond: (outer.user_resource_id = inner.id) - Seq Scan on email_info ei (cost=0.00..1666.07 rows=71907 width=4) (actual time=8.12..171.10 rows=71907 loops=1) - Hash (cost=413764.36..413764.36 rows=35772 width=16) (actual time=2279263.03..2279263.03 rows=0 loops=1) - Hash Join (cost=410712.87..413764.36 rows=35772 width=16) (actual time=993.90..2279008.72 rows=70841 loops=1) Hash Cond: (outer.id = inner.user_id) - Seq Scan on users u (cost=0.00..1888.85 rows=71548 width=4) (actual time=18.38..2277152.51 rows=71028 loops=1) Filter: (get_pwd(username, '127.0.0.1'::character varying, '101'::character varying, 'MD5'::character varying) IS NOT NULL) - Hash (cost=410622.99..410622.99 rows=35952 width=12) (actual time=975.40..975.40 rows=0 loops=1) - Hash Join (cost=408346.51..410622.99 rows=35952 width=12) (actual time=507.52..905.91 rows=71697 loops=1) Hash Cond: (outer.id = inner.user_resource_id) - Seq Scan on user_resources ur (cost=0.00..1108.04 rows=71904 width=8) (actual time=0.05..95.65 rows=71904 loops=1) - Hash (cost=408256.29..408256.29 rows=36091 width=4) (actual time=507.33..507.33 rows=0 loops=1) - Seq Scan on email_addresses ea (cost=0.00..408256.29 rows=36091 width=4) (actual time=0.15..432.83 rows=71700 loops=1) Filter: (NOT (subplan)) SubPlan - Index Scan using forwarding_idx on forwarding (cost=0.00..5.63 rows=1 width=4) (actual time=0.00..0.00 rows=0 loops=72182) Index Cond: (email_id = $0) Total runtime: 2279869.08 msec (20 rows) Any suggestions? I cant figure this out. There is no
