Date: Fri, 21 Jun 2019 08:41:11 -0700 (MST)
From: Jim Finnerty <jfinnert@amazon.com>
To: pgsql-hackers@postgresql.org
Message-ID: <1561131671319-0.post@n3.nabble.com>
In-Reply-To: <20190620164410.0218e26e0bdedb7574dec2eb@sraoss.co.jp>
References: <20181227215726.4d166b4874f8983a641123f5@sraoss.co.jp>
 <20190401121122.a84d8dff0bd13eaccd373135@sraoss.co.jp>
 <20190514154648.63fea174748c18ed7f7dcb16@sraoss.co.jp>
 <20190620164410.0218e26e0bdedb7574dec2eb@sraoss.co.jp>
Subject: Re: Implementing Incremental View Maintenance
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk

Hi Yugo,

    I'd like to compare the performance of your MV refresh algorithm versus
an approach that logs changes into an mv log table, and can then apply the
changes at some later point in time.  I'd like to handle the materialized
join view (mjv) case first, specifically a 2-way left outer join, with a UDF
in the SELECT list of the mjv.

    Does your refresh algorithm handle mjv's with connected join graphs that
consist entirely of inner and left outer joins?

    If so, I'd like to measure the overhead of your refresh algorithm on
pgbench, modified to include an mjv, versus a (hand coded) incremental
maintenance algorithm that uses mv log tables populated by ordinary
triggers.  We may also want to look at capturing the deltas using logical
replication, which ought to be faster than a trigger-based solution. 

    I have someone available to do the performance testing for another 2
months, so if you can connect with me off-list to coordinate, we can set up
the performance experiments and run them on our AWS clusters.

best regards,

    /Jim F


-----
Jim Finnerty, AWS, Amazon Aurora PostgreSQL
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html