Re: Major performance problem after upgrade from 8.3 to 8.4

On Tue, 14 Sep 2010, Merlin Moncure wrote:

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

Of course, subquery is limited to a result set returning 1 row and 1
column. Also order is of course preserved because of the join.

Further, I think I found a perfect query plan for the EAV pattern.

First I tried your suggestion but there were some limitation with O(n^2)
efforts (e.g. nested loops=12586 and also index scans with loop 12586):

CREATE OR REPLACE VIEW log_entries_test AS
SELECT
l.id AS id,
l.datetime AS datetime,
l.tdate AS tdate,
l.ttime AS ttime,
(SELECT value FROM log_details d JOIN key_description kd ON d.fk_keyid = kd.keyid WHERE l.id = d.fk_id AND kd.description = 'Raumsolltemperatur') AS Raumsolltemperatur,
(SELECT value FROM log_details d JOIN key_description kd ON d.fk_keyid = kd.keyid WHERE l.id = d.fk_id AND kd.description = 'Raumtemperatur') AS Raumtemperatur,
(SELECT value FROM log_details d JOIN key_description kd ON d.fk_keyid = kd.keyid WHERE l.id = d.fk_id AND kd.description = 'Kesselsolltemperatur') AS Kesselsolltemperatur,
(SELECT value FROM log_details d JOIN key_description kd ON d.fk_keyid = kd.keyid WHERE l.id = d.fk_id AND kd.description = 'Kesseltemperatur') AS Kesseltemperatur,
....
FROM
log l
;

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAIN ANALYZE SELECT * FROM log_entries_test WHERE datetime > now() - INTERVAL '10 days' ORDER BY datetime DESC;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan Backward using i_log_unique on log l (cost=0.00..140820.12 rows=69 width=32) (actual time=2.848..22812.331 rows=12586 loops=1)
Index Cond: (datetime > (now() - '10 days'::interval))
SubPlan 1
-> Nested Loop (cost=0.00..19.99 rows=1 width=8) (actual time=0.007..0.018 rows=1 loops=12586)
-> Seq Scan on key_description kd (cost=0.00..2.38 rows=1 width=8) (actual time=0.003..0.013 rows=1 loops=12586)
Filter: ((description)::text = 'Raumsolltemperatur'::text)
-> Index Scan using unique_key_and_id on log_details d (cost=0.00..17.60 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=12586)
Index Cond: (($0 = d.fk_id) AND (d.fk_keyid = kd.keyid))
SubPlan 2
-> Nested Loop (cost=0.00..19.99 rows=1 width=8) (actual time=0.006..0.017 rows=1 loops=12586)
-> Seq Scan on key_description kd (cost=0.00..2.38 rows=1 width=8) (actual time=0.003..0.013 rows=1 loops=12586)
Filter: ((description)::text = 'Raumtemperatur'::text)
-> Index Scan using unique_key_and_id on log_details d (cost=0.00..17.60 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=12586)
Index Cond: (($0 = d.fk_id) AND (d.fk_keyid = kd.keyid))
SubPlan 3
-> Nested Loop (cost=0.00..19.99 rows=1 width=8) (actual time=0.005..0.017 rows=1 loops=12586)
-> Seq Scan on key_description kd (cost=0.00..2.38 rows=1 width=8) (actual time=0.002..0.013 rows=1 loops=12586)
Filter: ((description)::text = 'Kesselsolltemperatur'::text)
-> Index Scan using unique_key_and_id on log_details d (cost=0.00..17.60 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=12586)
Index Cond: (($0 = d.fk_id) AND (d.fk_keyid = kd.keyid))
SubPlan 4
-> Nested Loop (cost=0.00..19.99 rows=1 width=8) (actual time=0.006..0.017 rows=1 loops=12586)
-> Seq Scan on key_description kd (cost=0.00..2.38 rows=1 width=8) (actual time=0.002..0.013 rows=1 loops=12586)
Filter: ((description)::text = 'Kesseltemperatur'::text)
-> Index Scan using unique_key_and_id on log_details d (cost=0.00..17.60 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=12586)
Index Cond: (($0 = d.fk_id) AND (d.fk_keyid = kd.keyid))
SubPlan 5
-> Nested Loop (cost=0.00..19.99 rows=1 width=8) (actual time=0.005..0.017 rows=1 loops=12586)
-> Seq Scan on key_description kd (cost=0.00..2.38 rows=1 width=8) (actual time=0.002..0.014 rows=1 loops=12586)
Filter: ((description)::text = 'Speichersolltemperatur'::text)
-> Index Scan using unique_key_and_id on log_details d (cost=0.00..17.60 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=12586)
Index Cond: (($0 = d.fk_id) AND (d.fk_keyid = kd.keyid))
SubPlan 6
-> Nested Loop (cost=0.00..19.99 rows=1 width=8) (actual time=0.006..0.017 rows=1 loops=12586)
-> Seq Scan on key_description kd (cost=0.00..2.38 rows=1 width=8) (actual time=0.003..0.013 rows=1 loops=12586)
Filter: ((description)::text = 'Speichertemperatur'::text)
-> Index Scan using unique_key_and_id on log_details d (cost=0.00..17.60 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=12586)
Index Cond: (($0 = d.fk_id) AND (d.fk_keyid = kd.keyid))
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Therefore I optimized the query further which can be done in the
following way with another subquery and IHMO a perfect query plan. Also
the subselect avoid multiple iterations for each of the result rows:

