What makes the cross apply query perform so poorly on this simple XML document, and perform exponentially slower as the dataset grows? It Is the use of the parent axis to get the attribute ID from the item node. It is this part of the query plan that is problematic. Notice the 423 rows coming … Read more
In Postgres 9.3 or later use a LATERAL join: SELECT v.col_a, v.col_b, f.* — no parentheses, f is a table alias FROM v_citizenversions v LEFT JOIN LATERAL f_citizen_rec_modified(v.col1, v.col2) f ON true WHERE f.col_c = _col_c; Why LEFT JOIN LATERAL … ON true? Record returned from function has columns concatenated For older versions, there is … Read more
Your closest direct approximation is a join with a correlated sub-query as the predicate. SELECT ORD.ID ,ORD.NAME ,ORD.DATE ,ORD_HISTORY.VALUE FROM ORD INNER JOIN ORD_HISTORY ON ORD_HISTORY.<PRIMARY_KEY> = (SELECT ORD_HISTORY.<PRIMARY_KEY> FROM ORD_HISTORY WHERE ORD.ID = ORD_HISTORY.ID AND ORD.DATE <= ORD_HISTORY.DATE ORDER BY ORD_HISTORY.DATE DESC LIMIT 1 ) In your case, however, you only need one field … Read more
Can anyone give me a good example of when CROSS APPLY makes a difference in those cases where INNER JOIN will work as well? See the article in my blog for detailed performance comparison: INNER JOIN vs. CROSS APPLY CROSS APPLY works better on things that have no simple JOIN condition. This one selects 3 … Read more