On Fri, 12 Jun 2020 at 16:10, Andy Fan <zhihui.fan1...@gmail.com> wrote: > I first tried to see if we can have a row estimation before the subplan > is created and it looks very complex. The subplan was created during > preprocess_qual_conditions, at that time, we even didn't create the base > RelOptInfo , to say nothing of join_rel which the rows estimation happens > much later. > > Then I see if we can delay the cache decision until we have the rows > estimation, > ExecInitSubPlan may be a candidate. At this time we can't add a new > ResutCache node, but we can add a cache function to SubPlan node with costed > based. However the num_of_distinct values for parameterized variable can't be > calculated which I still leave it as an open issue.
I don't really like the idea of stuffing this feature into some existing node type. Doing so would seem pretty magical when looking at an EXPLAIN ANALYZE. There is of course overhead to pulling tuples through an additional node in the plan, but if you use that as an argument then there's some room to argue that we should only have 1 executor node type to get rid of that overhead. Tom mentioned in [1] that he's reconsidering his original thoughts on leaving the AlternativeSubPlan selection decision until execution time. If that were done late in planning, as Tom mentioned, then it would be possible to give a more accurate cost to the Result Cache as we'd have built the outer plan by that time and would be able to estimate the number of distinct calls to the correlated subplan. As that feature is today we'd be unable to delay making the decision until execution time as we don't have the required details to know how many distinct calls there will be to the Result Cache node. For now, I'm planning on changing things around a little in the Result Cache node to allow faster deletions from the cache. As of now, we must perform 2 hash lookups to perform a single delete. This is because we must perform the lookup to fetch the entry from the MRU list key, then an additional lookup in the hash delete code. I plan on changing the hash delete code to expose another function that allows us to delete an item directly if we've already looked it up. This should make a small reduction in the overheads of the node. Perhaps if the overhead is very small (say < 1%) when the cache is of no use then it might not be such a bad thing to just have a Result Cache for correlated subplans regardless of estimates. With the TPCH Q20 test, it appeared as if the overhead was 0.27% for that particular subplan. A more simple subplan would execute more quickly resulting the Result Cache overhead being a more significant portion of the overall subquery execution. I'd need to perform a worst-case overhead test to get an indication of what the percentage is. David [1] https://www.postgresql.org/message-id/1992952.1592785...@sss.pgh.pa.us
