Re: Performance problem on RH7.1 - Mailing list pgsql-general

> 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))
Yes, it must be there.

>
> 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
You are right. I haven't thought about this yet, and to tell the truth this
index is a "left there" index from the early development times. I didn't
review that since I had made it.

> --- 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
It is a good idea and I will do it in this way.

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

>
> 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.
I use stored procedures because it is clearer and simpler way then always
writing big complex queries with a lot of joins etc. I know that it has it's
price as well.
On the other hand you have lit up something in my mind so I will think about
it seriosly. I wish I have some time to do so...

Bye,
  -- Csaba Együd

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