Re: A Better Way? (Multi-Left Join Lookup)

"David Johnston" <polobo@yahoo.com> writes:
> WITH
>   full_set AS ( ) -- 8,500 records
> , sub_1 AS () -- also about 8,500
> , sub_2 AS () -- maybe 5,000
> , sub_3 AS () - - maybe 3,000
> SELECT full_set.*
> , COALESCE(sub_1.field, FALSE)
> , COALESCE(sub_2.field, FALSE)
> , COALESCE(sub_2.field, FALSE)
> FROM full_set
> LEFT JOIN sub_1
> LEFT JOIN sub_2
> LEFT JOIN sub_3

> The performance of this query is exponential due to the fact that the
> sub-queries/CTEs are not indexed and so each subset has to be scanned
> completely for each record in the full set.

Surely not.  Neither merge nor hash joins require an index.  What plan
is getting selected?  Are you sure there's at most one match in each
"sub" set for each row in the "full" set?  If you were getting a large
number of matches in some cases, the size of the result could balloon
to something unfortunate ... but we have not got enough information to
know.

            regards, tom lane