Query Memory Usage

Kazuki

Hello all

 

My question is about memory usage by user "select" queries.

How does those consume memory resources?

 

For example, 

CREATE TABLE TEST.EXAMPLE (

VAR1 INT,

VAR2 INT,

VAR3 INT,

DATE1 DATE,

DATE2 DATE

) PARTITIONED BY DATE;

 

(VAR1=VAR2=VAR3 and DATE1=DATE2) 

 

1. SELECT VAR1, VAR2, VAR3 FROM TEST.EXAMPLE ;

2. SELECT VAR1 FROM TEST.EXAMPLE;

Does 1. query consume 3 times larger than 2. query?

 

1. SELECT * FROM TEST.EXAMPLE WHERE DATE1='2015-01-01' ;

2. SELECT * FROM TEST.EXAMPLE WHERE DATE2='2015-01-01' ;

Does 1. query consume less than 2. query?

That's because 2. query needs to check all partitions but 1. query needs to check only one partition.

 

And another question is "When does SELECT queries free their memory"

1. EXTRACT AND FREE ONE BY ONE PARTITION
2. EXTRACT ALL PARTITIONS AND FREE AT THE END OF QUERY

 

and if you know any best practice to save memory use of queries

 

Thank you 

eli_revach

Hi ,

For select  queries , memory allocation is related to the  amount of columns on the select list (its around 2M per column)  , in many cases it  also related to many other factors like if your query include  aggregation and sorting and it also related to your cluster setup (how many nodes )   .

 

 

You can basically  see the amount  of memory by querying the query_requests table , or using profile command . 

One of the  techniques  to reduce amount  of memory  per query is by  playing  with your resource pool parameters , the parameter that can help you  her is  PLANNEDCONCURRENCY (search in forum you will find many stuff on it )  .

 

Thanks

Kazuki

Hello eli_revach

 

 

Partition has no relation to memory consumption, doesn't it?

I'll check query_requests table.

 

Thank you for your help!