Re: Major performance problem after upgrade from 8.3 to 8.4

From: Gerhard Wiesinger <lists(at)wiesinger(dot)com>
To: Merlin Moncure <mmoncure(at)gmail(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 06:07:18
Message-ID: alpine.LFD.2.01.1009140759160.24945@bbs.intern
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Ciao,
Gerhard

--
http://www.wiesinger.com/

On Mon, 13 Sep 2010, Merlin Moncure wrote:

> On Mon, Sep 13, 2010 at 2:39 AM, Gerhard Wiesinger <lists(at)wiesinger(dot)com> wrote:
>> Hello,
>>
>> Any news or ideas regarding this issue?
>
> hm. is retooling the query an option? specifically, can you try converting
>
> CREATE OR REPLACE VIEW log_entries AS
> SELECT
> l.id AS id,
> l.datetime AS datetime,
> l.tdate AS tdate,
> l.ttime AS ttime,
> d1.value AS Raumsolltemperatur,
> [...]
> FROM
> log l
> LEFT JOIN log_details d1 ON l.id = d1.fk_id AND
> d1.fk_keyid = (SELECT keyid FROM key_description WHERE description =
> 'Raumsolltemperatur')
> [...]
>
> to
>
> CREATE OR REPLACE VIEW log_entries AS
> SELECT
> l.id AS id,
> l.datetime AS datetime,
> l.tdate AS tdate,
> l.ttime AS ttime,
> (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') AS Raumsolltemperatur,
> [...]
>
> (I am not 100% sure I have your head around your query, but I think I do)?
> This should get you a guaranteed (although not necessarily 'the best'
> plan, with each returned view column being treated independently of
> the other (is that what you want?). Also, if schema changes are under
> consideration, you can play log_details/key_description, using natural
> key and cut out one of the joins. I can't speak to some of the more
> complex planner issues at play, but your query absolutely screams
> optimization at the SQL level.
>
> What I am 100% sure of, is that you can get better performance if you
> do a little out of the box thinking here...
>
> merlin
>

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Heikki Linnakangas 2010-09-14 07:10:37 Re: Useless sort by
Previous Message Alvaro Herrera 2010-09-14 03:57:05 Re: Where does data in pg_stat_user_tables come from?