Hi Tom,
I did the modifications you suggested on the t_stockchanges_fullindex and
the result tells everthing:
---------
explain analyze select date,time from t_stockchanges where stockid='1' and
productid='234' and date<='2004.06.29' and changeid=1 order by stockid,
productid, changeid, date, time desc limit 1;
---------
QUERY PLAN
Limit (cost=30.28..30.28 rows=1 width=58) (actual time=0.19..0.19 rows=1
loops=1)
-> Sort (cost=30.28..30.30 rows=7 width=58) (actual time=0.18..0.18
rows=2 loops=1)
Sort Key: stockid, productid, changeid, date, "time"
-> Index Scan using t_stockchanges_fullindex on t_stockchanges
(cost=0.00..30.18 rows=7 width=58) (actual time=0.04..0.08 rows=6 loops=1)
Index Cond: ((stockid = 1) AND (productid = 234) AND (changeid
= 1) AND (date <= '2004.06.29'::bpchar))
Total runtime: 0.25 msec
( Compared to 9.17 msec !!!! 37 times faster! )
----------
Thank you wery much Tom! It was very kind of you!
Best regards,
-- Csaba Együd
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: 2004. június 28. 20:10
> To: csegyud@vnet.hu
> Cc: 'Alvaro Herrera'; 'Pgsql-General@Postgresql.Org (E-mail)'
> Subject: Re: [GENERAL] Performance problem on RH7.1
>
>
> =?iso-8859-2?Q?Egy=FCd_Csaba?= <csegyud@vnet.hu> writes:
> >> I'd also suggest dropping the EXECUTE approach, as this is
> costing you
> >> a re-plan on every call without buying much of anything.
>
> > Do you mean I should use PERFORM instead? Or what else?
> > Do you mean the "for R in execute" statements? How can I
> run a dynamic query
> > in other way?
>
> No, I mean the most straightforward way:
>
> for R in select ... where stockid = $1 and ...
>
> This lets plpgsql cache the plan for the SELECT.
>
> regards, tom lane
> ---
> 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.
