How Can An Query for Getting Metadata Cost 9G memory?
We use Sqoop to pull the data from Vertica. Sqoop generates a query with condition (1=0) to get the field type information and the query does not need to return any records. However, the query needs 9G memory allocations, just as the second query Sqoop fired for returning the records. We think this is wrong... could anyone help on this? Do we have any trick to let the trivial query (with condition 1=0) to use minimum memory?
0
Comments
the following three are equivalent, #3 is best and uses least memory
1. select * from t where 1=0;
2. select * from t where false;
3. select * from t limit 0;
but what you really want to run is the following.
SELECT quote_ident(c.table_schema), quote_ident(c.table_name), quote_ident(c.column_name),
c.data_type, c.data_type_length, c.column_default, not c.is_nullable,
p.constraint_id is not null,
f.rts || '.' || f.rtn || '(' || f.rcn || ')' || case when f.c < 2 then '' else ', ...' end
FROM v_catalog.columns AS c
LEFT OUTER JOIN v_catalog.primary_keys p ON c.table_schema=p.table_schema
AND c.table_name=p.table_name AND c.column_name=p.column_name
LEFT OUTER JOIN (SELECT table_schema, table_name, column_name,
quote_ident(MAX(reference_table_schema)) as rts,
quote_ident(MAX(reference_table_name)) as rtn,
quote_ident(MAX(reference_column_name)) as rcn,
count(*) as c
FROM v_catalog.foreign_keys
WHERE table_schema ILIKE E'%' ESCAPE E'\' AND table_name ILIKE E't' ESCAPE E'\'
GROUP BY table_schema, table_name, column_name
) AS f
ON c.table_schema=f.table_schema AND c.table_name=f.table_name
AND c.column_name=f.column_name
WHERE c.table_schema ILIKE E'%' ESCAPE E'\' AND c.table_name ILIKE E't' ESCAPE E'\'
ORDER BY c.table_schema, c.table_name, c.ordinal_position;
can you provide the query, with the profile information.
my internal test show it take 4MB of memory
skeswani=> profile select * from t t1 natural join t t2 where 1=0;
NOTICE 4788: Statement is being profiled
HINT: Select * from v_monitor.execution_engine_profiles where transaction_id=45035996273715386 and statement_id=12;
NOTICE 3557: Initiator memory for query: [on pool general: 4289 KB, minimum: 4289 KB]
NOTICE 5077: Total memory required by query: [4289 KB]
Our query includes some group by. The query itself if it does not include group by, the returned records will be 17million. With group by, the result set is about 1.4M. It is something like as below. Please note that $CONDITIONS is what sqoop needs (it will be"1=0" for the metadata).
SELECT
f1,
f2,
TO_CHAR(datetime,'YYYYMMDDHH24MI') dt1,
TO_CHAR(datetime,'YYYYMMDDHH24MI') dt2 ,
SUM(NVL(period, 0)) period ,
NVL(SUM (m1) ) :: VARCHAR,'') n3 ,
NVL(SUM( m2 ) :: VARCHAR,'') n4
FROM Table
WHERE $CONDITIONS
datetime >= TO_TIMESTAMP('2017112908','YYYYMMDDHH24')
AND datetime < TO_TIMESTAMP('2017112916','YYYYMMDDHH24')
AND datetimeutc >= TO_TIMESTAMP('2017112919','YYYYMMDDHH24')
AND datetimeutc < TO_TIMESTAMP('2017112920','YYYYMMDDHH24')
AND eutrancellfdd NOT LIKE '%TOY%'
GROUP By
f1,
f2,
TO_CHAR(datetime,'YYYYMMDDHH24MI'),
TO_CHAR(datetimeutc,'YYYYMMDDHH24MI')
-- limit 1
can you run this query with profile in front. also please replace the condition with what the actual condition is. its fair and correct for the query to take 9GB, unless the predicate reliably folds to a "false". i need to know for sure if that is indeed the case.
if you prefer i can run it if you give me the table sql ddl