Do PostgreSQL partition setting have any effect on TimescaleDB?

ts101 · October 6, 2023, 8:08am

In postgreSQL v15.4 there are the following partitioning settings, that have effect on optimizing table partitioning query “explain plan”:

Bellow settings can be on or off:

enable_partition_pruning
enable_partitionwise_aggregate
enable_partitionwise_join

Do those settings have any effect on TimescaleDb v2.12.0 self-hosted? If yes, is there recommendation to turn them on?

jonatasdp · October 10, 2023, 7:09pm

Hi @ts101, I couldn’t find anything about pruning, but I found a few tests about partitionwise_aggregate:

github.com

timescale/timescaledb/blob/6af0cb00c9cdd0e76ec58ecf46b8d279064fa2ab/test/sql/partitionwise.sql#L55


      
          SELECT * FROM pg2dim_h1_t1;
          SELECT * FROM pg2dim_h1_t2;
          SELECT * FROM pg2dim_h2_t1;
          SELECT * FROM pg2dim_h2_t2;
          
          
          -- Compare partitionwise aggreate enabled/disabled. First run queries
          -- on PG partitioned tables for reference.
          
          -- All partition keys covered by GROUP BY
          SET enable_partitionwise_aggregate = 'off';
          :PREFIX
          SELECT device, avg(temp)
          FROM pg1dim
          GROUP BY 1
          ORDER BY 1;
          
          SET enable_partitionwise_aggregate = 'on';
          :PREFIX
          SELECT device, avg(temp)
          FROM pg1dim

and join:

github.com

timescale/timescaledb/blob/6af0cb00c9cdd0e76ec58ecf46b8d279064fa2ab/test/sql/partitionwise.sql#L188


      
          
          -- Test partitionwise joins, mostly to see that we do not break
          -- anything
          CREATE TABLE hyper_meta (time timestamptz, device int, info text);
          SELECT * FROM create_hypertable('hyper_meta', 'time', 'device', 2);
          
          INSERT INTO hyper_meta VALUES
                 ('2018-02-19 13:01', 1, 'device_1'),
                 ('2018-02-19 13:02', 3, 'device_3');
          
          SET enable_partitionwise_join = 'off';
          
          :PREFIX
          SELECT h.time, h.device, h.temp, hm.info
          FROM hyper h, hyper_meta hm
          WHERE h.device = hm.device;
          
          :PREFIX
          SELECT pg2.time, pg2.device, pg2.temp, pg1.temp
          FROM pg2dim pg2, pg1dim pg1
          WHERE pg2.device = pg1.device;

It seems we have a test covering such behavior change, but I’m not sure how to interpret it. I asked some help from other folks that are contributing to core database. Let’s see if they can also give some insights.

jonatasdp · October 17, 2023, 2:35pm

Hi @ts101, I confirmed with the core database developers and it seems the latest versions none of them are compatible.

latest versions (2.12+) the enable_partitionwise_aggregate no longer affects timescaledb behaviour, we usually have our own GUCs to control timescaledb behaviour.