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@xxxxxxxxxxxxx> 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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
