"Adam Rich" <adam.r@xxxxxxxxxxxxx> writes: > Are there any plans to support materialized subquery / factoring (sql-99 > WITH) in Postgresql? I am spoiled with this feature in oracle, and find > myself wishing I had it in postgresql more and more. It *seems* to an > outsider like a relatively easy addition. We have the patch to support the syntax but we're a bit unsure whether that's actually helpful. One thing we're missing is exactly what you could help with. What I would like to know is what the user expectations are when using this feature. Are you using it just to avoid retyping a complex subquery? Or do you expect that the feature will reduce the execution time by avoiding re-executing the subquery for each call site in the query? How disappointing would it be if the WITH clause acted as an optimization barrier, preventing WHERE clauses from being pushed down and potentially using indexes? Or if the query ended up not needing the data in the WITH subquery but the query had to execute it anyways? Another way of looking at this question is: if you called some volatile function from the subquery such as one which printed diagnostic messages or accessed some remote service, how many times would you expect it to be called? Would you expect the feature to guarantee that the function would only be called once or would it be ok if it were called 0 times if the subquery data was never needed or many times if the optimizer thought that would be faster? > I searched the archives but only found a brief mention of a > syntax-support-only patch from last winter. Yeah, it was the first step in implementing recursive queries. It was deemed uninteresting without actual recursive query support, but there was some more recent emails where someone else was interested in it so that decision may be revisited for 8.4. And in any case there's still a good chance (though no guarantee) that recursive queries will be done for 8.4 which will definitely imply this feature. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
