Re: [PERFORM] Many left outer joins with limit performance

2009-09-27 Thread Gerhard Wiesinger 

Hello Tom,

The query was logically ok. The main problem was that the VIEW had an 
ORDER BY clause where cost went up to very high. Indices and unique 
constraints were minor optimizations.


Conclusio: Don't create ORDER BY in VIEW unless really necessary

Ciao,
Gerhard

--
http://www.wiesinger.com/


On Fri, 1 May 2009, Tom Lane wrote:


Gerhard Wiesinger  writes:

FROM
   log l
-- Order is relevant here
LEFT OUTER JOIN key_description k1  ON k1.description = 'Raumsolltemperatur'
LEFT OUTER JOIN log_details d1  ON l.id = d1.fk_id AND d1.fk_keyid = 
k1.keyid


Surely this query is just plain broken?  You're forming a cross product
of the relevant log lines with the k1 rows having description =
'Raumsolltemperatur' (I assume this isn't unique, else it's not clear
what the point is) and then the subsequent left join cannot get rid of
anything.  I think probably you meant something different, like

FROM
  log l
LEFT OUTER JOIN log_details d1  ON l.id = d1.fk_id
LEFT OUTER JOIN key_description k1  ON k1.description = 'Raumsolltemperatur' 
AND d1.fk_keyid = k1.keyid

regards, tom lane

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Many left outer joins with limit performance

2009-05-01 Thread Tom Lane 
Gerhard Wiesinger  writes:
> FROM
>log l
> -- Order is relevant here
> LEFT OUTER JOIN key_description k1  ON k1.description = 'Raumsolltemperatur'
> LEFT OUTER JOIN log_details d1  ON l.id = d1.fk_id AND d1.fk_keyid = 
> k1.keyid

Surely this query is just plain broken?  You're forming a cross product
of the relevant log lines with the k1 rows having description =
'Raumsolltemperatur' (I assume this isn't unique, else it's not clear
what the point is) and then the subsequent left join cannot get rid of
anything.  I think probably you meant something different, like

FROM
   log l
LEFT OUTER JOIN log_details d1  ON l.id = d1.fk_id
LEFT OUTER JOIN key_description k1  ON k1.description = 'Raumsolltemperatur' 
AND d1.fk_keyid = k1.keyid

regards, tom lane

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Many left outer joins with limit performance

2009-05-01 Thread Gerhard Wiesinger 

Hello,

I want to use postgresql for data entries (every minute) from a central heating 
system where the timestamp is logged in a table log. For flexibility in the 
future for future values and for implementing several high level types I've 
modelled the values in a separate key/value table called log_details.


A Query for the last valid entry for today looks like (also defined as a view), 
sometimes used without the limit:

SELECT
  l.id AS id,
  l.datetime AS datetime,
  l.tdate AS tdate,
  l.ttime AS ttime,
  d1.value  AS Raumsolltemperatur,
  d2.value  AS Raumtemperatur,
-- a lot more here, stripped for readibility, see link
FROM
  log l
-- Order is relevant here
LEFT OUTER JOIN key_description k1  ON k1.description = 'Raumsolltemperatur'
LEFT OUTER JOIN log_details d1  ON l.id = d1.fk_id AND d1.fk_keyid = 
k1.keyid

-- Order is relevant here
LEFT OUTER JOIN key_description k2  ON k2.description = 'Raumtemperatur'
LEFT OUTER JOIN log_details d2  ON l.id = d2.fk_id AND d2.fk_keyid = 
k2.keyid

-- a lot more here, stripped for readibility, see link
WHERE
  -- 86400 entries in that timeframe
  datetime>= '1970-01-01 00:00:00+02'
  AND datetime < '1970-01-02 00:00:00+02'
ORDER BY
  datetime DESC
LIMIT 1;

For me a perfect query plan would look like:
1.) Fetch the one and only id from table log (or fetch even all necessary id 
entries when no limit is specifie)

2.) Make the left outer joins

Details (machine details, table definition, query plans, etc.) 
can be found to due size limitations at:

http://www.wiesinger.com/tmp/pg_perf.txt

Any ideas how to improve the performance on left outer joins only and how to 
improve the planner to get better results?


For this special case a better solution exists but I thing the planner has to 
do the work.

-- ...
WHERE
-- Also slow: id IN
-- OK: id =
id = (
  SELECT
id
  FROM
log
  WHERE
datetime>= '1970-01-01 00:00:00+02'
AND datetime < '1970-01-02 00:00:00+02'
  ORDER BY
datetime DESC
  LIMIT 1
)
ORDER BY
datetime DESC LIMIT 1;

Any ideas?

Thnx.

Ciao,
Gerhard

--
http://www.wiesinger.com/

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance