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@wiesinger.com> 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
>