CREATE OR REPLACE VIEW log_entries_test AS
SELECT
l.id AS id,
l.datetime AS datetime,
l.tdate AS tdate,
l.ttime AS ttime,
(SELECT value FROM log_details d WHERE l.id = d.fk_id AND d.fk_keyid = (SELECT keyid FROM key_description WHERE description = 'Raumsolltemperatur')) AS Raumsolltemperatur,
(SELECT value FROM log_details d WHERE l.id = d.fk_id AND d.fk_keyid = (SELECT keyid FROM key_description WHERE description = 'Raumtemperatur')) AS Raumtemperatur,
(SELECT value FROM log_details d WHERE l.id = d.fk_id AND d.fk_keyid = (SELECT keyid FROM key_description WHERE description = 'Kesselsolltemperatur')) AS Kesselsolltemperatur,
(SELECT value FROM log_details d WHERE l.id = d.fk_id AND d.fk_keyid = (SELECT keyid FROM key_description WHERE description = 'Kesseltemperatur')) AS Kesseltemperatur,
...
FROM
log l
;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAIN ANALYZE SELECT * FROM log_entries_test WHERE datetime > now() - INTERVAL '10 days' ORDER BY datetime DESC;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan Backward using i_log_unique on log l (cost=0.00..140603.99 rows=69 width=32) (actual time=2.588..5602.899 rows=12586 loops=1)
Index Cond: (datetime > (now() - '10 days'::interval))
SubPlan 2
-> Index Scan using unique_key_and_id on log_details d (cost=2.38..19.97 rows=1 width=8) (actual time=0.010..0.011 rows=1 loops=12586)
Index Cond: (($1 = fk_id) AND (fk_keyid = $0))
InitPlan 1 (returns $0)
-> Seq Scan on key_description (cost=0.00..2.38 rows=1 width=8) (actual time=0.015..0.066 rows=1 loops=1)
Filter: ((description)::text = 'Raumsolltemperatur'::text)
SubPlan 4
-> Index Scan using unique_key_and_id on log_details d (cost=2.38..19.97 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=12586)
Index Cond: (($1 = fk_id) AND (fk_keyid = $2))
InitPlan 3 (returns $2)
-> Seq Scan on key_description (cost=0.00..2.38 rows=1 width=8) (actual time=0.009..0.020 rows=1 loops=1)
Filter: ((description)::text = 'Raumtemperatur'::text)
SubPlan 6
-> Index Scan using unique_key_and_id on log_details d (cost=2.38..19.97 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=12586)
Index Cond: (($1 = fk_id) AND (fk_keyid = $3))
InitPlan 5 (returns $3)
-> Seq Scan on key_description (cost=0.00..2.38 rows=1 width=8) (actual time=0.005..0.017 rows=1 loops=1)
Filter: ((description)::text = 'Kesselsolltemperatur'::text)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

BTW: Schemadata is in the links discussed in the thread

Thnx to all for helping me.

