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

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