Explain plan shows incorrect rows even after collecting 100% statistics

Hi Team,

I have a single node Vertica cluster (VM). When I run the explain on the following query I was getting the Rows read as 127, but the actual records retrieved is 861. Could you advice why I'm not able to match the actual records retrieved in the query results with the explain plan output?

Query:
explain select * from online_sales.online_sales_fact where sale_date_key = 164 and ship_date_key = 165 ;


QUERY PLAN DESCRIPTION:

explain select * from online_sales.online_sales_fact where sale_date_key = 164 and ship_date_key = 165

Access Path:
+-STORAGE ACCESS for online_sales_fact [Cost: 140K, Rows: 127] (PATH ID: 1)
| Projection: online_sales.online_sales_fact_super
| Materialize: online_sales_fact.ship_date_key, online_sales_fact.promotion_key, online_sales_fact.warehouse_key, online_sales_fact.shipping_key, online_sales_fact.online_page_key, online_sales_fact.call_center_key, online_sales_fact.customer_key, online_sales_fact.product_key, online_sales_fact.product_version, online_sales_fact.sale_date_key, online_sales_fact.pos_transaction_number, online_sales_fact.sales_quantity, online_sales_fact.sales_dollar_amount, online_sales_fact.ship_dollar_amount, online_sales_fact.net_dollar_amount, online_sales_fact.cost_dollar_amount, online_sales_fact.gross_profit_dollar_amount, online_sales_fact.transaction_type
| Filter: (online_sales_fact.sale_date_key = 164)
| Filter: (online_sales_fact.ship_date_key = 165)

Comments

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert
    edited February 28

    The ROWS in an explain plan are the estimated output rows for each operator. In this case (storage access). An EXPLAIN command doesn't run the query. That's why you only get estimates.

    Example:

    dbadmin->[email protected]=>* SELECT c1, COUNT(*) FROM test GROUP BY c1;
     c1 | COUNT
    ----+--------
      5 |    861
      1 |  16420
      2 |  16420
      3 | 229880
      4 | 788160
    (5 rows)
    
    dbadmin->[email protected]=>* EXPLAIN SELECT * FROM test WHERE c1 = 5;
                                                                                                                                              QUERY PLAN                                       
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     ------------------------------
     QUERY PLAN DESCRIPTION:
     ------------------------------
    
     EXPLAIN SELECT * FROM test WHERE c1 = 5;
    
     Access Path:
     +-STORAGE ACCESS for test [Cost: 180, Rows: 11K] (PATH ID: 1)
     |  Projection: public.test_b0
     |  Materialize: test.c1
     |  Filter: (test.c1 = 5)
     |  Execute on: Query Initiator
     ------------------------------
    

    You can profile a query to get an actual row count. When you PROFILE a SQL statement, Vertica runs it.

    dbadmin->[email protected]=> \! vsql -c "PROFILE SELECT * FROM test WHERE c1 = 5;" -o /dev/null
    Timing is on.
    NOTICE 4788:  Statement is being profiled
    HINT:  Select * from v_monitor.execution_engine_profiles where transaction_id=45035996280378193 and statement_id=1;
    NOTICE 3557:  Initiator memory for query: [on pool general: 4561 KB, minimum: 4561 KB]
    NOTICE 5077:  Total memory required by query: [4561 KB]
    Time: First fetch (861 rows): 59.722 ms. All rows formatted: 59.837 ms
    
    dbadmin->[email protected]=>* Select node_name, counter_value rows from v_monitor.execution_engine_profiles where transaction_id=45035996280378193 and statement_id=1 and counter_name = 'rows produced' and operator_name = 'Root';
        node_name     | rows
    ------------------+------
     v_vmart_node0001 |  861
    (1 row)
    

Leave a Comment

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