Ciao,
Gerhard

--
http://www.wiesinger.com/
>From pgsql-performance-owner(at)postgresql(dot)org Tue Sep 14 18:05:33 2010
Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
by mail.postgresql.org (Postfix) with ESMTP id 2C6B01337BA8
for <pgsql-performance-postgresql(dot)org(at)mail(dot)postgresql(dot)org>; Tue, 14 Sep 2010 18:05:32 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024)
with ESMTP id 33160-03
for <pgsql-performance-postgresql(dot)org(at)mail(dot)postgresql(dot)org>;
Tue, 14 Sep 2010 21:05:23 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-ey0-f174.google.com (mail-ey0-f174.google.com [209.85.215.174])
by mail.postgresql.org (Postfix) with ESMTP id 77CB91337BCE
for <pgsql-performance(at)postgresql(dot)org>; Tue, 14 Sep 2010 18:05:22 -0300 (ADT)
Received: by eyb6 with SMTP id 6so3359037eyb.19
for <pgsql-performance(at)postgresql(dot)org>; Tue, 14 Sep 2010 14:05:22 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
d=gmail.com; s=gamma;
h=domainkey-signature:mime-version:received:received:in-reply-to
:references:date:message-id:subject:from:to:cc:content-type
:content-transfer-encoding;
bh=ph7bnTo2Hx2D6BtOmOSqMxqmAn1RFjTjcqy8rJcQ8wc=;
b=aJ4khtf9+vtlRti6BefsbdwACEVN1kB49+U3AIX14ryyt+BzXBTRVbMLD13xU2ywvS
cZl6FdJH1Am5n0+7YSlJFjcSlPZ6h1xrIL9Rz4aCT6B3tlwICcVcIObwQej1f1nN6f4g
YbhdZl3NfYiAt8gbqnCit3qDBTCaVYw/iOrJA=
DomainKey-Signature: a=rsa-sha1; c=nofws;
d=gmail.com; s=gamma;
h=mime-version:in-reply-to:references:date:message-id:subject:from:to
:cc:content-type:content-transfer-encoding;
b=kL5l2BR3mBVvr5czOS+zOyUVPh10JLA+hOBCL5dc/9UDDfKUXcij1LyDT0MhkevFsq
zo+7zzlyTcvgEFUIJVfzeJMv85CVHZPc7iCclYJj6rbcfU7JFYLWqVn99dSQD0xwlv/b
NkFKrBUUrc55AA4PFooiWRkYaVAMxQq/Ghhro=
MIME-Version: 1.0
Received: by 10.239.135.2 with SMTP id b2mr40462hbb.21.1284498321965; Tue, 14
Sep 2010 14:05:21 -0700 (PDT)
Received: by 10.239.136.142 with HTTP; Tue, 14 Sep 2010 14:05:21 -0700 (PDT)
In-Reply-To: <alpine(dot)LFD(dot)2(dot)01(dot)1009142139290(dot)22995(at)bbs(dot)intern>
References: <alpine(dot)LFD(dot)2(dot)01(dot)1008300802300(dot)4121(at)bbs(dot)intern>
<12837(dot)1283186981(at)sss(dot)pgh(dot)pa(dot)us>
<alpine(dot)LFD(dot)2(dot)01(dot)1009020749380(dot)19625(at)bbs(dot)intern>
<21281(dot)1283433278(at)sss(dot)pgh(dot)pa(dot)us>
<alpine(dot)LFD(dot)2(dot)01(dot)1009030702520(dot)12651(at)bbs(dot)intern>
<22001(dot)1283523290(at)sss(dot)pgh(dot)pa(dot)us>
<alpine(dot)LFD(dot)2(dot)01(dot)1009031837550(dot)28047(at)bbs(dot)intern>
<24994(dot)1283532424(at)sss(dot)pgh(dot)pa(dot)us>
<alpine(dot)LFD(dot)2(dot)01(dot)1009031853400(dot)31084(at)bbs(dot)intern>
<25560(dot)1283534215(at)sss(dot)pgh(dot)pa(dot)us>
<alpine(dot)LFD(dot)2(dot)01(dot)1009031952390(dot)8416(at)bbs(dot)intern>
<26620(dot)1283537362(at)sss(dot)pgh(dot)pa(dot)us>
<alpine(dot)LFD(dot)2(dot)01(dot)1009032014270(dot)11936(at)bbs(dot)intern>
<27253(dot)1283539468(at)sss(dot)pgh(dot)pa(dot)us>
<alpine(dot)LFD(dot)2(dot)01(dot)1009032129590(dot)23544(at)bbs(dot)intern>
<29799(dot)1283548250(at)sss(dot)pgh(dot)pa(dot)us>
<alpine(dot)LFD(dot)2(dot)01(dot)1009040850110(dot)26391(at)bbs(dot)intern>
<alpine(dot)LFD(dot)2(dot)01(dot)1009130838530(dot)12288(at)bbs(dot)intern>
<AANLkTim5+e9C2qPfPyS-z3qaYGTHhtC3Zo2jEJwSo+K+(at)mail(dot)gmail(dot)com>
<alpine(dot)LFD(dot)2(dot)01(dot)1009140759160(dot)24945(at)bbs(dot)intern>
<AANLkTi=DMvV7J5UyYKaGyME1zKmesAZrmTZAkyJv37L_(at)mail(dot)gmail(dot)com>
<alpine(dot)LFD(dot)2(dot)01(dot)1009142139290(dot)22995(at)bbs(dot)intern>
Date: Tue, 14 Sep 2010 17:05:21 -0400
Message-ID: <AANLkTim2srP5bSy68iDgrS5HvH9p3Obf-qexmxB2tBUc(at)mail(dot)gmail(dot)com>
Subject: 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>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9
X-Spam-Level:
X-Archive-Number: 201009/81
X-Sequence-Number: 40284

