Re: Many left outer joins with limit performance

From: Tom Lane
Subject: Re: Many left outer joins with limit performance
Date: ,
Msg-id: 13700.1241194450@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Many left outer joins with limit performance  (Gerhard Wiesinger)
Responses: Re: Many left outer joins with limit performance  (Gerhard Wiesinger)
List: pgsql-performance

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


pgsql-performance by date:

From: Scott Carey
Date:
Subject: Re: Transparent table partitioning in future version of PG?
From: PFC
Date:
Subject: Re: performance for high-volume log insertion