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;
Limit (cost=30.28..30.28 rows=1 width=58) (actual time=0.19..0.19 rows=1
-> Sort (cost=30.28..30.30 rows=7 width=58) (actual time=0.18..0.18
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!
-- Csaba Együd
> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: 2004. június 28. 20:10
> 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-2?Q?Egy=FCd_Csaba?= <csegyud(at)vnet(dot)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.
In response to
pgsql-general by date
|Next:||From: Alvaro Herrera||Date: 2004-06-29 05:09:47|
|Subject: Re: How to determine field names in a trigger?|
|Previous:||From: Justin Clift||Date: 2004-06-29 03:59:11|
|Subject: How to determine field names in a trigger?|