Hi All,
I have a question about partition pruning.
Does runtime partition pruning (ie. pruning performed during execution) work
with partition wise aggregates?
1) I have a setup with a mix of foreign (postgres_fdw) and local partitions.
2) I want to perform an aggregate query and I want the
Thanks. Yes, the query with the same parameter seems to work as expected.
It doesn't help us though as we are trying to transparently support
partitioning using an ORM and we cannot change the parameters. Using the
column name would have been much easier.
Regards,
Radu
On Mon, Mar 23, 2020 at 5:5
>
> select * from test where id between client_id-10 and client_id+10 and
> client_id=?;
>
> does not (it scans all partitions in parallel) .
> Is it expected?
>
Yes. But the below would work fine I expect since the planner would know a
constant range for id. I would be very surprised if the opti
Hello list,
Is runtime partition pruning available in PostgreSQL 12?
I have a table partitioned by range on column id (primary key).
For the query (client_id is passed as a parameter from the application):
select * from test where id between>0 and and id<1000 and client_id=? ;
par
Hello,
Runtime partition pruning don't work without index on a hash partitioned
column.
Consider this test case on version 12:
create table data (
key_id integer not null,
value real not null
) partition by hash(key_id);
create table data_0 partition of data for values
Notice that only subqueries and parameterized nested loop joins are
mentioned. The above text does not really go into the detail of which
types of subqueries can be used, but I can confirm that they must be
subqueries that can only return a scalar value. e.g WHERE x = (SELECT
y FROM ...). The
s too long, because PostgreSQL uses a hash join over all
> partitions of "data_table" with the "import_table", instead of pruning
> the "data_table" partitions by the import_ids at runtime.
> Static pruning (when using ... IN (1, 2, 3, 4)) works fine though.
&g
"import_table", instead of pruning
the "data_table" partitions by the import_ids at runtime.
Static pruning (when using ... IN (1, 2, 3, 4)) works fine though.
What am I doing wrong that runtime partition pruning with PostgreSQL
11.5 does not work in my case?
Thanks,
Markus
