Vertica - calculating the cost of each query

Question

I am using Vertica, and I want to calculate the costs of my queries.

One way I was trying to figure this out was by calculating the number of rows rows that will be scanned (either before, or after the query).

eg. how many rows will be scanned to get the results for

select * from users where is_deleted = true;

(the answer may vary if we have projections in place I would imagine)

Answer 1

To get an estimated number of rows in a plan, you can use EXPLAIN .... Otherwise, you'll need to profile a statement to get exact values of all aspects of the query.

The counters available in the profile for rows are:

• estimated rows produced
• role rows produced
• intermediate rows to process
• rows segmented
• rows sent
• rows pruned by valindex
• rows to process
• rows processed
• rows received
• rows produced

The optimizer will choose the appropriate projection(s) for the query.