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-02T09:04:50Z
Lists: pgsql-hackers

Attachments

Today I tested the correctness & performance of this patch based on TPC-H
workload, the environment is setup based on [1]. Correctness is tested by
storing the result into another table when this feature is not introduced
and
then enable this feature and comparing the result with the original ones. No
issue is found at this stage.

I also checked the performance gain for TPC-H workload, totally 4 out of
the 22
queries uses this new path, 3 of them are subplan, 1 of them is nestloop.
All of
changes gets a better result. You can check the attachments for reference.
normal.log is the data without this feature, patched.log is the data with
the
feature. The data doesn't show the 10x performance gain, I think that's
mainly
data size related.

At the code level,  I mainly checked nestloop path and
cost_resultcache_rescan,
everything looks good to me. I'd like to check the other parts in the
following days.

[1] https://ankane.org/tpc-h


-- 
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.