On Tue, 14 Sep 2010, Merlin Moncure wrote:
On Tue, Sep 14, 2010 at 2:07 AM, Gerhard Wiesinger <lists@xxxxxxxxxxxxx> 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/
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
