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 <lists@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
