Vertica Blog

Vertica Blog

Projections

Summarize a Table’s Projections: Quick Tip

Jim Knicely authored this tip. Use the built-in Vertica function GET_TABLE_PROJECTIONS to quickly summarize the details of all of the projections of a Vertica table. dbadmin=> \t Showing only tuples. dbadmin=> SELECT get_table_projections('public.jim'); Current system K is 1. # of Nodes: 3. Table public.jim has 3 projections. Projection Name: [Segmented] [Seg Cols] [# of Buddies]...

Analyze Statistics at the Schema Level (Part 2): Quick Tip

Jim Knicely authored this tip. The ANALYZE_STATISTICS function only accepts a table/projection/column name as input. In yesterday’s Vertica Quick Tip we learned how to get Vertica to generate and execute ANALYZE_STATISTICS SQL statements, one for each table in a given schema. It was an okay solution, but not very convenient. A better option would be...

Query Tuning with Vertica: Dos and Don’ts

This blog post was authored by Eugenia Moreno. Query tuning in Vertica is not an exact science. Recommendations differ based on your database. This document assumes that all nodes in the cluster are UP, your Vertica configuration is ok, and that v*perf tools have been executed. The following diagram shows the query flow in Vertica:...

Announcing Vertica 9.x Query Performance Tuning Training

This blog post was authored by Drea Brandford. You have had Vertica up and running in production for a while now; but you’re noticing that, while your analytic queries are executing far faster than they would in a traditional row-store database, they are still not performing quite as fast as you need them to. Chances...

Understanding Vertica Eon Mode

This blog post was authored by Soniya Shah. This blog post is updated as of July 2018. With Eon mode, Vertica is a database that takes advantage of all elastic compute and scalable storage capabilities in the cloud. In Eon mode, Vertica can take advantage of cloud economics – it can be made to scale...

Understanding the Vertica Query Optimizer

This blog post was authored by Soniya Shah. The Vertica query optimizer uses statistics about the data to create a query plan, which contains a set of operations to compute the requested result. Depending on the properties of the projections defined in your database, the optimizer can choose faster and more efficient operations. This is...

Vertica Quick Tip: Projection Create Types

This blog post was authored by Jim Knicely. The DDL produced by the EXPORT_OBJECTS function typically contains a create type for a projection. In the above example the create type is L for “Lazy”, meaning that I inserted data into the table when no projections existed yet. Here is a list of the projection create...

Projection Delete Concerns

This blog post was authored by Curtis Bennett. Deletes in Vertica are a complicated topic. I’ve had many people say to me that they’d heard that one should never do deletes in Vertica, or that deletes in Vertica are slow. Nothing could be further from the truth. But like anything in a database, they should...
Programmer

Operational Considerations for Database Administrators in Eon Mode

This blog post was authored by Shrirang Kamat. The purpose of this document is to familiarize advanced Vertica Enterprise mode users about some of the behavioral similarities and differences between Vertica Enterprise mode and Vertica Eon mode. This document assumes you have a basic understanding about Eon mode architecture. You can find more details about...

Redesigning Projections for Query Optimization

When you submit a query to Vertica, the Vertica query optimizer automatically assembles a query plan, which consists of a set of operations to compute the requested result. Depending on the properties of the projections defined in your database, the query optimizer can choose faster and more efficient operations. Thus, it?s important to recognize what...
Three 3D arrows, different colors pointing in different directions

Looking Under the Hood at Vertica Queries

When you submit a query to Vertica, you want it to execute as quickly and efficiently as possible. The query optimizer creates a plan that is designed to do just that. The directives in the query plan determine your query's run-time performance and resource consumption, but the properties of your projections and the system parameters also impact...
Programmer

Improving COUNT DISTINCT Performance with Live Aggregate Projections

Today?s organizations need to be able to measure the effectiveness of online ads and marketing campaigns. In your particular organization, you may want to measure how effectively your ads drive unique visitors to your website. Or you may want to see if your ads drive repeat visits from the same user over a specific period...