From: Philippe Pepiot <phil@philpep.org>
To: David Rowley <dgrowleyml@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Range partitioning query performance with date_trunc (vs timescaledb)
Date: Mon, 11 Sep 2023 14:21:30 +0200
Message-ID: <ZP8GSpzTAbXZql4z@bezout.in.philpep.org> (raw)
In-Reply-To: <CAApHDvrW=whRLBpmsu5XM-x=bi=UTtqvQJdFjm9=Qy0VpbVzXw@mail.gmail.com>
References: <ZO2g1l1ho60LSEBt@bezout.in.philpep.org>
<CAApHDvrW=whRLBpmsu5XM-x=bi=UTtqvQJdFjm9=Qy0VpbVzXw@mail.gmail.com>
On 29/08/2023, David Rowley wrote:
> On Tue, 29 Aug 2023 at 19:40, Philippe Pepiot <phil@philpep.org> wrote:
> > I'm trying to implement some range partitioning on timeseries data. But it
> > looks some queries involving date_trunc() doesn't make use of partitioning.
> >
> > BEGIN;
> > CREATE TABLE test (
> > time TIMESTAMP WITHOUT TIME ZONE NOT NULL,
> > value FLOAT NOT NULL
> > ) PARTITION BY RANGE (time);
> > CREATE INDEX test_time_idx ON test(time DESC);
> > CREATE TABLE test_y2010 PARTITION OF test FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');
> > CREATE TABLE test_y2011 PARTITION OF test FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');
> > CREATE VIEW vtest AS SELECT DATE_TRUNC('year', time) AS time, SUM(value) AS value FROM test GROUP BY 1;
> > EXPLAIN (COSTS OFF) SELECT * FROM vtest WHERE time >= TIMESTAMP '2021-01-01';
> > ROLLBACK;
> >
> > The plan query all partitions:
>
> > I wonder if there is a way with a reasonable amount of SQL code to achieve this
> > with vanilla postgres ?
>
> The only options I see for you are
>
> 1) partition by LIST(date_Trunc('year', time)), or;
> 2) use a set-returning function instead of a view and pass the date
> range you want to select from the underlying table via parameters.
>
> I imagine you won't want to do #1. However, it would at least also
> allow the aggregation to be performed before the Append if you SET
> enable_partitionwise_aggregate=1.
>
> #2 isn't as flexible as a view as you'd have to create another
> function or expand the parameters of the existing one if you want to
> add items to the WHERE clause.
>
> Unfortunately, date_trunc is just a black box to partition pruning, so
> it's not able to determine that DATE_TRUNC('year', time) >=
> '2021-01-01' is the same as time >= '2021-01-01'. It would be
> possible to make PostgreSQL do that, but that's a core code change,
> not something that you can do from SQL.
Ok I think I'll go for Set-returning function since
LIST or RANGE on (date_trunc('year', time)) will break advantage of
partitioning when querying with "time betwen x and y".
Thanks!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: phil@philpep.org, dgrowleyml@gmail.com
Subject: Re: Range partitioning query performance with date_trunc (vs timescaledb)
In-Reply-To: <ZP8GSpzTAbXZql4z@bezout.in.philpep.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox