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

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?

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


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)


  • Jim_KnicelyJim_Knicely Administrator
    edited February 2019

    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.


    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                                       
     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
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.