Re: Performance problem on RH7.1

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(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Tom Lane
> Sent: 2004. június 28. 0:15
> To: csegyud(at)vnet(dot)hu
> Cc: 'Alvaro Herrera'; 'Pgsql-General(at)Postgresql(dot)Org (E-mail)'
> Subject: Re: [GENERAL] Performance problem on RH7.1
>
>
> =?iso-8859-1?Q?Egy=FCd_Csaba?= <csegyud(at)vnet(dot)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.