Comment 5 for bug 1400376

Ben,

Let's assume that both timings are stable. On their face, while both objectively "fast", the INNER JOIN version took very nearly twice the time as the LEFT JOIN. For one record, that may not matter. For many records, it could matter quite a lot.

Now, let's consider how the view itself might be used. If you use that view (say, through one of the dependent views) simply to probe for the existence of a row in record_attr_vector_list by selecting the id column with a where clause that filters on id (or, say, a subquery IN LIST of id's), then the LEFT JOIN version can skip the joins to the attribute value tables altogether. If used in an EXISTS (or similar) clause, it can even skip the execution of the "uncontrolled" side of the UNION, returning after the first row of the "controlled" side produces a row from record_attr_vector_list.

However, the INNER JOIN version /must/ produce the full output of one or the other arms (and, based on ORDER BY, LIMIT, or OFFSET clauses on wrapping subqueries, possible both arms) of the UNION, regardless of whether the attribute value is actually used anywhere in the query.

If you use the view to filter by attribute information, then they will act (generally) the same way in terms of planning.

Some queries will output or filter on the attribute information, some will only look at the id. One place, in particular, is search variants which use the dependent views to see if the record should be included in result output by probing the table underlying this view instead of looking at "bre", which is larger and more expensive to query.

I continue to recommend the LEFT JOIN version with a filter in the views above.