Does Partition Pruning Work?

Moderator: NorbertKrupa

Post Reply
beth
Intermediate
Intermediate
Posts: 58
Joined: Tue Oct 01, 2013 12:42 pm

Does Partition Pruning Work?

Post by beth » Sat Nov 14, 2015 4:58 pm

Hi guys,

The documentation says the following about Partition Pruning:
When a query predicate includes one or more columns in the partitioning clause, queries look only at relevant ROS containers.
Does anyone know if this actually works? If it does how can I tell? I do not see anything about "Partition Pruning" in the explain plan:

Code: Select all

dbadmin=> create table test (a_year date not null) partition by extract(year from a_year)::int;
CREATE TABLE
dbadmin=> insert into test values ('01/01/1987');
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into test values ('01/01/1988');
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into test values ('01/01/1989');
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into test values ('01/01/1990');
 OUTPUT
--------
      1
(1 row)

dbadmin=> commit;

 explain select a_year from test where a_year = '01/01/1987';

 Access Path:
 +-STORAGE ACCESS for test [Cost: 5, Rows: 1] (PATH ID: 1)
 |  Projection: public.test_super
 |  Materialize: test.a_year
 |  Filter: (test.a_year = '1987-01-01'::date)
So if it does work, is there any real world improvements in queries that use it?

Thank you!

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Does Partition Pruning Work?

Post by NorbertKrupa » Sat Nov 14, 2015 10:50 pm

Look in v_monitor.query_events for something like partitions eliminated on the statement. Otherwise, do an EXPLAIN VERBOSE and look for something like pruned storage.
Checkout vertica.tips for more Vertica resources.

Post Reply

Return to “New to Vertica Database Administration”