Re: Major performance problem after upgrade from 8.3 to 8.4

pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed

From: Gerhard Wiesinger <lists@wiesinger.com>
To: Merlin Moncure <mmoncure@gmail.com>
Cc: pgsql-performance@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
Cc: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Andreas Kretschmer <akretschmer@spamfence.net>
Subject: Re: Major performance problem after upgrade from 8.3 to 8.4
Date: Tue, 14 Sep 2010 08:07:18 +0200 (CEST)
Message-ID: <alpine.LFD.2.01.1009140759160.24945@bbs.intern> (raw)
In-Reply-To: <AANLkTim5+e9C2qPfPyS-z3qaYGTHhtC3Zo2jEJwSo+K+@mail.gmail.com>
References: <alpine.LFD.2.01.1008300802300.4121@bbs.intern>
	<alpine.LFD.2.01.1008301808330.31277@bbs.intern>
	<11111.1283185373@sss.pgh.pa.us>
	<alpine.LFD.2.01.1008301838480.4133@bbs.intern>
	<12837.1283186981@sss.pgh.pa.us>
	<alpine.LFD.2.01.1009020749380.19625@bbs.intern>
	<21281.1283433278@sss.pgh.pa.us>
	<alpine.LFD.2.01.1009030702520.12651@bbs.intern>
	<22001.1283523290@sss.pgh.pa.us>
	<alpine.LFD.2.01.1009031837550.28047@bbs.intern>
	<24994.1283532424@sss.pgh.pa.us>
	<alpine.LFD.2.01.1009031853400.31084@bbs.intern>
	<25560.1283534215@sss.pgh.pa.us>
	<alpine.LFD.2.01.1009031952390.8416@bbs.intern>
	<26620.1283537362@sss.pgh.pa.us>
	<alpine.LFD.2.01.1009032014270.11936@bbs.intern>
	<27253.1283539468@sss.pgh.pa.us>
	<alpine.LFD.2.01.1009032129590.23544@bbs.intern>
	<29799.1283548250@sss.pgh.pa.us>
	<alpine.LFD.2.01.1009040850110.26391@bbs.intern>
	<alpine.LFD.2.01.1009130838530.12288@bbs.intern>
	<AANLkTim5+e9C2qPfPyS-z3qaYGTHhtC3Zo2jEJwSo+K+@mail.gmail.com>

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

view thread (39+ messages)  latest in thread

Message-ID: <alpine.LFD.2.01.1009140759160.24945@bbs.intern>
Permalink:  ../alpine.LFD.2.01.1009140759160.24945@bbs.intern/
Also on:    postgresql.org/message-id/alpine.LFD.2.01.1009140759160.24945@bbs.intern

 ·

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: lists@wiesinger.com, mmoncure@gmail.com, tgl@sss.pgh.pa.us, pavel.stehule@gmail.com, akretschmer@spamfence.net
  Subject: Re: Major performance problem after upgrade from 8.3 to 8.4
  In-Reply-To: <alpine.LFD.2.01.1009140759160.24945@bbs.intern>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox