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

Hi,
here is one of the stored procedures (the other is almost the same - queries
quantity instead of getup). I explain analyzed the queries called from the
stored procedures.
Thans.

bye,
  -- cs.

*********************************************************************
alumiltmp=# explain analyze select
round(get_stock_getup(234,1,'2004.06.28')::numeric,2);
NOTICE:  select date,time from t_stockchanges where stockid='1' and
productid='234' and date<='2004.06.28' and changeid=
1 order by time desc limit 1;
NOTICE:  select dir, sum(getup) as getup from (select getup, (select dir
from t_changes where id = changeid) as dir from
 t_stockchanges where productid='234' and stockid='1' and date>='2004.06.01
' and date<='2004.06.28' order by ti
me) as foo group by dir
                                     QUERY PLAN
----------------------------------------------------------------------------
--------
 Result  (cost=0.00..0.01 rows=1 width=0) (actual time=13.97..13.97 rows=1
loops=1)
 Total runtime: 13.99 msec
(2 rows)

*********************************************************************

alumiltmp=# EXPLAIN ANALYZE select date,time from t_stockchanges where
stockid='1' and productid='234' and date<='2004.
06.28' and changeid=1 order by time desc limit 1;
                                                                     QUERY
PLAN
----------------------------------------------------------------------------
--------------------------------------------
-----------------------------
 Limit  (cost=28.84..28.84 rows=1 width=46) (actual time=9.10..9.10 rows=1
loops=1)
   ->  Sort  (cost=28.84..28.86 rows=7 width=46) (actual time=9.10..9.10
rows=2 loops=1)
         Sort Key: "time"
         ->  Index Scan using t_stockchanges_fullindex on t_stockchanges
(cost=0.00..28.74 rows=7 width=46)
             (actual time=0.14..9.03 rows=6 loops=1)
               Index Cond: ((date <= '2004.06.28'::bpchar) AND (stockid = 1)
AND (productid = 234) AND (changeid = 1))
 Total runtime: 9.17 msec
(6 rows)

*********************************************************************

alumiltmp=# EXPLAIN ANALYZE select dir, sum(getup) as getup from (select
getup, (select dir from t_changes where id = c
hangeid) as dir from t_stockchanges where productid='234' and stockid='1'
and date>='2004.06.01         ' and date<='20
04.06.28' order by time) as foo group by dir;

QUERY PLAN

----------------------------------------------------------------------------
--------------------------------------------
----------------------------------------------
 Aggregate  (cost=6.92..6.93 rows=1 width=38) (actual time=1.63..1.65 rows=2
loops=1)
   ->  Group  (cost=6.92..6.92 rows=1 width=38) (actual time=1.62..1.63
rows=7 loops=1)
         ->  Sort  (cost=6.92..6.92 rows=1 width=38) (actual time=1.61..1.62
rows=7 loops=1)
               Sort Key: dir
               ->  Subquery Scan foo  (cost=6.90..6.91 rows=1 width=38)
(actual time=1.55..1.56 rows=7 loops=1)
                     ->  Sort  (cost=6.90..6.91 rows=1 width=38) (actual
time=1.55..1.55 rows=7 loops=1)
                           Sort Key: "time"
                           ->  Index Scan using t_stockchanges_fullindex on
t_stockchanges  (cost=0.00..6.89 rows=1
                               width=38) (actual time=0.07..1.52 rows=7
loops=1)
                                 Index Cond: ((date >= '2004.06.01
'::bpchar) AND (date <= '2004.06.28'::bpchar)
                                 AND (stockid = 1) AND (productid = 234))
                                 SubPlan
                                   ->  Seq Scan on t_changes
(cost=0.00..1.16 rows=1 width=5) (actual time=0.01..0.01
                                       rows=1 loops=7)
                                         Filter: (id = $0)
 Total runtime: 1.78 msec
(13 rows)


*********************************************************************

create or replace function "get_stock_getup" (int, int, text) returns
numeric as'
declare
  ProductID     alias for $1;
  StockID       alias for $2;
  ADate         alias for $3;

  OpenTime      text;
  q             text;
  R             record;
  retval        numeric;
begin
  OpenTime := '''';
  -- Megkeressük a termék utolsó nyitókészletét az adott raktárban. Ha
nincs,
  -- akkor a raktár elejétől kezdve dolgozzuk fel az adatokat.
  q := ''select date,time from t_stockchanges where ''    ||
          ''stockid=''   || quote_literal(StockID)   || '' and '' ||
          ''productid='' || quote_literal(ProductID) || '' and '' ||
          ''date<=''     || quote_literal(ADate)     || '' and '' ||
          ''changeid=1 order by time desc limit 1;'';
--  raise notice ''%'',q;
  for R in execute q loop
    OpenTime := R.date;
  end loop;
  --raise notice ''%'', OpenTime;

  -- Ha OpenTime is null, azaz nem volt nyitó, akkor az összes rekordot
visszakapjuk.
  retval := 0;
  q := ''select dir, sum(getup) as getup from (select getup, (select dir
from t_changes where id = changeid) as dir '' ||
       ''from t_stockchanges where productid='' || quote_literal(ProductID)
|| '' and '' ||
       ''stockid='' || quote_literal(StockID)   || '' and '' ||
       ''date>=''   || quote_literal(OpenTime)  || '' and date<=''   ||
quote_literal(ADate)  ||
       '' order by time) as foo group by dir'';
--  raise notice ''%'',q;

  for R in execute q loop
    if R.dir=''+'' then
      retval := retval + R.getup;
    end if;
    if R.dir=''-'' then
      retval := retval - R.getup;
    end if;
  end loop;

  return retval;
end;
'LANGUAGE 'plpgsql';


> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tom Lane
> Sent: 2004. június 28. 0:15
> To: csegyud@vnet.hu
> Cc: 'Alvaro Herrera'; 'Pgsql-General@Postgresql.Org (E-mail)'
> Subject: Re: [GENERAL] Performance problem on RH7.1
>
>
> =?iso-8859-1?Q?Egy=FCd_Csaba?= <csegyud@vnet.hu> writes:
> > It is strange that the laptop substantially faster then the
> server. The
> > get_stock* functions are executed 2-3 times faster.
>
> So what do those stored procedures do exactly?
>
> What it smells like to me is a bad plan for a query executed in one of
> the stored procedures, but it's hard to theorize with no data.
>
>             regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
> ---
> Incoming mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.712 / Virus Database: 468 - Release Date: 2004. 06. 27.
>

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