Skip site navigation (1) Skip section navigation (2)

Wierd issues

From: "Andrew Matthews" <andrew(dot)m(at)corp(dot)dslextreme(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Wierd issues
Date: 2004-07-15 19:35:23
Message-ID: 79AEF92046759442A4AA04C2AB08C0B814F955@exchange.corp.dslextreme.com (view raw or flat)
Thread:
Lists: 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)

pgsql-performance by date

Next:From: Steinar H. GundersonDate: 2004-07-15 22:16:12
Subject: Re: Odd sorting behaviour
Previous:From: Josh BerkusDate: 2004-07-15 18:11:33
Subject: Re: Odd sorting behaviour

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group