On Sat, Jan 29, 2022 at 11:31 AM Andres Freund <and...@anarazel.de> wrote: > > Hi, > > Are there any recent performance evaluations of the overhead of row filters? I > think it'd be good to get some numbers comparing: > > 1) $workload with master > 2) $workload with patch, but no row filters > 3) $workload with patch, row filter matching everything > 4) $workload with patch, row filter matching few rows > > For workload I think it'd be worth testing: > a) bulk COPY/INSERT into one table > b) Many transactions doing small modifications to one table > c) Many transactions targetting many different tables > d) Interspersed DDL + small changes to a table >
Here's the performance data results for scenario d: HEAD "with patch no row filter" "with patch 0%" "row-filter-patch 25%" "row-filter-patch v74 50%" "row-filter-patch 75%" "row-filter-patch v74 100%" 1 65.397639 64.414034 5.919732 20.012096 36.35911 49.412548 64.508842 2 65.641783 65.255775 5.715082 20.157575 36.957403 51.355821 65.708444 3 65.096526 64.795163 6.146072 21.130709 37.679346 49.568513 66.602145 4 65.173569 64.644448 5.787197 20.784607 34.465133 55.397313 63.545337 5 65.791092 66.000412 5.642696 20.258802 36.493626 52.873252 63.511428 The performance is similar to the other scenarios. The script used is below: CREATE TABLE test (key int, value text, value1 text, data jsonb, PRIMARY KEY(key, value)); CREATE PUBLICATION pub_1 FOR TABLE test WHERE (key > 0); -- 100% allowed CREATE PUBLICATION pub_1 FOR TABLE test WHERE (key > 250000); -- 75% allowed CREATE PUBLICATION pub_1 FOR TABLE test WHERE (key > 500000); -- 50% allowed CREATE PUBLICATION pub_1 FOR TABLE test WHERE (key > 750000); -- 25% allowed CREATE PUBLICATION pub_1 FOR TABLE test WHERE (key > 1000000); -- 0% allowed DO $do$ BEGIN FOR i IN 1..1000001 BY 4000 LOOP Alter table test alter column value1 TYPE varchar(30); INSERT INTO test VALUES(i,'BAH', row_to_json(row(i))); Alter table test ALTER COLUMN value1 TYPE text; UPDATE test SET value = 'FOO' WHERE key = i; COMMIT; END LOOP; END $do$; regards, Ajin Cherian Fujitsu Australia
