How Partition Pruning work in Vertica
In Vertica we can take advantage of the "Partition Pruning" so we can speed up our queryes.
So what happens when we query a partitioned table in Vertica.
- all ROS containers that are not needed will be eliminated/excluded from the Query process, this is done using the predicate values to compare them with the
partition-related metadata.
- is very important that when using partitions you provide the query with a predicate that is on the partitioning column.
Let's create a partitioned table and identify if Vertica is able to use the Partition Pruning or not.
create table table2(id_fk integer not null, i integer)
partition by id_fk;
insert /*+direct*/ into table2 values (1,1);
insert /*+direct*/ into table2 values (2,2);
insert /*+direct*/ into table2 values (3,3);
commit;(dbadmin@:5433) [dbadmin] * select * from table2
dbadmin- where id_fk = 1;
id_fk | i
-------+---
1 | 1
(1 row)select event_details
from query_events
where event_type = 'PARTITIONS_ELIMINATED'
and (transaction_id, statement_id) in
(select transaction_id, statement_id - 1 from current_session);
event_details
-----------------------------------------------------------------
Using only 1 stores out of 3 for projection public.table2_super
(1 row)
- so what this tells us is that partition pruning was used to resolve the query.
this tells us that only 1 ROS was used in the query execution out from the 3 ROS available and the rest were pruned "excluded from the query plan".
Another way to see this using the EXPLAIN command with the LOCAL VERBOSE option.
- since the output of the EXPLAIN will be to long to be analyzed at the command line i will spool it into a file and then grep the file and look for the "Pruned storages" string.
(dbadmin@:5433) [dbadmin] * o /tmp/file.out
(dbadmin@:5433) [dbadmin] * EXPLAIN LOCAL VERBOSE select * from table2 where id_fk = 1;
(dbadmin@:5433) [dbadmin] * ! cat /tmp/file.out | grep Pruned
2[label = "StorageUnionStep: table2_supernsubdivision: 1048576nstartEpoch 0nendEpoch 1
192nNo of storages: 1n<span style="color: #ff0000;">Pruned storages: 2</span> (2 rows)nnodeSet: [0] n[Local Segmented Prope
rties: UNSEGMENTED]nUnc: id_fk[1,1] - Integer(8)nUnc: i[1,2] - Integer(8)", color = "pur
ple", shape = "box"];
