Horton

Re: Hybrid Hash/Nested Loop joins and caching results from subplans

Andy Fan <zhihui.fan1213@gmail.com>

View thread View on postgr.es
From: Andy Fan <zhihui.fan1213@gmail.com>
To: David Rowley <dgrowleyml@gmail.com>
Cc: Simon Riggs <simon@2ndquadrant.com>, PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>
Date: 2020-06-03T02:36:20Z
Lists: pgsql-hackers 
>
>
> Thanks for running those tests.  I had a quick look at the results and
> I think to say that all 4 are better is not quite right. One is
> actually a tiny bit slower and one is only faster due to a plan
> change.
>
>
Yes..  Thanks for pointing it out.


> Q18 uses a result cache for 2 x nested loop joins and has a 0% hit
> ratio.  The execution time is reduced to 91% of the original time only
> because the planner uses a different plan, which just happens to be
> faster by chance.
> Q20 uses a result cache for the subplan and has a 0% hit ratio.  The
> execution time is 100.27% of the original time. There are 8620 cache
> misses.
>

Looks the case here is some statistics issue or cost model issue. I'd
like to check more about that.  But before that, I upload the steps[1] I
used
in case you want to reproduce it locally.

[1]  https://github.com/zhihuiFan/tpch-postgres

-- 
Best Regards
Andy Fan

Commits

  1. Add Result Cache executor node (take 2)

  2. Add Result Cache executor node

  3. Allow estimate_num_groups() to pass back further details about the estimation

  4. Allow users of simplehash.h to perform direct deletions

  5. Cache if PathTarget and RestrictInfos contain volatile functions

  6. Fix pull_varnos' miscomputation of relids set for a PlaceHolderVar.