On Tue, Sep 14, 2010 at 3:59 PM, Gerhard Wiesinger <lists(at)wiesinger(dot)com> wr=
ote:
> On Tue, 14 Sep 2010, Merlin Moncure wrote:
>
>> 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 =A0| Value from time 2
>>> Value from time 2 =A0| Value from time 1
>>>
>>> but should be
>>> Raumsolltemperatur | Raumisttemperatur
>>> Value from time 1 =A0| Value from time 1
>>> Value from time 2 =A0| Value from time 2
>>>
>>> But that might be solveable by first selecting keys from the log_detail=
s
>>> 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. =A0I 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 =3D kd.keyid where ld.fk_id =3D l.id and =A0description =3D
>> '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. =A0some experimentation is in
>> order.
>>
>> XYZ being the ordering condition you want. =A0If that isn't available
>> inside the join then we need to think about this some more. =A0We could
>> probably help more if you could describe the schema in a little more
>> detail. =A0This is solvable.
>
> Of course, subquery is limited to a result set returning 1 row and 1 colu=
mn.
> Also order is of course preserved because of the join.
>
> Further, I think I found a perfect query plan for the EAV pattern.
>
> First I tried your suggestion but there were some limitation with O(n^2)
> efforts (e.g. nested loops=3D12586 and also index scans with loop 12586):
>
> CREATE OR REPLACE VIEW log_entries_test AS
> SELECT
> =A0l.id AS id,
> =A0l.datetime AS datetime,
> =A0l.tdate AS tdate,
> =A0l.ttime AS ttime,
> =A0(SELECT value FROM log_details d JOIN key_description kd ON d.fk_keyid=
=3D
> kd.keyid WHERE l.id =3D d.fk_id AND kd.description =3D 'Raumsolltemperatu=
r') AS
> Raumsolltemperatur,
> =A0(SELECT value FROM log_details d JOIN key_description kd ON d.fk_keyid=
=3D
> kd.keyid WHERE l.id =3D d.fk_id AND kd.description =3D 'Raumtemperatur') =
AS
> Raumtemperatur,
> =A0(SELECT value FROM log_details d JOIN key_description kd ON d.fk_keyid=
=3D
> kd.keyid WHERE l.id =3D d.fk_id AND kd.description =3D 'Kesselsolltempera=
tur')
> AS Kesselsolltemperatur,
> =A0(SELECT value FROM log_details d JOIN key_description kd ON d.fk_keyid=
=3D
> kd.keyid WHERE l.id =3D d.fk_id AND kd.description =3D 'Kesseltemperatur'=
) AS
> Kesseltemperatur,
> ....
> FROM
> =A0log l
> ;
>
> -------------------------------------------------------------------------=
---------------------------------------------------------------------------=
--------------------------------------------------------------
> EXPLAIN ANALYZE SELECT * FROM log_entries_test WHERE datetime > now() -
> INTERVAL '10 days' ORDER BY datetime DESC;
> -------------------------------------------------------------------------=
---------------------------------------------------------------------------=
--------------------------------------------------------------
> Index Scan Backward using i_log_unique on log l =A0(cost=3D0.00..140820.1=
2
> rows=3D69 width=3D32) (actual time=3D2.848..22812.331 rows=3D12586 loops=
=3D1)
> =A0Index Cond: (datetime > (now() - '10 days'::interval))
> =A0SubPlan 1
> =A0 =A0-> =A0Nested Loop =A0(cost=3D0.00..19.99 rows=3D1 width=3D8) (actu=
al
> time=3D0.007..0.018 rows=3D1 loops=3D12586)
> =A0 =A0 =A0 =A0 =A0-> =A0Seq Scan on key_description kd =A0(cost=3D0.00..=
2.38 rows=3D1
> width=3D8) (actual time=3D0.003..0.013 rows=3D1 loops=3D12586)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((description)::text =3D 'Raumsoll=
temperatur'::text)
> =A0 =A0 =A0 =A0 =A0-> =A0Index Scan using unique_key_and_id on log_detail=
s d
> =A0(cost=3D0.00..17.60 rows=3D1 width=3D16) (actual time=3D0.004..0.004 r=
ows=3D1
> loops=3D12586)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Index Cond: (($0 =3D d.fk_id) AND (d.fk_ke=
yid =3D kd.keyid))
> =A0SubPlan 2
> =A0 =A0-> =A0Nested Loop =A0(cost=3D0.00..19.99 rows=3D1 width=3D8) (actu=
al
> time=3D0.006..0.017 rows=3D1 loops=3D12586)
> =A0 =A0 =A0 =A0 =A0-> =A0Seq Scan on key_description kd =A0(cost=3D0.00..=
2.38 rows=3D1
> width=3D8) (actual time=3D0.003..0.013 rows=3D1 loops=3D12586)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((description)::text =3D 'Raumtemp=
eratur'::text)
> =A0 =A0 =A0 =A0 =A0-> =A0Index Scan using unique_key_and_id on log_detail=
s d
> =A0(cost=3D0.00..17.60 rows=3D1 width=3D16) (actual time=3D0.002..0.003 r=
ows=3D1
> loops=3D12586)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Index Cond: (($0 =3D d.fk_id) AND (d.fk_ke=
yid =3D kd.keyid))
> =A0SubPlan 3
> =A0 =A0-> =A0Nested Loop =A0(cost=3D0.00..19.99 rows=3D1 width=3D8) (actu=
al
> time=3D0.005..0.017 rows=3D1 loops=3D12586)
> =A0 =A0 =A0 =A0 =A0-> =A0Seq Scan on key_description kd =A0(cost=3D0.00..=
2.38 rows=3D1
> width=3D8) (actual time=3D0.002..0.013 rows=3D1 loops=3D12586)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((description)::text =3D 'Kesselso=
lltemperatur'::text)
> =A0 =A0 =A0 =A0 =A0-> =A0Index Scan using unique_key_and_id on log_detail=
s d
> =A0(cost=3D0.00..17.60 rows=3D1 width=3D16) (actual time=3D0.003..0.003 r=
ows=3D1
> loops=3D12586)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Index Cond: (($0 =3D d.fk_id) AND (d.fk_ke=
yid =3D kd.keyid))
> =A0SubPlan 4
> =A0 =A0-> =A0Nested Loop =A0(cost=3D0.00..19.99 rows=3D1 width=3D8) (actu=
al
> time=3D0.006..0.017 rows=3D1 loops=3D12586)
> =A0 =A0 =A0 =A0 =A0-> =A0Seq Scan on key_description kd =A0(cost=3D0.00..=
2.38 rows=3D1
> width=3D8) (actual time=3D0.002..0.013 rows=3D1 loops=3D12586)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((description)::text =3D 'Kesselte=
mperatur'::text)
> =A0 =A0 =A0 =A0 =A0-> =A0Index Scan using unique_key_and_id on log_detail=
s d
> =A0(cost=3D0.00..17.60 rows=3D1 width=3D16) (actual time=3D0.002..0.003 r=
ows=3D1
> loops=3D12586)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Index Cond: (($0 =3D d.fk_id) AND (d.fk_ke=
yid =3D kd.keyid))
> =A0SubPlan 5
> =A0 =A0-> =A0Nested Loop =A0(cost=3D0.00..19.99 rows=3D1 width=3D8) (actu=
al
> time=3D0.005..0.017 rows=3D1 loops=3D12586)
> =A0 =A0 =A0 =A0 =A0-> =A0Seq Scan on key_description kd =A0(cost=3D0.00..=
2.38 rows=3D1
> width=3D8) (actual time=3D0.002..0.014 rows=3D1 loops=3D12586)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((description)::text =3D
> 'Speichersolltemperatur'::text)
> =A0 =A0 =A0 =A0 =A0-> =A0Index Scan using unique_key_and_id on log_detail=
s d
> =A0(cost=3D0.00..17.60 rows=3D1 width=3D16) (actual time=3D0.002..0.003 r=
ows=3D1
> loops=3D12586)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Index Cond: (($0 =3D d.fk_id) AND (d.fk_ke=
yid =3D kd.keyid))
> =A0SubPlan 6
> =A0 =A0-> =A0Nested Loop =A0(cost=3D0.00..19.99 rows=3D1 width=3D8) (actu=
al
> time=3D0.006..0.017 rows=3D1 loops=3D12586)
> =A0 =A0 =A0 =A0 =A0-> =A0Seq Scan on key_description kd =A0(cost=3D0.00..=
2.38 rows=3D1
> width=3D8) (actual time=3D0.003..0.013 rows=3D1 loops=3D12586)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((description)::text =3D 'Speicher=
temperatur'::text)
> =A0 =A0 =A0 =A0 =A0-> =A0Index Scan using unique_key_and_id on log_detail=
s d
> =A0(cost=3D0.00..17.60 rows=3D1 width=3D16) (actual time=3D0.002..0.003 r=
ows=3D1
> loops=3D12586)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Index Cond: (($0 =3D d.fk_id) AND (d.fk_ke=
yid =3D kd.keyid))
> -------------------------------------------------------------------------=
---------------------------------------------------------------------------=
--------------------------------------------------------------
>
> Therefore I optimized the query further which can be done in the followin=
g
> way with another subquery and IHMO a perfect query plan. Also the subsele=
ct
> avoid multiple iterations for each of the result rows:
>
> CREATE OR REPLACE VIEW log_entries_test AS
> SELECT
> =A0l.id AS id,
> =A0l.datetime AS datetime,
> =A0l.tdate AS tdate,
> =A0l.ttime AS ttime,
> =A0(SELECT value FROM log_details d WHERE l.id =3D d.fk_id AND d.fk_keyid=
=3D
> (SELECT keyid FROM key_description WHERE description =3D
> 'Raumsolltemperatur')) AS Raumsolltemperatur,
> =A0(SELECT value FROM log_details d WHERE l.id =3D d.fk_id AND d.fk_keyid=
=3D
> (SELECT keyid FROM key_description WHERE description =3D 'Raumtemperatur'=
)) AS
> Raumtemperatur,
> =A0(SELECT value FROM log_details d WHERE l.id =3D d.fk_id AND d.fk_keyid=
=3D
> (SELECT keyid FROM key_description WHERE description =3D
> 'Kesselsolltemperatur')) AS Kesselsolltemperatur,
> =A0(SELECT value FROM log_details d WHERE l.id =3D d.fk_id AND d.fk_keyid=
=3D
> (SELECT keyid FROM key_description WHERE description =3D 'Kesseltemperatu=
r'))
> AS Kesseltemperatur,
> ...
> FROM
> =A0log l
> ;
> -------------------------------------------------------------------------=
---------------------------------------------------------------------------=
--------------------------------------------------------------
> EXPLAIN ANALYZE SELECT * FROM log_entries_test WHERE datetime > now() -
> INTERVAL '10 days' ORDER BY datetime DESC;
> -------------------------------------------------------------------------=
---------------------------------------------------------------------------=
--------------------------------------------------------------
> Index Scan Backward using i_log_unique on log l =A0(cost=3D0.00..140603.9=
9
> rows=3D69 width=3D32) (actual time=3D2.588..5602.899 rows=3D12586 loops=
=3D1)
> =A0Index Cond: (datetime > (now() - '10 days'::interval))
> =A0SubPlan 2
> =A0 =A0-> =A0Index Scan using unique_key_and_id on log_details d
> =A0(cost=3D2.38..19.97 rows=3D1 width=3D8) (actual time=3D0.010..0.011 ro=
ws=3D1
> loops=3D12586)
> =A0 =A0 =A0 =A0 =A0Index Cond: (($1 =3D fk_id) AND (fk_keyid =3D $0))
> =A0 =A0 =A0 =A0 =A0InitPlan 1 (returns $0)
> =A0 =A0 =A0 =A0 =A0 =A0-> =A0Seq Scan on key_description =A0(cost=3D0.00.=
.2.38 rows=3D1 width=3D8)
> (actual time=3D0.015..0.066 rows=3D1 loops=3D1)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((description)::text =3D 'Raum=
solltemperatur'::text)
> =A0SubPlan 4
> =A0 =A0-> =A0Index Scan using unique_key_and_id on log_details d
> =A0(cost=3D2.38..19.97 rows=3D1 width=3D8) (actual time=3D0.003..0.003 ro=
ws=3D1
> loops=3D12586)
> =A0 =A0 =A0 =A0 =A0Index Cond: (($1 =3D fk_id) AND (fk_keyid =3D $2))
> =A0 =A0 =A0 =A0 =A0InitPlan 3 (returns $2)
> =A0 =A0 =A0 =A0 =A0 =A0-> =A0Seq Scan on key_description =A0(cost=3D0.00.=
.2.38 rows=3D1 width=3D8)
> (actual time=3D0.009..0.020 rows=3D1 loops=3D1)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((description)::text =3D 'Raum=
temperatur'::text)
> =A0SubPlan 6
> =A0 =A0-> =A0Index Scan using unique_key_and_id on log_details d
> =A0(cost=3D2.38..19.97 rows=3D1 width=3D8) (actual time=3D0.002..0.003 ro=
ws=3D1
> loops=3D12586)
> =A0 =A0 =A0 =A0 =A0Index Cond: (($1 =3D fk_id) AND (fk_keyid =3D $3))
> =A0 =A0 =A0 =A0 =A0InitPlan 5 (returns $3)
> =A0 =A0 =A0 =A0 =A0 =A0-> =A0Seq Scan on key_description =A0(cost=3D0.00.=
.2.38 rows=3D1 width=3D8)
> (actual time=3D0.005..0.017 rows=3D1 loops=3D1)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Filter: ((description)::text =3D
> 'Kesselsolltemperatur'::text)
> -------------------------------------------------------------------------=
---------------------------------------------------------------------------=
--------------------------------------------------------------
>
> BTW: Schemadata is in the links discussed in the thread
>
> Thnx to all for helping me.

np -- this felt particularly satisfying for some reason. btw, I think
you have some more low hanging optimization fruit. I think (although
it would certainly have to be tested) hiding your attribute
description under keyid is buying you nothing but headaches. If you
used natural key style, making description primary key of
key_description (or unique), and had log_details have a description
column that directly referenced that column, your subquery:

(
SELECT value FROM log_details d WHERE l.id =3D d.fk_id AND d.fk_keyid =3D
(
SELECT keyid FROM key_description WHERE description =3D 'Kesselsolltemp=
eratur'
)
) AS Kesselsolltemperatur,

would look like this:
(
SELECT value FROM log_details d WHERE l.id =3D d.fk_id AND
d.description =3D 'Kesselsolltemperatur'
) AS Kesselsolltemperatur,

your index on log_details(fk_id, description) is of course fatter, but
quite precise...does require rebuilding your entire dataset however.
food for thought.

merlin