How to optimize query

Be sure statistics are up to date - the explain plan will indicate this. After that, the quickest way is to copy this query to a file and run this through DataBase Designer (DBD) from admintools or Management Console. You can run an incremental design only on the relevant schema(s) and table(s) using the query file, then analyze statistics and build the design. In case of incremental design, I usually don't deploy automatically, instead I download and review the proposed projections to verify they seem reasonable. In particular, you'll want TABLE1 and TABLE2 segmented by id, sorted by id, (segment/sort will help ensure that id's to be joined are on same node) and grouped by the keys listed here for best performance.

To start with:
If you have to group by 46 columns, I would really challenge your database design - or, at least, the way your query is formulated..
If , say, columns 5 through 46 depend directly on columns 1,2,3 and 4, then, by all means, create a subselect with columns 1,2,3,4 and AVG(price), GROUPing by 1,2,3,4, and then join that subselect with the previous table(s) to retrieve columns 5 through 46.

And - as soon as you have long strings in the columns you GROUP BY, you risk GROUP BY spilling, too. Don't keep a VARCHAR(256) in your table definition, if your maximum string is only 32 bytes long, for example.

Happy playing -
Marco

@marcothesane : Thanks for your info. Can you please tell me in what conditions we encounter with group by spill. Earlier I was thinking keeping many columns in group by cause this but its happening even with single group by column. And how group by spill cause query performance? what are the ways to avoid this?

'# start_lecture #

Vertica projections are always sorted. If you say nothing when creating the table, in the newest Vertica versions, by the first 8 columns of the table - and before, by the first 32 columns of the table. If you have one, by the primary key. But you can sort by whatever column or group of columns you choose.

In a query like this one:

SELECT
  prod_id
, SUM(sales_amt)
FROM sales_fact
GROUP BY
  prod_id
;

If sales_fact is ordered by something else than prod_id , the scan of the table will proceed along this other sort order. You read prod ID 1, then another prod ID, and, until the very end, you must be prepared to read yet another prod ID of 1. So you need to keep intermediate sum results for each encountered different prod ID. The DBMS creates a hash table to accommodate a bucket for each different prod ID it encounters. If you have millions of different prod ID-s, the hash table won't fit into memory, and it will spill to disk, even if it is just an integer. With columns that require much more than the 8 bytes of an integer, like VARCHARs, it gets much worse. Vertica can't tell in advance what will be the effective maximum length of the data it will find in a VARCHAR(2000). Even if the longest existing string is 32, Vertica will have to pre-reserve 2000 bytes for each column entry in the hash table, because in theory it could get as long as that. And if 32 bytes * 1 million rows might fit into memory, 2000 bytes * 1 million rows probably won't , and you will spill to disk.

Avoiding it is trying to reduce the length definition of VARCHAR columns in the CREATE TABLE; or, better, GROUP-ing by the HASH() of a VARCHAR, and transform the HASH() value back to the string using a previously built lookup table.

And you won't need any memory at all for a GROUP BY if you order a projection of sales_fact by the prod_id first. In that case, when you read a bunch of rows with prod_idof 1, then you encounter a prod_idof 2, you can be sure that you won't encounter any prod_id of 1 ever again in this query, and the sum you have now is final.

'# stop_lecture #

Cheers -

I created table like this:
CREATE TABLE T
(
p_id varchar(5) NOT NULL,
t_id varchar(5) NOT NULL,
a_date date NOT NULL
)

and executing below query:

SELECT
  p_id
FROM T
GROUP BY
  p_id
;

but still getting group by spill(Table has only 3 rows).
below is the digraph of above query:

3[label = "GroupByHash(Spill): 1 keys\nAggs:\nUnc: Date(8)", color = "green", shape = "box"];

@michaeljack - A properly ordered projection can avoid the GroupByHash(Spill).

See:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AnalyzingData/SQLAnalytics/AvoidingGROUPBYHASHWithProjectionDesign.htm

Example:

dbadmin=> CREATE TABLE T
dbadmin-> (
dbadmin(> p_id varchar(5) NOT NULL,
dbadmin(> t_id varchar(5) NOT NULL,
dbadmin(> a_date date NOT NULL
dbadmin(> );
CREATE TABLE

dbadmin=> explain
dbadmin-> SELECT
dbadmin->   p_id
dbadmin-> FROM T
dbadmin-> GROUP BY
dbadmin->   p_id
dbadmin-> ;
                                                                                                                                                    QUERY PLAN                                                                                             
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 ------------------------------
 QUERY PLAN DESCRIPTION:
 ------------------------------

 explain
 SELECT
   p_id
 FROM T
 GROUP BY
   p_id
 ;

 Access Path:
 +-GROUPBY HASH (LOCAL RESEGMENT GROUPS) [Cost: 0, Rows: 0] (PATH ID: 1)
 |  Group By: T.p_id
 |  Execute on: Query Initiator
 |  Execute on: Query Initiator
 ------------------------------
 -----------------------------------------------
 PLAN: BASE QUERY PLAN (GraphViz Format)
 -----------------------------------------------
 digraph G {
 graph [rankdir=BT, label = "BASE QUERY PLAN\nQuery: explain \nSELECT\n  p_id\nFROM T\nGROUP BY\n  p_id\n;\n\nAll Nodes Vector: \n\n  node[0]=v_test_db_node0001 (initiator) Up\n  node[1]=v_test_db_node0002 (executor) Up\n  node[2]=v_test_db_node0003 (executor) Up\n", labelloc=t, labeljust=l ordering=out]
 0[label = "Root \nOutBlk=[UncTuple]", color = "green", shape = "house"];
 1[label = "NewEENode \nOutBlk=[UncTuple]", color = "green", shape = "box"];
 2[label = "ParallelUnionStep: \nCombine\nUnc: Varchar(5)", color = "green", shape = "box"];
 3[label = "GroupByHash(Spill): 1 keys\nAggs:\nUnc: Varchar(5)", color = "green", shape = "box"];
 4[label = "ParallelUnionStep: \nLocally Resegment\nUnc: Varchar(5)", color = "green", shape = "box"];
 5[label = "ExprEval: \n  NULL\nUnc: Varchar(5)", color = "green", shape = "box"];
 1->0 [label = "V[0]",color = "black"];
 2->1 [label = "0",color = "blue"];
 3->2 [label = "0",color = "blue"];
 4->3 [label = "0",color = "blue"];
 5->4 [label = "0",color = "blue"];
 }
(39 rows)

dbadmin=> CREATE PROJECTION public.T_PR AS SELECT p_id, t_id, a_date FROM public.T ORDER BY p_id SEGMENTED BY HASH(p_id) ALL NODES;
CREATE PROJECTION

dbadmin=> explain
dbadmin-> SELECT
dbadmin->   p_id
dbadmin-> FROM T
dbadmin-> GROUP BY
dbadmin->   p_id
dbadmin-> ;
                                                                                                                                                   QUERY PLAN                                                                                              
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 ------------------------------
 QUERY PLAN DESCRIPTION:
 ------------------------------

 explain
 SELECT
   p_id
 FROM T
 GROUP BY
   p_id
 ;

 Access Path:
 +-GROUPBY PIPELINED [Cost: 205, Rows: 10K (NO STATISTICS)] (PATH ID: 1)
 |  Group By: T.p_id
 |  Execute on: All Nodes
 | +---> STORAGE ACCESS for T [Cost: 201, Rows: 10K (NO STATISTICS)] (PATH ID: 2)
 | |      Projection: public.T_PR_b0
 | |      Materialize: T.p_id
 | |      Execute on: All Nodes
 ------------------------------
 -----------------------------------------------
 PLAN: BASE QUERY PLAN (GraphViz Format)
 -----------------------------------------------
 digraph G {
 graph [rankdir=BT, label = "BASE QUERY PLAN\nQuery: explain\nSELECT\n  p_id\nFROM T\nGROUP BY\n  p_id\n;\n\nAll Nodes Vector: \n\n  node[0]=v_test_db_node0001 (initiator) Up\n  node[1]=v_test_db_node0002 (executor) Up\n  node[2]=v_test_db_node0003 (executor) Up\n", labelloc=t, labeljust=l ordering=out]
 0[label = "Root \nOutBlk=[UncTuple]", color = "green", shape = "house"];
 1[label = "NewEENode \nOutBlk=[UncTuple]", color = "green", shape = "box"];
 2[label = "Recv\nRecv from: v_test_db_node0001,v_test_db_node0002,v_test_db_node0003\nNet id: 1000\n\nUnc: Varchar(5)", color = "green", shape = "box"];
 3[label = "Send\nSend to: v_test_db_node0001\nNet id: 1000\n\nUnc: Varchar(5)", color = "green", shape = "box"];
 4[label = "GroupByPipe: 1 keys\nAggs:\nUnc: Varchar(5)", color = "green", shape = "box"];
 5[label = "StorageMergeStep: T_PR_b0; 1 sorted\nUnc: Varchar(5)", color = "purple", shape = "box"];
 6[label = "GroupByPipe: 1 keys\nAggs:\nUnc: Varchar(5)", color = "brown", shape = "box"];
 7[label = "ScanStep: T_PR_b0\np_id\nUnc: Varchar(5)", color = "brown", shape = "box"];
 1->0 [label = "V[0]",color = "black"];
 2->1 [label = "0",color = "blue"];
 3->2 [label = "0",color = "blue"];
 4->3 [label = "0",color = "blue"];
 5->4 [label = "0",color = "blue"];
 6->5 [label = "0",color = "blue"];
 7->6 [label = "0",color = "blue"];
 }
(46 rows)

You only get that string: GroupByHash(Spill) in the explain phase. Explain shows what might happen. Not what actually happened.

You probably did not run a SELECT ANALYZE_STATISTICS('t'); , so the optimizer does not know if it is a three billion row or a three row table. It prepares for a GROUP BY spill. It does not do it.

Try this one here below - it does exactly what you are stating above.

In my example, it goes into GROUP BY PIPELINED.

Can you build a similar scenario for us in which you do run into GROUP BY spilled?

DROP TABLE IF EXISTS t;
CREATE TABLE t (
p_id   VARCHAR(5) NOT NULL,
t_id   VARCHAR(5) NOT NULL,
a_date DATE       NOT NULL
) UNSEGMENTED ALL NODES;

INSERT INTO t 
          SELECT '00001','00001', DATE '2019-01-01'
UNION ALL SELECT '00002','00001', DATE '2019-01-02'
UNION ALL SELECT '00003','00001', DATE '2019-01-03'
;

-- Note, from the output of next line, that the auto
--  projection is sorted by p_id first.
SELECT EXPORT_OBJECTS('','t',FALSE);

-- profile the query - it will store query events and 
-- execution details in query_profiles.
-- use the label to identify the query 
-- and the related other info tables.
PROFILE
SELECT
/*+LABEL('for_michaeljack') */
  p_id
FROM T
GROUP BY
  p_id
;

-- a spill to disk of a JOIN or a GROUP BY 
-- would be logged as a query_event.
\x
SELECT
  e.*
FROM query_events e
JOIN query_requests r USING(transaction_id,statement_id)
WHERE request_label='for_michaeljack'
;


-- dc_explain_plans contains the execution plan of 
-- a profiled query
SELECT 
  path_line
FROM v_internal.dc_explain_plans
JOIN query_requests USING(transaction_id,statement_id)
WHERE request_label='for_michaeljack'
ORDER BY 
  path_id
, path_line_index ;

@marcothesane : If i create a projection with a sort order like

order by col1,col2,col3

what does that translate to from a storage perspective...

Does it mean that col1,col2 and col3 are independently stored sorted ? Is there any collocation of rows based on this column ordering?

What do you mean in detail?

An additional projection to the one you already have will of course consume disk space - in the same order of magnitude , a bit less, a bit more, than the projection that already exists.

And Vertica is column oriented - it's the first real columnar database . So, yes, col1, col2 and col3 are independently stored - each column is basically a file by itself.

And co-location happens by segmentation, not by sorting, and is used for joins, not for grouping.

If you order a table's projection by the columns you use in the GROUP BY clause, you will never spill to disk:

If you GROUP BY customer_id, and the table is ORDER BY customer_id (and maybe other columns after that), all rows with customer_id 1 will be together. As soon as you reach a customer_id of 2, you can be sure that no customer_id of 1 will follow, and you can pass on the grouping result for customer_id 1 without having to store it. So you don't need any memory for GROUP BY at all, and you cannot possibly spill to disk ....

Does this answer your question?

I mean. let say I have below table.

p_id  col1
1       a4   
4       a3  
3       a1  
1       a2

If I do order by p_id, col1 then first data will be sorted by p_id and then by col1 in RDBMS.

p_id  col1
1       a2   
1       a4 
3       a1  
4       a3

Now my questions is, same thing will happen in Vertica as well if I keep these 2 fields in projection's order by clause or it will sort both column independently and will store independently. like below.

p_id          col1
1                a1
1                a2
3 .              a3
4                a4

If you order like so:

ORDER BY
  p_id
, col1

, then, if you fire a query:

SELECT * FROM the_table;

with no ORDER BY in the query,

the result will be:

p_id  col_1
  1    a2
  1    a4
  3    a1
  4    a3

It behaves just like a classic relational database. A row will never be split apart.
Internally, there is a system of file based pointers to enable reading the file that constitutes one column, and jump to the matching instances of the other column(s) and re-assemble the tuple/row.

@marcothesane: Thank you so much for your help. I learned so many things from you about Vertica. It helped me a lot to optimize my query.

If you order like so:

ORDER BY
  p_id
, col1

, then, if you fire a query:

SELECT * FROM the_table;

with no ORDER BY in the query,

the result will be:

p_id  col_1
  1    a2
  1    a4
  3    a1
  4    a3

It behaves just like a classic relational database. A row will never be split apart.
Internally, there is a system of file based pointers to enable reading the file that constitutes one column, and jump to the matching instances of the other column(s) and re-assemble the tuple/row.

You're welcome. You get most out of the database if you know how it works - just as you can drive a motorbike with a sidecar better if you know how it works. And the more you get from Vertica, the more you like it ... :-]]