Re: Many left outer joins with limit performance - Mailing list pgsql-performance

From Gerhard Wiesinger
Subject Re: Many left outer joins with limit performance
Date
Msg-id alpine.LFD.2.00.0909270849290.31347@bbs.intern
Whole thread Raw
In response to Re: Many left outer joins with limit performance  (Tom Lane <tgl@sss.pgh.pa.us>)
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 <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
>

pgsql-performance by date:

Previous
From: Xia Qingran
Date:
Subject: Re: Bad performance of SELECT ... where id IN (...)
Next
From: Robert Haas
Date:
Subject: Re: Regarding Sequential Scans count increase each time we press refresh .