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-28 17:53:13
Message-ID: 003601c45d38$c8e48df0$230a0a0a@compaq (view raw, whole thread or download thread mbox)
Lists: 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...

  -- Csaba Együd

Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (
Version: 6.0.712 / Virus Database: 468 - Release Date: 2004. 06. 27.

In response to


pgsql-general by date

Next:From: Tom LaneDate: 2004-06-28 18:04:59
Subject: Re: Inconsistant DOW...
Previous:From: Alvaro HerreraDate: 2004-06-28 17:29:03
Subject: Re: Plperlu and sending emails, is it safe?

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