But I forgot to say we can improve for the partitioned ones with predicates indeed. It's on my TODO list. Pedro On 7/17/20 3:10 PM, ferreira wrote:

Hello Daniel,

This is something we are missing on the documentation :)

In order for the pruning to work, the children tables must be set to read only. Then the analyze command should run for each child, so the min and max values are updated on their statistics. Only then, the pruning can happen.

This is to avoid eventual cache poisoning on the re-writers.

Also the upper range limit is exclusive on a partition. At your example the third_decade table should be the only one expected.

Best regards,

Pedro

On 7/17/20 2:32 PM, Daniel Glöckner wrote:

...

Hi,

looking at merge tables and the related query plans I was expecting the planner to skip tables depending on partition information and query predicates.

I'm using the official example for merge tables: https://www.monetdb.org/blog/updatable-merge-tables

CREATE MERGE TABLE splitted (stamp TIMESTAMP, val INT) PARTITION BY RANGE ON (stamp); CREATE TABLE first_decade (stamp TIMESTAMP, val INT); CREATE TABLE second_decade (stamp TIMESTAMP, val INT); CREATE TABLE third_decade (stamp TIMESTAMP, val INT); ALTER TABLE splitted ADD TABLE first_decade AS PARTITION FROM TIMESTAMP '2000-01-01 00:00:00' TO TIMESTAMP '2010-01-01 00:00:00'; ALTER TABLE splitted ADD TABLE second_decade AS PARTITION FROM TIMESTAMP '2010-01-01 00:00:00' TO TIMESTAMP '2020-01-01 00:00:00'; ALTER TABLE splitted ADD TABLE third_decade AS PARTITION FROM TIMESTAMP '2020-01-01 00:00:00' TO RANGE MAXVALUE WITH NULL VALUES; INSERT INTO splitted VALUES (TIMESTAMP '2000-01-01 00:00:00', 1), (TIMESTAMP '2002-12-03 20:00:00', 2), (TIMESTAMP '2012-05-12 21:01:00', 3), (TIMESTAMP '2019-12-12 23:59:59', 4); INSERT INTO splitted VALUES (TIMESTAMP '2020-01-01 00:00:00', 5), (NULL, 6);

When I trigger the following query I was expecting that only table second_decade would be used as Monet would be smart enough to check the partition information for each table.

Kind regards, Daniel

sql>plan select * from splitted where stamp = TIMESTAMP '2020-01-01 00:00:00'; +--------------------------------------------------------------------------------------------------------------------------------------+ | rel                    | +======================================================================================================================================+ | union (                    | | | union (                    | | | | project (                    | | | | | select (                   | | | | | | table(sys.first_decade) [ "first_decade"."stamp" as "splitted"."stamp", "first_decade"."val" as "splitted"."val" ] COUNT     | | | | | ) [ "splitted"."stamp" = timestamp(7) "2020-01-01 00:00:00.000000" ]                   | | | | ) [ "splitted"."stamp", "splitted"."val" ],                    | | | | project (                    | | | | | select (                   | | | | | | table(sys.second_decade) [ "second_decade"."stamp" as "splitted"."stamp", "second_decade"."val" as "splitted"."val" ] COUNT  | | | | | ) [ "splitted"."stamp" = timestamp(7) "2020-01-01 00:00:00.000000" ]                   | | | | ) [ "splitted"."stamp", "splitted"."val" ]                   | | | ) [ "splitted"."stamp", "splitted"."val" ],                    | | | project (                    | | | | select (                   | | | | | table(sys.third_decade) [ "third_decade"."stamp" as "splitted"."stamp", "third_decade"."val" as "splitted"."val" ] COUNT       | | | | ) [ "splitted"."stamp" = timestamp(7) "2020-01-01 00:00:00.000000" ]                     | | | ) [ "splitted"."stamp", "splitted"."val" ]                   | | ) [ "splitted"."stamp", "splitted"."val" ]                   | +--------------------------------------------------------------------------------------------------------------------------------------+ 19 tuples clk: 2.428 ms

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

Hello Daniel. There was no issue for it, just a TODO on my list. I implemented the pruning over the last few days, for tables partitioned by columns like in your case. It will be available at the next feature release sometime later this year. Best regards, Pedro On 7/18/20 9:20 PM, Daniel Glöckner wrote:

