Re: Performance problem on RH7.1

=?iso-8859-2?Q?Egy=FCd_Csaba?= <csegyud(at)vnet(dot)hu> writes:
> 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.

The major time sink is clearly here:

> -> 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))

and I think the problem is you've not chosen the index very well. Using
date as the most significant index column is simply the wrong way to do
this query --- the thing is going to start at the beginning of time and
scan forward over *all* index entries until it reaches a date greater
than the cutoff. What you want is date as the least significant index
column, so that you don't have to scan entries for irrelevant stocks at
all. Also you should probably put time into the index (in fact, why do
you have separate date and time fields at all?). What you really want
here is an index on (stockid, productid, changeid, date, time) and to
get a backwards indexscan with no sort step. It'd have to look like

where stockid='1' and productid='234' and changeid=1 and date<='2004.06.28'
order by stockid desc, productid desc, changeid desc, date desc, time desc
limit 1

I'd also suggest dropping the EXECUTE approach, as this is costing you
a re-plan on every call without buying much of anything.

A larger issue is whether you shouldn't forego the stored procedures
entirely and convert the whole problem into a join. The way you are
doing things now is essentially a forced nested-loop join between the
table traversed by the outer query and the table examined by the stored
procedures. Nested-loop is often the least efficient way to do a join.
But that could get pretty messy notationally, and I'm not sure how much
win there would be.

regards, tom lane