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