Hi Pedro,

That’s great! Any Issue that I could check out and subscribe for updates?

Kind regards, Daniel

...

Am 17.07.2020 um 15:13 schrieb ferreira :



But I forgot to say we can improve for the partitioned ones with predicates indeed. It's on my TODO list.

Pedro

On 7/17/20 3:10 PM, ferreira wrote:

...

Hello Daniel,

This is something we are missing on the documentation :)

In order for the pruning to work, the children tables must be set to read only. Then the analyze command should run for each child, so the min and max values are updated on their statistics. Only then, the pruning can happen.

This is to avoid eventual cache poisoning on the re-writers.

Also the upper range limit is exclusive on a partition. At your example the third_decade table should be the only one expected.

Best regards,

Pedro

On 7/17/20 2:32 PM, Daniel Glöckner wrote:

...

Hi,

looking at merge tables and the related query plans I was expecting the planner to skip tables depending on partition information and query predicates.

I'm using the official example for merge tables: https://www.monetdb.org/blog/updatable-merge-tables

CREATE MERGE TABLE splitted (stamp TIMESTAMP, val INT) PARTITION BY RANGE ON (stamp); CREATE TABLE first_decade (stamp TIMESTAMP, val INT); CREATE TABLE second_decade (stamp TIMESTAMP, val INT); CREATE TABLE third_decade (stamp TIMESTAMP, val INT); ALTER TABLE splitted ADD TABLE first_decade AS PARTITION FROM TIMESTAMP '2000-01-01 00:00:00' TO TIMESTAMP '2010-01-01 00:00:00'; ALTER TABLE splitted ADD TABLE second_decade AS PARTITION FROM TIMESTAMP '2010-01-01 00:00:00' TO TIMESTAMP '2020-01-01 00:00:00'; ALTER TABLE splitted ADD TABLE third_decade AS PARTITION FROM TIMESTAMP '2020-01-01 00:00:00' TO RANGE MAXVALUE WITH NULL VALUES; INSERT INTO splitted VALUES (TIMESTAMP '2000-01-01 00:00:00', 1), (TIMESTAMP '2002-12-03 20:00:00', 2), (TIMESTAMP '2012-05-12 21:01:00', 3), (TIMESTAMP '2019-12-12 23:59:59', 4); INSERT INTO splitted VALUES (TIMESTAMP '2020-01-01 00:00:00', 5), (NULL, 6);

When I trigger the following query I was expecting that only table second_decade would be used as Monet would be smart enough to check the partition information for each table.

Kind regards, Daniel

sql>plan select * from splitted where stamp = TIMESTAMP '2020-01-01 00:00:00'; +--------------------------------------------------------------------------------------------------------------------------------------+ | rel                                    | +======================================================================================================================================+ | union (                                    | | | union (                                    | | | | project (                                    | | | | | select (                                   | | | | | | table(sys.first_decade) [ "first_decade"."stamp" as "splitted"."stamp", "first_decade"."val" as "splitted"."val" ] COUNT   | | | | | ) [ "splitted"."stamp" = timestamp(7) "2020-01-01 00:00:00.000000" ]                                       | | | | ) [ "splitted"."stamp", "splitted"."val" ],                                    | | | | project (                                    | | | | | select (                                   | | | | | | table(sys.second_decade) [ "second_decade"."stamp" as "splitted"."stamp", "second_decade"."val" as "splitted"."val" ] COUNT  | | | | | ) [ "splitted"."stamp" = timestamp(7) "2020-01-01 00:00:00.000000" ]                                       | | | | ) [ "splitted"."stamp", "splitted"."val" ]                                   | | | ) [ "splitted"."stamp", "splitted"."val" ],                                    | | | project (                                    | | | | select (                                   | | | | | table(sys.third_decade) [ "third_decade"."stamp" as "splitted"."stamp", "third_decade"."val" as "splitted"."val" ] COUNT     | | | | ) [ "splitted"."stamp" = timestamp(7) "2020-01-01 00:00:00.000000" ]                                         | | | ) [ "splitted"."stamp", "splitted"."val" ]                                   | | ) [ "splitted"."stamp", "splitted"."val" ]                                   | +--------------------------------------------------------------------------------------------------------------------------------------+ 19 tuples clk: 2.428 ms

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list