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 can't 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 it's

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)