Partition Pruning Slow

Moderator: NorbertKrupa

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Partition Pruning Slow

Post by id10t » Fri Aug 02, 2013 5:29 pm

Hi!

Probably local resegmentation takes a time:
Locally Resegment By: ((((((100 \<*\> 69069) \<+\> internal_strhash(a.storid)) \<*\> 69069) \<+\> internal_numhash(a.tempid)) \<*\> 69069) \<+\> a.tempid1)
Why you have segmentation on one node?
More interesting why Vertica performs resegmentation? 'internal_numhash' and 'internal_strhash' - its UDF or SQL functions? Are they stable, immutable?

sramdram
Newbie
Newbie
Posts: 23
Joined: Fri Jul 12, 2013 6:46 pm

Re: Partition Pruning Slow

Post by sramdram » Fri Aug 02, 2013 6:26 pm

The table creation script is common for both clustered and single node.(using the segmenation clause for both)

How does that affect partition pruning

scutter
Master
Master
Posts: 302
Joined: Tue Aug 07, 2012 2:15 am

Re: Partition Pruning Slow

Post by scutter » Sat Aug 03, 2013 12:42 pm

If the table creation steps are identical on both clusters, you should be getting partition pruning on both clusters. If you are able to provide an actual example we may be able to offer more insight. Or open a support case.

--Sharon
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

scutter
Master
Master
Posts: 302
Joined: Tue Aug 07, 2012 2:15 am

Re: Partition Pruning Slow

Post by scutter » Wed Aug 07, 2013 7:36 pm

I'm reminded that in 6.x you can get partition pruning info from the query_events table. No need to use the undocumented "old school" explain method.

event_type | PARTITIONS_ELIMINATED
event_description | Some storage containers will not be processed because they contain no relevant data.
event_details | Using only 0 stores out of 7 for projection public.tpart_super
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Partition Pruning Slow

Post by id10t » Thu Aug 08, 2013 10:27 pm

Hi!

@Sharon
More interesting its - why Vertica do a re-segmentation when 0 rows deleted (doesn't matter if a partition pruning occurred or not, even if it occurred Vertica should not to do a re-segmentation) ?

scutter
Master
Master
Posts: 302
Joined: Tue Aug 07, 2012 2:15 am

Re: Partition Pruning Slow

Post by scutter » Fri Aug 09, 2013 2:30 am

I think that resegmentation in this context is referring to resegmentation within a node for the purpose of further parallelizing work. For example for a GROUP BY you may see LOCAL RESEGMENT in the explain plan. GLOBAL RESEGMENT goes across nodes.
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

Post Reply

Return to “Vertica Performance Tuning”