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

Re: Major performance problem after upgrade from 8.3 to 8.4

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Gerhard Wiesinger <lists(at)wiesinger(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, Andreas Kretschmer <akretschmer(at)spamfence(dot)net>
Subject: Re: Major performance problem after upgrade from 8.3 to 8.4
Date: 2010-09-14 12:01:05
Message-ID: AANLkTi=DMvV7J5UyYKaGyME1zKmesAZrmTZAkyJv37L_@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Tue, Sep 14, 2010 at 2:07 AM, Gerhard Wiesinger <lists(at)wiesinger(dot)com> wrote:
> Hello Merlin,
>
> Seems to be a feasible approach. On problem which might be that when
> multiple rows are returned that they are not ordered in each subselect
> correctly. Any idea to solve that?
>
> e.g.
> Raumsolltemperatur | Raumisttemperatur
> Value from time 1  | Value from time 2
> Value from time 2  | Value from time 1
>
> but should be
> Raumsolltemperatur | Raumisttemperatur
> Value from time 1  | Value from time 1
> Value from time 2  | Value from time 2
>
> But that might be solveable by first selecting keys from the log_details
> table and then join again.
>
> I will try it in the evening and I have to think about in detail.
>
> But thank you for the new approach and opening the mind :-)

Using subquery in that style select (<subquery>), ... is limited to
results that return 1 row, 1 column.  I assumed that was the case...if
it isn't in your view, you can always attempt arrays:

CREATE OR REPLACE VIEW log_entries AS
SELECT
 l.id AS id,
 l.datetime AS datetime,
 l.tdate AS tdate,
 l.ttime AS ttime,
 array(select value from log_details ld join key_description kd on
ld.fk_keyid = kd.keyid where ld.fk_id = l.id and  description =
'Raumsolltemperatur' order by XYZ) AS Raumsolltemperatur,
 [...]

arrays might raise the bar somewhat in terms of dealing with the
returned data, or they might work great.  some experimentation is in
order.

XYZ being the ordering condition you want.  If that isn't available
inside the join then we need to think about this some more.  We could
probably help more if you could describe the schema in a little more
detail.  This is solvable.

merlin

In response to

Responses

pgsql-performance by date

Next:From: Maciek SakrejdaDate: 2010-09-14 15:09:18
Subject: Re: Useless sort by
Previous:From: Heikki LinnakangasDate: 2010-09-14 07:10:37
Subject: Re: Useless sort by

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