Performance problem on RH7.1 - Mailing list pgsql-general

From Együd Csaba
Subject Performance problem on RH7.1
Date
Msg-id 014e01c45b66$9ee8b860$230a0a0a@compaq
Whole thread Raw
Responses Re: Performance problem on RH7.1  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: Performance problem on RH7.1  ("Scott Marlowe" <smarlowe@qwest.net>)
Re: Performance problem on RH7.1  (Alvaro Herrera <alvherre@dcc.uchile.cl>)
List pgsql-general
Hi All,
I've a problem with the perfprmance of the production environment.
I've two db servers. One on my laptop computer (2Ghz, 1GB, WinXP, Cygwin,
Postgres 7.3.4) and one on a production server (2GHz, 1GB, Ultra SCSI,
RH7.1, Postgres 7.3.2).

I run the same dump and the same query on both of the computers. The
difference is substantial.
The query takes 5 times longer on the production server then on the laptop.

What can be the reason? Could anybody suggest me something?
Thakn you in advance.

Best regards,
  -- Csaba Együd


Kernel parameters on the linux server:
--------------------------------------
[root@db kernel]# pwd
/proc/sys/kernel
[root@db kernel]# cat shmall shmmax
134217728
134217728
[root@db kernel]#


The query:
----------
explain analyze select
  id, artnum, oldartnum, name, munitid, getupid, vtsz, vat, description,
getupquantity, minstock,
  (select count(*) from t_prices where t_prices.productid=t_products.id) as
pcount,
  round(get_stock(id,1)::numeric,2) as stockm,
  round(get_stock_getup(id,1)::numeric,2) as stockg,
  (select abbrev from t_munits where id=munitid) as munit,
  (select get_order_getup(id)) as deliverygetup,
  (select (select deliverydate from t_orders where id=orderid) as
deliverydate
   from t_orderdetails
   where productid=t_products.id and
         not (select delivered from t_orders where id=orderid) limit 1) as
deliverydate,
  (select abbrev from t_getups where id=getupid) as getup
from t_products
order by artnum;

QUERY PLAN on my laptop:
------------------------
Sort  (cost=70.17..72.38 rows=885 width=184) (actual time=7264.00..7264.00
rows=885 loops=1)
  Sort Key: artnum
  ->  Seq Scan on t_products  (cost=0.00..26.85 rows=885 width=184) (actual
time=21.00..7259.00 rows=885 loops=1)
        SubPlan
          ->  Aggregate  (cost=28.62..28.62 rows=1 width=0) (actual
time=0.12..0.12 rows=1 loops=885)
                ->  Index Scan using t_prices_productid on t_prices
(cost=0.00..28.60 rows=8 width=0) (actual time=0.05..0.10 rows=2 loops=885)
                      Index Cond: (productid = $0)
          ->  Seq Scan on t_munits  (cost=0.00..1.06 rows=1 width=32)
(actual time=0.02..0.02 rows=1 loops=885)
                Filter: (id = $1)
          ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual
time=1.13..1.13 rows=1 loops=885)
          ->  Limit  (cost=0.00..149.06 rows=1 width=4) (actual
time=0.09..0.09 rows=0 loops=885)
                ->  Seq Scan on t_orderdetails  (cost=0.00..149.06 rows=1
width=4) (actual time=0.08..0.08 rows=0 loops=885)
                      Filter: ((productid = $0) AND (NOT (subplan)))
                      SubPlan
                        ->  Seq Scan on t_orders  (cost=0.00..1.27 rows=1
width=14) (actual time=0.00..0.00 rows=1 loops=107)
                              Filter: (id = $2)
                        ->  Seq Scan on t_orders  (cost=0.00..1.27 rows=1
width=1) (actual time=0.02..0.03 rows=1 loops=107)
                              Filter: (id = $2)
          ->  Seq Scan on t_getups  (cost=0.00..1.16 rows=1 width=32)
(actual time=0.01..0.02 rows=1 loops=885)
                Filter: (id = $3)
Total runtime: 7265.00 msec

QUERY PLAN on the production server:
------------------------------------
Sort  (cost=70.17..72.38 rows=885 width=121) (actual time=36729.92..36730.18
rows=885 loops=1)
  Sort Key: artnum
  ->  Seq Scan on t_products  (cost=0.00..26.85 rows=885 width=121) (actual
time=45.16..36724.73 rows=885 loops=1)
        SubPlan
          ->  Aggregate  (cost=9.06..9.06 rows=1 width=0) (actual
time=0.15..0.15 rows=1 loops=885)
                ->  Index Scan using t_prices_productid on t_prices
(cost=0.00..9.05 rows=2 width=0) (actual time=0.12..0.14 rows=2 loops=885)
                      Index Cond: (productid = $0)
          ->  Seq Scan on t_munits  (cost=0.00..1.06 rows=1 width=5) (actual
time=0.04..0.04 rows=1 loops=885)
                Filter: (id = $1)
          ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual
time=0.80..0.80 rows=1 loops=885)
          ->  Limit  (cost=0.00..149.06 rows=1 width=4) (actual
time=0.08..0.08 rows=0 loops=885)
                ->  Seq Scan on t_orderdetails  (cost=0.00..149.06 rows=1
width=4) (actual time=0.07..0.08 rows=0 loops=885)
                      Filter: ((productid = $0) AND (NOT (subplan)))
                      SubPlan
                        ->  Seq Scan on t_orders  (cost=0.00..1.27 rows=1
width=14) (actual time=0.01..0.02 rows=1 loops=107)
                              Filter: (id = $2)
                        ->  Seq Scan on t_orders  (cost=0.00..1.27 rows=1
width=1) (actual time=0.01..0.02 rows=1 loops=107)
                              Filter: (id = $2)
          ->  Seq Scan on t_getups  (cost=0.00..1.16 rows=1 width=11)
(actual time=0.03..0.04 rows=1 loops=885)
                Filter: (id = $3)
Total runtime: 36730.67 msec


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.707 / Virus Database: 463 - Release Date: 2004. 06. 15.



pgsql-general by date:

Previous
From: spied@yandex.ru
Date:
Subject: Re: strange apllicaion error
Next
From: Tom Lane
Date:
Subject: Re: Performance problem on RH7.1