Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

PROFILE vs EXPLAIN

Hello,
I'm trying to assess the cost of a huge query, now when I run it with EXPLAIN, it shows
SELECT LIMIT 1K [Cost: 572M, Rows: 1K (NO STATISTICS)] (PATH ID: 30)
but when I run it with PROFILE, it shows
NOTICE 5077: Total memory required by query: [49320002 KB]
as you can see there's a huge difference in memory consumption, why do these commands generate two different outputs and what am I reading wrong here?

Comments

  • moshegmosheg Employee
    edited September 15

    In general EXPLAIN estimates and PROFILE shows the real numbers after the target statement executes.
    EXPLAIN returns the optimizer's query plan to evaluate choices that the optimizer will make.
    EXPLAIN with the VERBOSE option reports in more detail the rendered query plan including Memory estimation for each Path.
    The Row operator is the number of rows the optimizer estimates the query will return. Letters after numbers refer to the units of measure (K=thousand, M=million, B=billion, T=trillion) and Cost correlate to query runtime estimation, but it do not provide an estimate of actual runtime.
    To see how much Memory really reserved for a given query check Vertica system table: QUERY_CONSUMPTION
    which saves data from all queries, whether explicitly profiled or not.
    You can retrieve your query details from QUERY_CONSUMPTION using columns STATEMENT_ID and TRANSACTION_ID which combine as unique keys (unless the query has multiple plans), or use a LABEL hint in your query.

    The MEMORY_INUSE_KB column in system table RESOURCE_ACQUISITIONS shows how much total memory was acquired for each query. If operators acquire all memory acquired for the query, the plan must request more memory from the Vertica resource manager.

    Use the following query to find the memory used by a query in one node.
    Memory used will always be reflected in the pool of query initiation.

    Select transaction_id,statement_id, max(memory_kb) 
    from dc_resource_acquisitions 
    where transaction_id=<xxxx> and 
          statement_id=<xxxx> and 
          node_name=’node1’ 
    group by transaction_id,statement_id;
    

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.