Wierd issues - Mailing list pgsql-performance

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)