Skip site navigation (1) Skip section navigation (2)

Re: Performance problem on RH7.1

From: Együd Csaba <csegyud(at)vnet(dot)hu>
To: 'Tom Lane' <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: 'Alvaro Herrera' <alvherre(at)dcc(dot)uchile(dot)cl>,"'Pgsql-General(at)Postgresql(dot)Org (E-mail)'" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Performance problem on RH7.1
Date: 2004-06-29 04:52:56
Message-ID: 003d01c45d94$f32fb6f0$230a0a0a@compaq (view raw or flat)
Thread:
Lists: pgsql-general
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(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

Responses

pgsql-general by date

Next:From: Alvaro HerreraDate: 2004-06-29 05:09:47
Subject: Re: How to determine field names in a trigger?
Previous:From: Justin CliftDate: 2004-06-29 03:59:11
Subject: How to determine field names in a trigger?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group