Horton

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

David Rowley <dgrowleyml@gmail.com>

View thread View on postgr.es
From: David Rowley <dgrowleyml@gmail.com>
To: Andy Fan <zhihui.fan1213@gmail.com>
Cc: Justin Pryzby <pryzby@telsasoft.com>, Konstantin Knizhnik <k.knizhnik@postgrespro.ru>, Tom Lane <tgl@sss.pgh.pa.us>, Alvaro Herrera <alvherre@alvh.no-ip.org>, Andres Freund <andres@anarazel.de>, PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>
Date: 2021-05-26T03:43:54Z
Lists: pgsql-hackers 
On Wed, 26 May 2021 at 14:19, Andy Fan <zhihui.fan1213@gmail.com> wrote:
> I just checked the latest code,  looks like we didn't improve this situation except
> that we introduced a GUC to control it.   Am I missing something? I don't have a
> suggestion though.

Various extra caching was done to help speed it up.  We now cache the
volatility of RestrictInfo and PathTarget.

I also added caching for the hash function in RestrictInfo so that we
could more quickly determine if we can Result Cache or not.

There's still a bit of caching left that I didn't do. This is around
lateral_vars.  I've nowhere to cache the hash function since that's
just a list of vars.  At the moment we need to check that each time we
consider a result cache path.  LATERAL joins are a bit less common so
I didn't think that would be a huge issue.  There's always
enable_resultcache = off for people who cannot tolerate the overhead.

Also, it's never going to be 100% as fast as it was. We're considering
another path that we didn't consider before.

Did you do some performance testing that caused you to bring this topic up?

David

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.