Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id F3B891337E57 for ; Tue, 14 Sep 2010 17:00:33 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 90656-02 for ; Tue, 14 Sep 2010 20:00:26 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from fep12.mx.upcmail.net (fep12.mx.upcmail.net [62.179.121.32]) by mail.postgresql.org (Postfix) with ESMTP id 3CE411337C10 for ; Tue, 14 Sep 2010 16:59:55 -0300 (ADT) Received: from edge05.upcmail.net ([192.168.13.212]) by viefep12-int.chello.at (InterMail vM.8.01.02.02 201-2260-120-106-20100312) with ESMTP id <20100914195952.ZQJT1480.viefep12-int.chello.at@edge05.upcmail.net>; Tue, 14 Sep 2010 21:59:52 +0200 Received: from wiesinger.com ([84.112.167.138]) by edge05.upcmail.net with edge id 6jzg1f05P2zVakc05jzheC; Tue, 14 Sep 2010 21:59:52 +0200 X-SourceIP: 84.112.167.138 Received: from bbs.intern (localhost [127.0.0.1]) by wiesinger.com (8.14.4/8.14.4) with ESMTP id o8EJx9Dj025933; Tue, 14 Sep 2010 21:59:09 +0200 Received: from localhost (gerhard@localhost) by bbs.intern (8.14.4/8.14.4/Submit) with ESMTP id o8EJx918025929; Tue, 14 Sep 2010 21:59:09 +0200 Date: Tue, 14 Sep 2010 21:59:09 +0200 (CEST) From: Gerhard Wiesinger To: Merlin Moncure cc: pgsql-performance@postgresql.org, Tom Lane , Pavel Stehule , Andreas Kretschmer Subject: Re: Major performance problem after upgrade from 8.3 to 8.4 In-Reply-To: Message-ID: References: <12837.1283186981@sss.pgh.pa.us> <21281.1283433278@sss.pgh.pa.us> <22001.1283523290@sss.pgh.pa.us> <24994.1283532424@sss.pgh.pa.us> <25560.1283534215@sss.pgh.pa.us> <26620.1283537362@sss.pgh.pa.us> <27253.1283539468@sss.pgh.pa.us> <29799.1283548250@sss.pgh.pa.us> User-Agent: Alpine 2.01 (LFD 1266 2009-07-14) MIME-Version: 1.0 X-MailScanner-Information-wiesinger-com: Please contact the ISP for more information X-wiesinger-com-MailScanner-ID: o8EJx9Dj025933 X-MailScanner-wiesinger-com: Found to be clean X-MailScanner-SpamCheck-wiesinger-com: not spam, SpamAssassin (not cached, score=-4.399, required 4.5, autolearn=not spam, ALL_TRUSTED -1.80, BAYES_00 -2.60) X-MailScanner-From: lists@wiesinger.com X-Cloudmark-Analysis: v=1.1 cv=BBXmjR/jrMMj1wHBsuD7Cdv69cLzNztNz9uR8CXiauo= c=1 sm=0 a=-BBt41PQAQkA:10 a=wPDyFdB5xvgA:10 a=-ProOGmuAAAA:8 a=lnBNX0v_F0A5zMUMEsAA:9 a=Zb1UJrM0pkkiMbq-PkgA:7 a=qO71piynaKUoWGOeKk_ZhDG52NgA:4 a=wPNLvfGTeEIA:10 a=fU-A4pz3-wIA:10 a=HpAAvcLHHh0Zw7uRqdWCyQ==:117 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, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: Content-Type: TEXT/PLAIN; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8BIT X-Archive-Number: 201009/80 X-Sequence-Number: 40283 On Tue, 14 Sep 2010, Merlin Moncure wrote: > On Tue, Sep 14, 2010 at 2:07 AM, Gerhard Wiesinger 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 (), ... 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@postgresql.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 ; 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 ; 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 ; Tue, 14 Sep 2010 18:05:22 -0300 (ADT) Received: by eyb6 with SMTP id 6so3359037eyb.19 for ; 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: References: <12837.1283186981@sss.pgh.pa.us> <21281.1283433278@sss.pgh.pa.us> <22001.1283523290@sss.pgh.pa.us> <24994.1283532424@sss.pgh.pa.us> <25560.1283534215@sss.pgh.pa.us> <26620.1283537362@sss.pgh.pa.us> <27253.1283539468@sss.pgh.pa.us> <29799.1283548250@sss.pgh.pa.us> Date: Tue, 14 Sep 2010 17:05:21 -0400 Message-ID: Subject: Re: Major performance problem after upgrade from 8.3 to 8.4 From: Merlin Moncure To: Gerhard Wiesinger Cc: pgsql-performance@postgresql.org, Tom Lane , Pavel Stehule , Andreas Kretschmer 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 wr= ote: > On Tue, 14 Sep 2010, Merlin Moncure wrote: > >> On Tue, Sep 14, 2010 at 2:07 AM, Gerhard Wiesinger >> 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 (), ... 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