Re: Many left outer joins with limit performance

From: Gerhard Wiesinger
Subject: Re: Many left outer joins with limit performance
Date: ,
Msg-id: alpine.LFD.2.00.0909270849290.31347@bbs.intern
(view: Whole thread, Raw)
In response to: Re: Many left outer joins with limit performance  (Tom Lane)
List: pgsql-performance

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 ()
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>


pgsql-performance by date:

From: Gerhard Wiesinger
Date:
Subject: Re: Many left outer joins with limit performance
From: Robert Haas
Date:
Subject: Re: Regarding Sequential Scans count increase each time we press refresh .