Обсуждение: How to launch parallel aggregations ?
Dear all
In MobilityDB we have defined parallel aggregations with a combine function, e.g.,
CREATE AGGREGATE extent(tbox) (
SFUNC = tbox_extent_transfn,
STYPE = tbox,
COMBINEFUNC = tbox_extent_combinefn,
PARALLEL = safe
);
SFUNC = tbox_extent_transfn,
STYPE = tbox,
COMBINEFUNC = tbox_extent_combinefn,
PARALLEL = safe
);
We would like to trigger the combine functions in the coverage tests but for this it is required that the tables are VERY big. In particular for the above aggregation, the combine function only is triggered when the table has more than 300K rows.
As it is not very effective to have such a big table in the test database used for the regression and the coverage tests I wonder whether it is possible to set some parameters to launch the combine functions with tables of, e.g., 10K rows, which are the bigger tables in our regression test database.
Many thanks for your insights !
Esteban
On Tue, May 18, 2021 at 2:32 PM Esteban Zimanyi <ezimanyi@ulb.ac.be> wrote: > > Dear all > > In MobilityDB we have defined parallel aggregations with a combine function, e.g., > > CREATE AGGREGATE extent(tbox) ( > SFUNC = tbox_extent_transfn, > STYPE = tbox, > COMBINEFUNC = tbox_extent_combinefn, > PARALLEL = safe > ); > > We would like to trigger the combine functions in the coverage tests but for this it is required that the tables are VERYbig. In particular for the above aggregation, the combine function only is triggered when the table has more than 300Krows. > > As it is not very effective to have such a big table in the test database used for the regression and the coverage testsI wonder whether it is possible to set some parameters to launch the combine functions with tables of, e.g., 10K rows,which are the bigger tables in our regression test database. > > Many thanks for your insights ! You could do something like below, just before your test: -- encourage use of parallel plans set parallel_setup_cost=0; set parallel_tuple_cost=0; set min_parallel_table_scan_size=0; set max_parallel_workers_per_gather=2; And after the test you can reset all of the above parameters. Hope that helps! With Regards, Bharath Rupireddy. EnterpriseDB: http://www.enterprisedb.com
Thanks a lot! It works!
On Tue, May 18, 2021 at 11:15 AM Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com> wrote:
On Tue, May 18, 2021 at 2:32 PM Esteban Zimanyi <ezimanyi@ulb.ac.be> wrote:
>
> Dear all
>
> In MobilityDB we have defined parallel aggregations with a combine function, e.g.,
>
> CREATE AGGREGATE extent(tbox) (
> SFUNC = tbox_extent_transfn,
> STYPE = tbox,
> COMBINEFUNC = tbox_extent_combinefn,
> PARALLEL = safe
> );
>
> We would like to trigger the combine functions in the coverage tests but for this it is required that the tables are VERY big. In particular for the above aggregation, the combine function only is triggered when the table has more than 300K rows.
>
> As it is not very effective to have such a big table in the test database used for the regression and the coverage tests I wonder whether it is possible to set some parameters to launch the combine functions with tables of, e.g., 10K rows, which are the bigger tables in our regression test database.
>
> Many thanks for your insights !
You could do something like below, just before your test:
-- encourage use of parallel plans
set parallel_setup_cost=0;
set parallel_tuple_cost=0;
set min_parallel_table_scan_size=0;
set max_parallel_workers_per_gather=2;
And after the test you can reset all of the above parameters.
Hope that helps!
With Regards,
Bharath Rupireddy.
EnterpriseDB: http://www.enterprisedb.com